1

I have this query:

SELECT mac, pid, node_category.name, status, computername, user_agent,
detect_date, regdate, unregdate, last_dhcp, node.notes, 
(SELECT ip FROM iplog WHERE iplog.mac = node.mac ORDER BY start_time DESC LIMIT 1) as ip,
(SELECT ssid FROM locationlog WHERE locationlog.mac = node.mac ORDER BY start_time DESC LIMIT 1) as ssid FROM node LEFT OUTER JOIN node_category ON (node.category_id = node_category.category_id) 
WHERE pid = 'someid';

The slow query log shows this:

Query_time: 38.660259 Lock_time: 0.000188 Rows_sent: 8 Rows_examined: 27428978

The explain for the query is this:

+----+--------------------+---------------+--------+----------------------+------------+---------+---------------------+------+-------------+
| id | select_type        | table         | type   | possible_keys        | key        | key_len | ref                 | rows | Extra       |
+----+--------------------+---------------+--------+----------------------+------------+---------+---------------------+------+-------------+
|  1 | PRIMARY            | node          | ref    | pid                  | pid        | 257     | const               |    8 | Using where |
|  1 | PRIMARY            | node_category | eq_ref | PRIMARY              | PRIMARY    | 4       | pf.node.category_id |    1 |             |
|  3 | DEPENDENT SUBQUERY | locationlog   | index  | locationlog_view_mac | start_time | 8       | NULL                |    1 | Using where |
|  2 | DEPENDENT SUBQUERY | iplog         | index  | mac,mac_view_open    | start_time | 8       | NULL                |    1 | Using where |
+----+--------------------+---------------+--------+----------------------+------------+---------+---------------------+------+-------------+

With the following indexes:

mysql> show index from iplog;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| iplog |          1 | mac           |            1 | mac         | A         |       31800 |     NULL | NULL   |      | BTREE      |         |
| iplog |          1 | ip_view_open  |            1 | ip          | A         |      182851 |     NULL | NULL   |      | BTREE      |         |
| iplog |          1 | ip_view_open  |            2 | end_time    | A         |     6582656 |     NULL | NULL   | YES  | BTREE      |         |
| iplog |          1 | mac_view_open |            1 | mac         | A         |       11428 |     NULL | NULL   |      | BTREE      |         |
| iplog |          1 | mac_view_open |            2 | end_time    | A         |     6582656 |     NULL | NULL   | YES  | BTREE      |         |
| iplog |          1 | start_time    |            1 | start_time  | A         |     6582656 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

+-------------+------------+-----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name                    | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+-----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| locationlog |          1 | locationlog_view_mac        |            1 | mac             | A         |       83815 |     NULL | NULL   | YES  | BTREE      |         |
| locationlog |          1 | locationlog_view_mac        |            2 | end_time        | A         |     4106982 |     NULL | NULL   | YES  | BTREE      |         |
| locationlog |          1 | locationlog_view_switchport |            1 | switch          | A         |          17 |     NULL | NULL   |      | BTREE      |         |
| locationlog |          1 | locationlog_view_switchport |            2 | port            | A         |          17 |     NULL | NULL   |      | BTREE      |         |
| locationlog |          1 | locationlog_view_switchport |            3 | end_time        | A         |     4106982 |     NULL | NULL   | YES  | BTREE      |         |
| locationlog |          1 | locationlog_view_switchport |            4 | vlan            | A         |     4106982 |     NULL | NULL   | YES  | BTREE      |         |
| locationlog |          1 | start_time                  |            1 | start_time      | A         |     4106982 |     NULL | NULL   |      | BTREE      |         |
| locationlog |          1 | connection_type             |            1 | connection_type | A         |          17 |     NULL | NULL   |      | BTREE      |         |
+-------------+------------+-----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+

locationlog has about 4 million rows, and iplog has about 6 million. Can anyone see why this query is taking so long and why it is examining so many rows? I really appreciate it!

  • 1
    Run EXPLAIN on your query and post the results. – Vérace Aug 27 '14 at 8:10
  • I posted the EXPLAIN up above..did I do the EXPLAIN incorrectly? – user46302 Aug 27 '14 at 16:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.