开发者

MySql: make this query faster... is there a way?

开发者 https://www.devze.com 2023-01-01 07:02 出处:网络
There are 4 tables: Books: id, name, author, ecc... Category : id, name Library: id, na开发者_运维知识库me, street, city, ecc..

There are 4 tables:

  • Books : id, name, author, ecc...
  • Category : id, name
  • Library : id, na开发者_运维知识库me, street, city, ecc..
  • bookcorr : book_id, category_id, library_id

Ids are all keys.

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

Library X:

Romantic (50)

Yellow (40)

Science (30)

This is my query:

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 ?


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

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


Change the query so that it would group on the leading table's column to avoid Using temporary:

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

and make sure that you have an index on bookcorr (library_id, category_id)

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号