Michael's Blog

SQL Query Optimization

I've been updating the code for my site a bit to make things load a bit faster and one of the things I fixed was the random query generator.

SELECT quote, name FROM quotes ORDER BY RANDOM() LIMIT 1

The query above works great for small tables but consider the issue when you have a table with millions of rows, the server must read every row, sort them, and then throw out all the results but the first. This takes a lot of CPU time and is slow, the optimal solution is to use create a sequence as the primary key and then generate a random number using that. For example:

idx = select last_value FROM quotes_idx_seq

SELECT quote_text, name FROM quotes WHERE quote_id = '%s' %idx

It requires two queries instead of one but there is much less disk I/O required.