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

Expand Post »

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?

MySQL Syntax (Toggle Plain Text)
 
 
EXPLAIN SELECT
         tb1.Name
FROM
         tb1, tb2
WHERE
         tb1.Name = tb2.Name;
    

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

Similar Threads

Datagrid - no value at index ### error when scroll and sort in VB.NET

cy163

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

16 posts
since Sep 2008

Permalink

Nov 5th, 2008

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

are you using a foreign key on the name column?

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Nov 5th, 2008

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

See if you force mysql to use index

MySQL Syntax (Toggle Plain Text)
EXPLAIN SELECT
         tb1.Name
FROM
         tb1, tb2 FORCE index(t1.name)
WHERE
         tb1.Name = tb2.Name;
EXPLAIN SELECT
         tb1.Name
FROM
         tb1, tb2 force index(t1.name)
WHERE
         tb1.Name = tb2.Name;

and also check it name column is of same data type in both the tables

varmadba

Reputation Points: 22

Solved Threads: 9

Junior Poster in Training

Offline

83 posts
since Jun 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: MySQL Migr Toolkit, Generic JDBC ".. schema names could not be retrieved (error: 0)."

Next Thread in MySQL Forum Timeline: Microsoft Offers Startups Free Software, but Be Wary

DaniWeb Message

Cancel Changes

User Name
Password