一个查询优化问题

[mQney]

书和人是两张表
explain select book.bookid
from book
left join person on book.personid=person.personid
where 1=1
and (person.personmobile='23456780730' or person.persontel='98766540730');

1 SIMPLE book index personid personid 9 174495 Using index
1 SIMPLE person eq_ref PRIMARY,personid PRIMARY 8 book.personid 1 Using where


174495太大了，扫描全表了。因为一个bookid只有关联一个personid。

请各位解惑一下

[steely_chen]

select book.bookid
from book 
right join person on book.personid=person.personid
where 1=1 
and (person.personmobile='23456780730' or person.persontel='98766540730');

注意这里是right join 会把person做为主表,先查person再查book ，如果还想更快就要考虑person表中添加索引了。

[steely_chen]

select book.bookid
from book 
right join person on book.personid=person.personid
where 1=1 
and (person.personmobile='23456780730' or person.persontel='98766540730');

丢,不能添加颜色.

[mQney]

explain select bookid from book, person where book.personid=person.personid and 1=1 and (person.personmobile='33333390730' or personid.persontel='88888890730'); 1 SIMPLE book ALL personid 174495 1 SIMPLE person eq_ref PRIMARY,personid PRIMARY 8 book.personid 1 Using where 如果这种连接方法，干脆索引也用不到了（不过仍然是全表扫描，没坏到哪儿去）。

[rucypli]

person.personmobile person.persontel 各加一个索引 然后强制使用

[ACMAIN_CHM]

贴出 show index from person; show index from book ; 以供分析。