Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2007
    Posts
    130

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

    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;

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    • 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?

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.
    Last edited by mike_bike_kite; 11-05-08 at 12:53.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •