MySQL strangeness with joins and keys.
| From: | William Bailey | Date: | Tue, 06 Feb 2001 15:38:41 +0000 |
| Subject: | MySQL strangeness with joins and keys. | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-6428@lists.php.net to get a copy of this message | ||
Hi all,
This might be a bit OT but i'll ask anyway.
I'm currently working on a project that involves using a database
that contains about 1.8 million rows and am currently in the process of
trying to get it to search as quick as posable. I've been using the
explain keyword to try to see how mysql is handling the query and i am
seeing some strange things. The query that i am currently using is:
explain
select
RECORDING.RNO as rno,
TRACK.TRA as track,
ARTIST.ART as artist,
RECORDING.REC as title,
RECORDING.MUSIC as style,
MEDIA.MEDIUM as medium
from
TRACK
left join ARTIST use index (ano_art_index) on TRACK.ANO=ARTIST.ANO
left join RECORDING use index (rno_rec_music_index) on TRACK.RNO=RECORDING.RNO
left join MEDIA use index (rno_lno_medium_index) on RECORDING.RNO=MEDIA.RNO
where
TRACK.TRA like '%war pigs%'
limit 0,5;
which returns the following in the mysql client:
+-----------+-------+----------------------------------------------...
| table | type | possible_keys
+-----------+-------+----------------------------------------------...
| TRACK | index | NULL
| ARTIST | ref | ano_index,ano_art_index
| RECORDING | ref | rno_index,rno_rec_music_index
| MEDIA | ref | rno_index,rno_lno_index,rno_lno_medium_index
+-----------+-------+----------------------------------------------...
...+----------------------+---------+---------------+---------...
| key | key_len | ref | rows
...+----------------------+---------+---------------+---------...
| tra_rno_ano_index | 68 | NULL | 1775059
| ano_art_index | 4 | TRACK.ANO | 1500
| rno_rec_music_index | 4 | TRACK.RNO | 3458
| rno_lno_medium_index | 5 | RECORDING.RNO | 5225
...+----------------------+---------+---------------+---------...
...+-------------------------+
| Extra |
...+-------------------------+
| where used; Using index |
| Using index |
| Using index |
| Using index |
...+-------------------------+
Now what i don't understand is why Mysql is saying that there is no
(NULL) keys available for table TRACK yet it does use the key
'tra_rno_ano_index' and it only looks in the index for the results (Using
index).
Also when searching using the query above it will take about 3 min to
perform the search but if i change the line
TRACK.TRA like '%war pigs%'
to
TRACK.TRA like '%what if%'
the search will take anywhere from 0.5 - 6 seconds. Anybody have any idea
why this is happening and how i can get a constant, and hopefully quick,
speed across a number of different searches?
If its any help im currently running MySQL 3.23.31 for freeBSD 4.2
Thanks,
William.