Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > MySQL > оптимизация сложного запроса

Автор: gcc 25.4.2010, 18:23

я хотел вывести 2 фотографии с последних фотоальбомов... с последних 5 фотоальбомов (или 10-20)
нужно проверить доступность фотоальбома и доступность фотографии
(я хотел вывести первую фотографии в фотоальбоме и последнюю фотографию в нем же, всего 2 вывести)

у меня не получается проверить, если я ставлю id_pf_min в where он пишет что не извесный id_pf_min вообще!!

вообщем, как пропустить тот фотоальбом в котором нету фотографии и в котором id_pf_min не равняется?

я сделал через INNER JOIN ( в него вставил через ON IN вложенный запрос), но вроде бы плохо, для видео выполняется сек. 10 (у меня включено кеширование, т.е. оно не всегда так выполнется)

Код


SELECT t1.privat_pa,
                                               t1.id_pa,  
                                               t1.name_pa,          

                    (SELECT MIN(t6.id_pf) FROM photo_files AS t6 WHERE t6.id_pa = t1.id_pa 
                           '.$sql_p.' ) AS id_pf_min,                                        
                      (SELECT MAX(t5.id_pf) FROM photo_files AS t5 WHERE t5.id_pa = t1.id_pa 
                           AND t5.id_pf > id_pf_min '.$sql_p2.' ) AS id_pf_max                                        

                         FROM photo_albums AS t1

                            INNER JOIN photo_files AS t77
                                  ON t77.id_pf in (SELECT MIN(t6.id_pf) FROM photo_files 
                           AS t6 WHERE t6.id_pa = t1.id_pa '.$sql_p.' )

                                     
                                  
                                    LEFT JOIN content AS t2
                                  ON t1.id_co = t2.id_co                                  
                                 ' . $sql2 . '
                        ' . $sql . '
                        AND id_pf is not null
                   ORDER BY t1.created desc 
                      LIMIT 5

================

а вот есть для видео тоже самое:
(только есть таблица video_files_preview еще одна, треья, с превиюшками )

можно ли это оптимизировать?
повторюсь, в where не работает если вставить алиас id_vf_min

Код


SELECT  
                                               t1.id_va,    
                                               t1.privat_va,      
                                           t1.name_va ,
                                           
                                              ( SELECT MIN(t20.id_vf) FROM  video_files AS t20
                                                  WHERE t20.id_va = t1.id_va ) AS id_vf_min,                                           
                                           
                                              (
                                            SELECT MIN(t10.id_vfp)                 
                                                    FROM video_files_preview AS t10
                                               WHERE t10.id_vf = id_vf_min

                                              )    AS vf_preview_min,
                                              
                                              
                                              
                                              
                                              ( SELECT MAX(t21.id_vf) FROM  video_files AS t21
                                                  WHERE t21.id_va = t1.id_va ) AS id_vf_max,                                           
                                           
                                              (
                                            SELECT MAX(t11.id_vfp)                 
                                                    FROM video_files_preview AS t11
                                               WHERE t11.id_vf = id_vf_max

                                              )    AS vf_preview_max                                              
                                              

                                           
                                           
                         FROM video_albums AS t1

                        INNER JOIN video_files_preview AS t77
                           ON t77.id_vfp in (
                                            SELECT MIN(t10.id_vfp)                 
                                                    FROM video_files_preview AS t10
                                               WHERE t10.id_vf in ( SELECT MIN(t20.id_vf) FROM  video_files AS t20
                                                  WHERE t20.id_va = t1.id_va ) 
                                              )
                       

                                    LEFT JOIN content AS t2
                                  ON t1.id_co = t2.id_co                                  
                             
                   ORDER BY t1.created desc 
                      LIMIT 5

Your MySQL connection id is 41076
Server version: 5.0.83-log FreeBSD port: mysql-server-5.0.83

кстате, в последнее время хитрые запросы на MySQL пишутся сложно, на PgSQL подобное давным давно доступно для использования...
а на PgSQL это на много удобней и лучше будет?

Автор: Akina 25.4.2010, 20:07

Бррр...
Ничего не понять... а чтобы понять, надо так много думать, что времени жалко.
Давай как-нить попроще да постандартнее. Структура, связи, формализованный желаемый результат...

Автор: gcc 27.4.2010, 01:13

сделал так:
добавил вложенній в FROM

Код

SELECT t1.privat_pa,
                                               t1.id_pa,  
                                               t1.name_pa,          
                                              id_pf_min,
                                                    
                      (SELECT MAX(t5.id_pf) FROM photo_files AS t5 
                      WHERE t5.id_pa = t1.id_pa AND t5.id_pf > id_pf_min '.$sql_p2.' ) AS id_pf_max                                        
 
                       FROM photo_albums AS t1,  
                     (SELECT MIN(t6.id_pf) FROM photo_files AS t6,photo_albums AS t1  
                     WHERE t6.id_pa = t1.id_pa '.$sql_p.' ) AS id_pf_min
                                                                      
                                    LEFT JOIN content AS t2
                                  ON t1.id_co = t2.id_co                                  
                                 ' . $sql2 . '
                        ' . $sql . '
                        AND id_pf_min > 0
 
                   ORDER BY t1.created desc  
                      LIMIT 5

и в INNER JOIN вложенный

Код

SELECT  
                                         t1.id_va,    
                                         t1.privat_va,      
                                        t1.name_va ,
                                        
                                            ( SELECT MIN(t20.id_vf) FROM  video_files AS t20
                                                WHERE t20.id_va = t1.id_va '.$sql_p.' ) AS id_vf_min,                                        
                                        
                                            (
                                            SELECT MIN(t10.id_vfp)              
                                                FROM video_files_preview AS t10
                                         WHERE t10.id_vf = id_vf_min
                                            )    AS vf_preview_min,
                                            
                                            
                                            ( SELECT MAX(t21.id_vf) FROM  video_files AS t21
                                                WHERE t21.id_va = t1.id_va AND t21.id_vf > id_vf_min '.$sql_p2.') AS                                                    id_vf_max,                                        
                                        
                                            (
                                            SELECT MAX(t11.id_vfp)              
                                                FROM video_files_preview AS t11
                                         WHERE t11.id_vf = id_vf_max
                                            )    AS vf_preview_max                                            
                                            
                                        
                                        
                        FROM video_albums AS t1
                INNER JOIN video_files AS t78
                       ON t78.id_vf in  ( SELECT MIN(t20.id_vf) FROM  video_files AS t20
                                                WHERE t20.id_va = t1.id_va '.$sql_p.' ) 
                       INNER JOIN video_files_preview AS t77
                       ON t77.id_vfp in (
                                            SELECT MIN(t10.id_vfp)              
                                                FROM video_files_preview AS t10
                                         WHERE t10.id_vf = t78.id_vf
                                            )
                   
                                    LEFT JOIN content AS t2
                              ON t1.id_co = t2.id_co                              
                   ' . $sql2 . '
                       ' . $sql . '                    
                           
                  ORDER BY t1.created desc 
                     LIMIT 5