SQL Count Optimisations

Question

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.

So there are indices on the album_id and viewable columns in the tracks table? Other than this, I don't see any possible optimizations. You could also run EXPLAIN on this query to see what is going on. — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 16, 2018 at 1:43
That is correct. :/ I guess I'll have to see if there's a way to override how Django determines it's count, because this query is generated automatically. The EXPLAIN resulted in >20 million rows being selected so I guess it's no wonder that the join is going to take some time... — Tom Prentice
– Tom Prentice, Commented Mar 16, 2018 at 2:32
50 million rows is a lot of data. If you need the results of this query often, perhaps you can cache it somewhere. — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 16, 2018 at 2:34

Gordon Linoff · Accepted Answer · 2018-03-16 01:43:37Z

2

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.

answered Mar 16, 2018 at 1:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tim Biegeleisen Over a year ago

Assuming id is the primary key of album, I think it should already be indexed. +1 for your second query suggestion.

Tom Prentice Over a year ago

Thanks, I already have indexes on all mentioned columns though... and my overall goal is to obtain the album records based on the track relation having viewable set to 1, not the tracks themselves.

Collectives™ on Stack Overflow

SQL Count Optimisations

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related