Optimise MySQL ORDER BY RAND() on a filtered GROUP BY query to avoid temp/indexless join

MySQL "join without index" counter is incrementing as shown in various analysis tools like mysql-tuner.pl etc, having tracked down to a query which selects a random product using RAND(), I would like to optimise to help avoid this increment.

The query looks like this:

select p.*, count(u.prodid) as count from prods p 
left outer join usage u on p.prodid=u.prodid 
where p.ownerid>0 and p.active=1
group by p.prodid
order by rand() limit 1;

I've tried using this style also...

select p.*, count(u.prodid) as count from prods p
left outer join usage u on p.prodid=u.prodid
where prodid in 
(select prodid from prods 
where ownerid>0 and active=1 
group by prodid order by rand() limit 1);

but MySQL doesn't support a LIMIT in an 'in' subquery...

The explain/describe looks like this...

+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                        |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | p     | range | ownerid       | ownerid | 4       | NULL |   11 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | u     | index | NULL          | userid  | 8       | NULL |   52 | Using index                                  | 
开发者_JAVA技巧+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+

2 rows in set (0.00 sec)

Whilst some of you may think "so what if it performs an index-less join", perhaps it's more an annoyance than something that could be a problem, but I appreciate there may be a better way to achieve what is needed anyway particularly as the table row counts grow...

So any ideas welcome!

Usually it's faster to run several queries than sorting the table by rand(). Firstly get the random number of the row:

select floor( count(*) * rand() ) random_number
from prods
where ownerid > 0 and active = 1

And then get the particular row:

select p.*, count(u.prodid) as count 
from prods p
left outer join usage u on p.prodid = u.prodid
where prodid = (
    select prodid from prods 
    where ownerid > 0 and active = 1 
    limit {$random_number}, 1
)

By the way your subquery returns only one field, so you can use = instead of in operator.