Databases
StackOverflow2013
Postsdbo
POOptimise MySQL ORDER BY RAND() on a filtered GROUP BY query to avoid temp/indexless join
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOptimise MySQL ORDER BY RAND() on a filtered GROUP BY query to avoid temp/indexless join
    text
    copied!<p>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.</p> <p>The query looks like this:</p> <pre><code>select p.*, count(u.prodid) as count from prods p left outer join usage u on p.prodid=u.prodid where p.ownerid&gt;0 and p.active=1 group by p.prodid order by rand() limit 1; </code></pre> <p>I've tried using this style also...</p> <pre><code>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&gt;0 and active=1 group by prodid order by rand() limit 1); </code></pre> <p>but MySQL doesn't support a LIMIT in an 'in' subquery...</p> <p>The explain/describe looks like this...</p> <pre><code>+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+ | 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 | +----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+ </code></pre> <p>2 rows in set (0.00 sec)</p> <p>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...</p> <p>So any ideas welcome!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload