[...]
Michael Stassen wrote:
Don't bother. This is a very expensive solution. You get nearly a Cartesian product on each JOIN. I've got a 40 row test table with 20 values in each of 2 groups. The top 3 version of this examines 2302 rows to produce the 3 values for each of the 2 groups. The top 10 version has been running for several minutes...
It just finished:
2 rows in set (7 min 41.06 sec)
Nearly 8 minutes to get the top 10 for two 20-row groups. This definitely doesn't scale.
My (new) computer used 3 minutes, top 5 values took 3 seconds.
I have used similar code in production, but it was not "top 10", it was more like top 4, and it worked just fine, millions of rows. You say nearly cartesian product "on each join", I would say nearly cartesian within each group. The size of the groups very much matters in this case. In my case the groups was typically 1-5 in size, sometimes 10 or even 100, but mostly (99%) small groups.
"top 50" and definitely "top 100" would never work with this approach, as MySQL can only join 31 (or 63 on 64 bit platform) tables.
-- Roger
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
