CODEWITHSUNDEEP
pythonmysqldjango
Tom Prentice
Tom Prentice

Reputation: 21

SQL Count Optimisations

I have been using the Django Rest Framework, and part of the ORM does the following query as part of a generic object list endpoint:

`SELECT COUNT(*) AS `__count`
FROM `album`
INNER JOIN `tracks`
    ON (`album`.`id` = `tracks`.`album_id`)
WHERE `tracks`.`viewable` = 1`

The API is supposed to only display albums with tracks that are set to viewable, but with a tracks table containing 50 million rows this is query never seem to complete and hangs the endpoint's execution.

All columns referenced are indexed, so I do not know why this is taking so long to execute. If there are any potential optimisations that I might have not considered please let me know.

Upvotes: 0

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

For this query:

SELECT COUNT(*) AS `__count`
FROM `album` INNER JOIN 
     `tracks`
     ON (`album`.`id` = `tracks`.`album_id`)
WHERE `tracks`.`viewable` = 1`;

An index on tracks(viewable, album_id) and album(id) would help.

But, in all likelihood a join is not needed, so you can do:

select count(*)
from tracks
where viewable = 1;

For this the index on tracks(viewable) will be a big help.

Upvotes: 2

Related Questions