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
