What if we used the MySQL-specific feature "group-wise auto_increment" ?
http://dev.mysql.com/doc/mysql/en/example-AUTO_INCREMENT.html >>> For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column)+1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups. <<< So to group his records by k1: CREATE TABLE groupwizeCounter ( id int auto_incrment, , k1 <datatype here> , k2 <datatype here> , total_amt <datatype here> , PRIMARY KEY (k1, id) } type=INNODB; INSERT groupwizeCounter (k1, k2, total_amt) SELECT (k1,k2,total_amt) FROM Z ORDER BY total_amt DESC; SELECT k1, k2, total_amt FROM groupwizeCounter WHERE id <= 10; ORDER BY k1 What do y'all think? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Roger Baklund <[EMAIL PROTECTED]> wrote on 12/03/2004 12:40:24 PM: > Michael Stassen wrote: > > > > 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] >
