MySql:使这个查询更快......有办法吗? [英] MySql: make this query faster... is there a way?

问题描述

有 4 个表:

• 书籍:id、名称、作者、ecc...
• 类别:id、名称
• 图书馆:id、名称、街道、城市、ecc..
• bookcorr:book_id、category_id、library_id

ID 都是键.

查询必须显示定义图书馆中图书数量的类别.例如:

The query must show the categories with the numbers of books in a defined Library. for ex:

图书馆 X:

浪漫 (50)

黄色 (40)

科学 (30)

这是我的查询:

SELECT category.id
     , category.name
     , count(*)      AS tot
  FROM bookcorr  
  JOIN category 
    ON category.id = bookcorr.category_id 
WHERE bookcorr.library_id = 'x' 
GROUP BY bookcorr.category_id 
ORDER BY tot DESC

而且还是很慢，有没有办法更快地获得结果?

and it's still slow, is there a way to get results faster ?

推荐答案

您在这些表上有哪些索引?查询建议 bookcorr 应该在 (category_id, library_id) 上有一个索引.

What indices do you have on these tables? The query suggests that bookcorr should have an index on (category_id, library_id).

您的查询未使用 Books 或 Library...

Your query doesn't make use of Books or Library...

这篇关于MySql:使这个查询更快......有办法吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！