List:General Discussion« Previous MessageNext Message »
From:Kip Turk Date:June 25 2008 3:54pm
Subject:subselect logic
View as plain text  
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
Thread
subselect logicKip Turk25 Jun
  • Re: subselect logicPeter Brawley25 Jun
    • Re: subselect logicKip Turk25 Jun