沒想到mysql的子查詢這麼弱

aaqwsh發表於2010-11-24
MySql
select t.id_1, t.id_2
  from aaa t
 where 1 = 1
   and t.id_1 in ( ...)
   and t.id_2 not in (...)
   and t.xxx in
       (select MAX(xxx) xxx
          from aaa t
         where 1 = 1
           and t.id_1 in (...)
           and t.id_2 not in (...)
         GROUP BY t.ID_2)
 order by t.xxx desc LIMIT 10
 
mysql> show profile cpu,block io for query 4;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000021 | 0.000000 |   0.000000 |            0 |             0 |
| checking query cache for query | 0.000151 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables                 | 0.000110 | 0.000000 |   0.000000 |            0 |             0 |
| System lock                    | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| Table lock                     | 0.000077 | 0.000000 |   0.000000 |            0 |             0 |
| init                           | 0.000076 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing                     | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |
| statistics                     | 0.001712 | 0.002000 |   0.000000 |            0 |             0 |
| preparing                      | 0.001671 | 0.001999 |   0.000000 |            0 |             0 |
| executing                      | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| Sorting result                 | 0.000653 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing                     | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |
| statistics                     | 0.001727 | 0.002000 |   0.000000 |            0 |             0 |
| preparing                      | 0.001676 | 0.002000 |   0.000000 |            0 |             0 |
| Creating tmp table             | 0.000030 | 0.000000 |   0.000000 |            0 |             0 |
| executing                      | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| Copying to tmp table           | 0.001329 | 0.001000 |   0.000000 |            0 |             0 |
....................................................................................................
| Sorting result                 | 0.000018 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data                   | 0.000085 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data                   | 0.000030 | 0.000000 |   0.000000 |            0 |             0 |
| end                            | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| removing tmp table             | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |
| end                            | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| query end                      | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items                  | 0.000039 | 0.000000 |   0.000000 |            0 |             0 |
| storing result in query cache  | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |
| logging slow query             | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up                    | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
468 rows in set (0.00 sec)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/758322/viewspace-680170/，如需轉載，請註明出處，否則將追究法律責任。

相關文章