MySQL添加索引优化SQL-11774929
mysql> explain SELECT-> t.total_meeting_num,
-> r.voip_user_num
-> FROM
-> (
-> SELECT
-> count(*) total_meeting_num
-> FROM
-> Conference
-> WHERE
-> isStart = 1
-> AND startTime >= ADDDATE(now(), - 1)
-> AND billingcode != 651158
-> AND billingcode != 651204
-> ) t,
-> (
-> SELECT
-> count(userID) voip_user_num
-> FROM
-> (
-> SELECT
-> conferenceID,
-> userID,
-> isOnline,
-> createdTime
-> FROM
-> (
-> SELECT
-> *
-> FROM
-> ConferenceUser
-> WHERE
-> createdTime >= ADDDATE(now(), - 1)
-> AND userID > 1000
-> ORDER BY
-> userID,
-> createdTime DESC
-> ) t
-> GROUP BY
-> userID
-> ) t,
-> (
-> SELECT
-> *
-> FROM
-> Conference
-> WHERE
-> isStart = 1
-> AND startTime >= ADDDATE(now(), - 1)
-> AND conferenceName NOT LIKE 'evmonitor%'
-> ) r
-> WHERE
-> t.isOnline = 1
-> AND t.conferenceID = r.conferenceID
-> ) r;
+----+-------------+----------------+--------+-------------------+-------------------+---------+------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref| rows| Extra |
+----+-------------+----------------+--------+-------------------+-------------------+---------+------+-------+---------------------------------+
|1 | PRIMARY | | system | NULL | NULL | NULL | NULL | 1 | |
|1 | PRIMARY | | system | NULL | NULL | NULL | NULL | 1 | |
|3 | DERIVED | | ALL | NULL | NULL | NULL | NULL | 20 | |
|3 | DERIVED | | ALL | NULL | NULL | NULL | NULL | 12682 | Using where; Using join buffer|
|6 | DERIVED | Conference | range| ind_start_time | ind_start_time | 5 | NULL | 879 | Using where |
|4 | DERIVED | | ALL | NULL | NULL | NULL | NULL | 18951 | Using temporary; Using filesort |
|5 | DERIVED | ConferenceUser | range| index_createdtime | index_createdtime | 4 | NULL | 31455 | Using where; Using filesort |
|2 | DERIVED | Conference | range| ind_start_time | ind_start_time | 5 | NULL | 879 | Using where |
+----+-------------+----------------+--------+-------------------+-------------------+---------+------+-------+---------------------------------+
8 rows in set (0.18 sec)
页:
[1]