|Home | RSS | Gallery | Stats | About | Comics | Downloads | Links | Scripts | Test Scripts | Wiki ||
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.
Return to main page.