书和人是两张表
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。请各位解惑一下MySQL索引优化
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。请各位解惑一下MySQL索引优化
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
如果这种连接方法,干脆索引也用不到了(不过仍然是全表扫描,没坏到哪儿去)。
person.persontel
各加一个索引 然后强制使用
以供分析。
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表中添加索引了。
select book.bookid
from book
right join person on book.personid=person.personid
where 1=1
and (person.personmobile='23456780730' or person.persontel='98766540730');
丢,不能添加颜色.