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
>
