From: Peter Brawley
Date: June 25 2008 4:52pm
Subject: Re: subselect logic
List-Archive: http://lists.mysql.com/mysql/213464
Message-Id: <486277EB.7000007@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------060805060509040406070607"

--------------060805060509040406070607
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>   

--------------060805060509040406070607--