CODEWITHSUNDEEP
mysqlperformance
robert
robert

Reputation: 25

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

There are 4 tables:

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 ?

Upvotes: 1

Views: 169

Answers (2)

Quassnoi
Quassnoi

Reputation: 425421

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)

Upvotes: 1

FrustratedWithFormsDesigner
FrustratedWithFormsDesigner

Reputation: 27496

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

Upvotes: 1

Related Questions