Why tb1.Name=tb2.Name does not use index on Name

cy163 · #1 (**permalink**) 11-04-08, 10:24

Hello ALL,

I did the following query. the EXPLAIN result showed 'NULL' in the 'key' colume, meaning that NONE of indexes have been used. However, indexes have been created on Name fields for both tb1 and tb2. I wonder why MySQL does not use the indexes to speed up the query. Is there any way to make the query faster?

Code:

EXPLAIN SELECT
         tb1.Name
FROM
         tb1, tb2
WHERE
         tb1.Name = tb2.Name;

mike_bike_kite · #2 (**permalink**) 11-04-08, 10:48

It would help if you gave the SQL you used to create both tables. You can use the command show create table tb1.
How many records in both tables?
How long does the query take at the moment?
Can you show the output of your explain command?
Have you ever considered giving proper names to your tables?

mike_bike_kite · #3 (**permalink**) 11-05-08, 11:43

I'll make a guess then :-

assuming that your indexes are correct then the normal reason for the optimiser to decide not to use the indexes is simply because the amount of data in the tables is so small that it's not worth the effort of downloading the index pages when it might as well just download the main data pages directly. This would be quite normal behaviour.

Internet Services

Web Development

Digital Marketing

Consumer Tech