Help tuning a very poor SQL query

[eluser]Leon Stafford[/eluser]
[quote author="TheFuzzy0ne" date="1245266382"]My first reaction is to suggest you don't order by RAND(). It actually re-orders the rows, which can be slow in itself. Instead, I'd suggest you count the rows, and do something like this:

Code:

$this->db->where('grade', $grade);
$row_count = $this->db->count_results('kg_compounds');

$this->db->where('grade', $grade);
$this->db->limit(1, rand(0, $row_count-1));
$res = $this->db->get('kg_compounds');

It's two queries, but I'd be interested to know how much of a difference it makes (if any).

Sorry, I know I've totally avoided your question, but I am about to dash off out the door, so I thought I should point that out.[/quote]

Thanks again TheFuzzyOne!

I will implement something like that for the random part.

If you come back before anyone else replies, would be thrilled if you could check out the meaty part of the code, too :cheese: