🚀 go-pugleaf

Cyber Sinh a �crit :
> Hi,
> 
> Why the first query don't use index on T_TRACK (take 15 s!) whereas the 
> second use it (and take only 0s!)?
> 
> PS: I use Firebird 1.5
> 
> SELECT t_album.c_id, t_album.c_artist, t_album.c_name
> FROM t_album
> INNER JOIN t_albumjoin ON t_albumjoin.c_album = t_album.c_id
> INNER JOIN t_track ON t_albumjoin.c_track = t_track.c_id
> INNER JOIN t_artist ON t_artist.c_id = t_track.c_artist
> WHERE t_artist.c_name = 'Evanescence';
> 
> 
> Plan
> PLAN JOIN (T_TRACK NATURAL,T_ARTIST INDEX (ARTIST_PKEY),T_ALBUMJOIN 
> INDEX (ALBUMJOIN_FK_TRACK),T_ALBUM INDEX (ALBUM_PKEY))
> 
> Plan Adpat�
> PLAN JOIN (T_TRACK NATURAL,T_ARTIST INDEX (ARTIST_PKEY),T_ALBUMJOIN 
> INDEX (ALBUMJOIN_FK_TRACK),T_ALBUM INDEX (ALBUM_PKEY))
> 
> ------ Performance info ------
> Prepare time = 0ms
> Execute time = 15s 828ms
> Avg fetch time = 565,29 ms
> Current memory = 1 110 768
> Max memory = 1 244 108
> Memory buffers = 2 048
> Reads from disk to cache = 3 680
> Writes from cache to disk = 0
> Fetches from cache = 5 112 579
> 
> 
> 
> SELECT t_album.c_id, t_album.c_artist, t_album.c_name
> FROM t_album
> INNER JOIN t_albumjoin ON t_albumjoin.c_album = t_album.c_id
> INNER JOIN t_track ON t_albumjoin.c_track = t_track.c_id
> INNER JOIN t_artist ON t_artist.c_id = t_track.c_artist
> WHERE t_artist.c_name = 'Evanescence' AND
> t_track.c_name = 'Anywhere' AND
> t_album.c_name = 'Origin';
> 
> 
> Plan
> PLAN JOIN (T_TRACK INDEX (TRACK_NAME),T_ARTIST INDEX 
> (ARTIST_PKEY),T_ALBUM INDEX (ALBUM_NAME),T_ALBUMJOIN INDEX 
> (ALBUMJOIN_FK_TRACK,ALBUMJOIN_FK_ALBUM))
> 
> Plan Adpat�
> PLAN JOIN (T_TRACK INDEX (TRACK_NAME),T_ARTIST INDEX 
> (ARTIST_PKEY),T_ALBUM INDEX (ALBUM_NAME),T_ALBUMJOIN INDEX 
> (ALBUMJOIN_FK_TRACK,ALBUMJOIN_FK_ALBUM))
> 
> ------ Performance info ------
> Prepare time = 0ms
> Execute time = 0ms
> Avg fetch time = 0,00 ms
> Current memory = 1 128 940
> Max memory = 1 244 108
> Memory buffers = 2 048
> Reads from disk to cache = 0
> Writes from cache to disk = 0
> Fetches from cache = 783
> 
> 
> Thanks for your help...
> Cyber Sinh
Je pense que la baisse de performance est simplement du � la diff�rence 
de la requete. Dans la premi�re, j'utilise une clause WHERE 
suppl�mentaire qui permet au moteur de disqualifier d'office un grand 
nombre d'enregistrements ! Dans la seconde requete, le moteur est oblig� 
de parcourir tous les enregistrements de la table (plus de 3 000 000)...

Le probl�me, c'est que je ne sais pas comment mieux optimiser ma requ�te 
(tous les champs de tri / filtrage contiennent des index). Une id�e ?