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

Question

There are 4 tables:

• Books : id, name, author, ecc...
• Category : id, name
• Library : id, name, 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 ?

Answer 1

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...

Answer 2

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)