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)
精彩评论