[Firebird 1.5] Problème avec les jointures et les index...
Bonjour,
Pourquoi la première requête n'utilise pas les index sur la table T_TRACK (et s'exécute en 15 s !) alors que la seconde les utilise correctement (et s'exécute en 0 s!) ?
PS: J'utilise Firebird 1.5
Code:
1
2
3
4
5
6
| 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
Code:
1
2
3
4
5
6
7
8
| 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
Merci pour votre aide.
Cyber Sinh
