MySQL

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

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Sep 2008

Posts: 15

Reputation: cy163 is an unknown quantity at this point

Solved Threads: 0

cy163

Offline

Newbie Poster

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

Nov 5th, 2008

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?

 Help with Code Tags 
 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;

•

Join Date: Aug 2008

Posts: 1,162

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

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

Nov 5th, 2008

are you using a foreign key on the name column?

Custom Application & Software Development
www.houseshark.net

•

Join Date: Jun 2008

Posts: 79

Reputation: varmadba is an unknown quantity at this point

Solved Threads: 8

varmadba

Offline

Junior Poster in Training

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

Nov 5th, 2008

See if you force mysql to use index

 Help with Code Tags 
 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

:- Varma

We are Happy to inform launch of a new site with loads of database related information Site offers wide range of functionality Forums,Blogs,Articles,Editorials and much more
http://www.sqllibrarian.info/