Cannot join two tables using indexes
I am just not able to get the table indexes to use themselves in this table join :
explain select n.* from npi n,npi_taxo开发者_如何转开发nomy nt where n.NPI=nt.NPI_CODE;
Here is the output of explain that i am getting :
As you can see, it finds the key in possible_keys column but the key column is empty. Please help.
Your query hits all rows in table NPI
and you are selecting all columns from it.
Why would you want MySQL to use an index on that table?
I'm fairly certain that if you only included the column npi
in the select list,you would get the desired behaviour.
why not use explicit joins
SELECT n.* FROM npi n
INNER JOIN npi_taxomoty nt ON (n.NPI = nt.NPI_CODE)
or use a left join:
SELECT n.* FROM npi n
LEFT JOIN npi_taxomoty nt ON (n.NPI = nt.NPI_CODE)
If the first query returns no result, but the 2nd one does, you have no matches between n.NPI and nt.NPI_CODE.
As a side remark, I find it odd that you would give a field (NPI) the same name as a table (npi). It's confusing.
精彩评论