优化MySQL多对多查询
我在让 MySQL 在多对多查询上使用索引时遇到问题,我在下面粘贴了相关信息。
EXPLAIN SELECT *
FROM interviews
JOIN interview_category_links ON interviews.id = interview_category_links.inter_id
JOIN categories ON interview_category_links.cat_id = categories.id
WHERE categories.category_safe = 'news'
ORDER BY date DESC
LIMIT 15
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE categories ALL PRIMARY,id NULL NULL NULL 16 Using where; Using temporary; Using filesort
1 SIMPLE interview_category_links ref inter_id,inter_id_2,cat_id cat_id 4 devel.categories.id 893
1 SIMPLE interviews eq_ref PRIMARY PRIMARY 4 devel.interview_category_links.in... 1
在类别表上,我有索引(id,cat_id),在interview_category_links表上,我有(cat_id,inter_id)和单独索引以及(cat_id)和(inter_id)
I've having problems getting MySQL to use indexes on a many to many query, i have pasted the relative information below.
EXPLAIN SELECT *
FROM interviews
JOIN interview_category_links ON interviews.id = interview_category_links.inter_id
JOIN categories ON interview_category_links.cat_id = categories.id
WHERE categories.category_safe = 'news'
ORDER BY date DESC
LIMIT 15
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE categories ALL PRIMARY,id NULL NULL NULL 16 Using where; Using temporary; Using filesort
1 SIMPLE interview_category_links ref inter_id,inter_id_2,cat_id cat_id 4 devel.categories.id 893
1 SIMPLE interviews eq_ref PRIMARY PRIMARY 4 devel.interview_category_links.in... 1
On the categories table I have the index (id, cat_id) and on the interview_category_links table i have both (cat_id, inter_id) and individual indexes as well (cat_id) and (inter_id)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您有采访索引(id)吗?
如果删除“按日期描述排序”会怎样?查询运行得更快吗?如果是这样,您可能需要向该字段添加索引(我假设它是 Interviews 表中的一列)。
Do you have an index on the interviews(id)?
What if you removed the "order by date desc"? Does the query run faster? If so, you may want to add an index to that field (which, I assume, is a column in the interviews table).