Hi Shawn-
This worked great. Perfect. Thanks very
much for your help. If I can ever be of help, let me know - I owe you
one. And thanks Michael and Roger for your help too.
Best regards,
Rick
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, December 03, 2004 2:14 PM
To: [EMAIL PROTECTED]
Cc: Michael Stassen; 'Mysql'; Roger Baklund
Subject: Re: Use of limit with IN on subquery
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]
>
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
