List:General Discussion« Previous MessageNext Message »
From:Kip Turk Date:June 25 2008 5:23pm
Subject:Re: subselect logic
View as plain text  
Awesome, thanks.  My first attempt is able to run on the full table in 
85 seconds.  I'll continue to read up on these queries to see if I can 
optimize it further.

Thanks again.

Peter Brawley wrote:
> Kip,
> 
>  > What can I do to optimize this  query?
> 
> For more efficient alternatives see "Within-group aggregates" at 
> http://www.artfulsoftware.com/queries.php.
> 
> PB
> 
> -----
> 
> Kip Turk wrote:
>> I'm having problems optimizing a series of subselects.  I have the 
>> following sample table:
>>
>> mysql> select * from fake order by msgid, id desc;
>> +----+-------+-----+---------------------+
>> | id | msgid | nec | dt                  |
>> +----+-------+-----+---------------------+
>> | 10 |     1 | 300 | 2008-06-25 09:18:05 |
>> |  9 |     1 | 301 | 2008-06-25 09:18:02 |
>> |  6 |     1 | 305 | 2008-06-25 09:15:40 |
>> |  5 |     1 | 301 | 2008-06-25 09:15:32 |
>> |  2 |     1 | 301 | 2008-06-25 09:15:10 |
>> |  1 |     1 | 300 | 2008-06-25 09:15:04 |
>> | 11 |     2 | 300 | 2008-06-25 09:18:13 |
>> |  8 |     2 | 305 | 2008-06-25 09:17:49 |
>> |  4 |     2 | 305 | 2008-06-25 09:15:19 |
>> |  3 |     2 | 301 | 2008-06-25 09:15:14 |
>> |  7 |     3 | 305 | 2008-06-25 09:17:44 |
>> | 12 |     4 | 300 | 2008-06-25 09:23:22 |
>> | 14 |     5 | 305 | 2008-06-25 09:23:39 |
>> | 13 |     5 | 301 | 2008-06-25 09:23:33 |
>> | 15 |     6 | 300 | 2008-06-25 09:23:45 |
>> +----+-------+-----+---------------------+
>>
>> I'm trying to grab and count the nec for the highest id entry for each 
>> distinct msgid.  To get the correct entries, I can use:
>>
>> mysql> select * from (select * from fake order by id desc) as fake1 
>> group by msgid;
>> +----+-------+-----+---------------------+
>> | id | msgid | nec | dt                  |
>> +----+-------+-----+---------------------+
>> | 10 |     1 | 300 | 2008-06-25 09:18:05 |
>> | 11 |     2 | 300 | 2008-06-25 09:18:13 |
>> |  7 |     3 | 305 | 2008-06-25 09:17:44 |
>> | 12 |     4 | 300 | 2008-06-25 09:23:22 |
>> | 14 |     5 | 305 | 2008-06-25 09:23:39 |
>> | 15 |     6 | 300 | 2008-06-25 09:23:45 |
>> +----+-------+-----+---------------------+
>>
>> And to get the counts, I can use:
>> mysql> select nec, count(nec) as count from (select * from (select * 
>> from fake order by id desc) as fake1 group by msgid) as fake2 group by 
>> nec;
>> +-----+-------+
>> | nec | count |
>> +-----+-------+
>> | 300 |     4 |
>> | 305 |     2 |
>> +-----+-------+
>>
>> So on my tiny test table, the logic is valid to get the results I 
>> want.  However, on my actual table with several million lines, the 
>> nested selects makes this a pretty ugly option (to the point even 
>> explain took a few minutes to respond).  What can I do to optimize 
>> this  query?
>>
>> Thanks,
>> Kip Turk
>>
>> ------------------------------------------------------------------------
>>
>>
>> No virus found in this incoming message.
>> Checked by AVG. Version: 8.0.101 / Virus Database: 270.4.1/1518 - 
>> Release Date: 6/25/2008 9:46 AM
>>   
> 

Thread
subselect logicKip Turk25 Jun
  • Re: subselect logicPeter Brawley25 Jun
    • Re: subselect logicKip Turk25 Jun