如何在查询里使用到索引

[ycjzj]

EXPLAIN SELECT DISTINCT tiger_person_resume.Email
FROM tiger_person_resume
LEFT JOIN tiger_memo_record ON tiger_memo_record.Email = tiger_person_resume.Email
LEFT JOIN tiger_person_work ON tiger_person_work.Email = tiger_person_resume.Email
LEFT JOIN tiger_person_copy ON tiger_person_copy.Email = tiger_person_resume.Email
WHERE 1
AND (
(
tiger_memo_record.memo LIKE '%销售%'
AND tiger_memo_record.memo LIKE '%总监%'
)
OR (
tiger_person_work.Work_content LIKE '%销售%'
AND tiger_person_work.Work_content LIKE '%总监%'
)
OR (
tiger_person_resume.Self_evaluate LIKE '%销售%'
AND tiger_person_resume.Self_evaluate LIKE '%总监%'
)
OR (
tiger_person_copy.copy_content LIKE '%销售%'
AND tiger_person_copy.copy_content LIKE '%总监%'
)
)

这个查询我explain后结果如下:


我已经在tiger_person_resume建立了索引,为什么就只有我查询的tiger_person_resume没有使用"ref",可能索引是email了??

[WWWWA]

在什么字段上建立的索引？

[utpcb]

LIKE '% 前%不会使用索引的

[ycjzj]

我在tiger_person_resume的email字段已经做了索引了!其他表同样也是在email上做了索引,并且其他表都关联tiger_person_resume中的email字段的,可为什么我使用以上explain的时候却只有其他表使用了email这个索引,而针对tiger_person_resume表却没有使用email这个索引了!


utpcb,我并没在email字段上使用模糊查询!!

[懒得去死]

The table tiger_person_resume couldn't use index until you use exact column.

[ycjzj]

版主在练习英语了

I use "distinct tiger_person_resume.Email " column

"distinct tiger_person_resume.Email " is not exact column??

how can I edit this sql ??

[ycjzj]

斑竹你所说的使用确定列,是在select里还是在where里??或在其他地方??

[ycjzj]

EXPLAIN SELECT DISTINCT tiger_person_resume.Email
FROM tiger_person_resume
LEFT JOIN tiger_memo_record ON tiger_memo_record.Email = tiger_person_resume.Email
LEFT JOIN tiger_person_work ON tiger_person_work.Email = tiger_person_resume.Email
LEFT JOIN tiger_person_copy ON tiger_person_copy.Email = tiger_person_resume.Email
WHERE 1
AND (
tiger_memo_record.memo = '销售'
OR tiger_person_work.Work_content = '销售'
OR tiger_person_resume.Self_evaluate = '销售'
OR tiger_person_copy.copy_content = '销售'
)
AND tiger_person_resume.Email = tiger_memo_record.Email

我改成以上的语句,但在key和key_len中均是null,extra还是使用Using temporary

[懒得去死]

Change your sql with union all not using or.
The index will be used.

[ycjzj]

我以上的sql语句如何改为union all了??麻烦写个好嘛,从没用过union all,所以我不知道咱将以上语句组合和union all的!
谢谢了!

[ycjzj]

有没人帮帮我，如果将我的那段sql改为用union all呀!!改好我加分!!