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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
