开发者

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 :

Cannot join two tables using indexes

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜