Help tuning a very poor SQL query

[eluser]TheFuzzy0ne[/eluser]
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.