Django Query Performance

Question

I have a rather performance related question about django queries.

Say I have a table of employees with 10,000 records. Now If I'm looking to select 5 random employees that are of age greater than or equal to 20, let's say some 5,500 employees are 20 or older. The django query would be:

Employee.objects.filter(age__gte=20).order_by('?')[:5]

and the raw counterpart of this query in mysql will be:

SELECT * FROM `database`.`employee` 
WHERE `employee`.`age` >= 20
ORDER BY RAND ()
LIMIT 5;

From the looks of django query the database first returns the 5,500 records, then python sorts these records on random or whatever order we select and a chunk of first five records is returned whereas the raw query will return only five records from the database directly.

My question is that is there any performance difference between both the queries? If so which one is better and why?

Good question - I don't know! Check out this answer though, you should be able to apply the steps they suggest to find out. — chucksmash, Sep 12 '15 at 02:59
Heh, sorry about that! [This one](http://stackoverflow.com/a/1074224/341510) — chucksmash, Sep 12 '15 at 03:30

Shang Wang · Accepted Answer · 2015-09-12T03:41:34.917

I did a quick check on my existing project:

queryset = BlahModel.objects.order_by('?')[:5]
print queryset.query

The result is:

SELECT `blah_model`.`id`, `blah_model`.`date` FROM `blah_model` ORDER BY RAND() LIMIT 5;

So, they are the same.

I wouldn't be too surprise at the result, because django ORM is a direct mapping between sql query result and django object, so order_by('?') would be equal to ORDER BY RAND(), even the [:5] statement is translated to LIMIT in mysql (here's the doc and doc).

score 0 · Answer 2 · edited May 23 '17 at 12:03

Any performance issue that you are seeing is not because of django. As Shang Wang as already pointed out the query executed by Django is exactly the same as the raw query. The trouble is with the rand() call.

As has been explained elsewhere including some StackOveflow answers like this one: https://stackoverflow.com/a/6911022/267540 trouble is caused by the fact that a random value has to be calculated for each row. Luckily you have a WHERE clause that cuts down the number of rows. BUt as the data grows your query will gradually become slower and slower.

This link has several different suggestions on how to overcome this problem http://www.warpconduit.net/2011/03/23/selecting-a-random-record-using-mysql-benchmark-results/

It is tricky to avoid calling `RAND()` for each row. See [my blog](http://mysql.rjweb.org/doc.php/random) on the problem. — Rick James, Sep 14 '15 at 03:02

Django Query Performance

2 Answers2