1

I'm trying to optimise a query that performs an inner join with itself. The table size is currently 10GB ~50 million rows.

/* Count all occurrences between start and end site id
   of a given speed threshold between a time range */
SET @lowerThreshold := 2.31;
SET @upperThreshold := 17.85;

SET @startDate := '2014-10-28 00:00:00';
SET @endDate := '2014-11-05 00:00:00';

SET @start_id := 56;
SET @end_id := 62;

-- EXPLAIN
    SELECT 
    COUNT(start.mac) AS count
FROM 
    `Wifi_JourneyTime` AS start
-- FORCE INDEX(route_stats)
        INNER JOIN
    `Wifi_JourneyTime` as end ON start.mac = end.mac

WHERE
    start.event_id = 22
        AND start.begin_site_id = @start_id
        AND start.end_timestamp BETWEEN @startDate AND @endDate
        AND start.spd BETWEEN @lowerThreshold AND @upperThreshold
        AND end.event_id = 22
        AND end.end_site_id = @end_id
        AND end.end_timestamp BETWEEN @startDate AND @endDate
        AND end.spd BETWEEN @lowerThreshold AND @upperThreshold

Here are all of the indexes (yes I have too many, I've been trying to force queries to use them)

+------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name        | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Wifi_JourneyTime |          0 | PRIMARY         |            1 | id              | A         |    17875671 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | begin_site_id   |            1 | begin_site_id   | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | end_site_id     |            1 | end_site_id     | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | end_timestamp   |            1 | end_timestamp   | A         |    17875671 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | begin_timestamp |            1 | begin_timestamp | A         |     8937835 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | event_id        |            1 | event_id        | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | mac             |            1 | mac             | A         |     8937835 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | end_timestamp_2 |            1 | end_timestamp   | A         |    17875671 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | end_timestamp_2 |            2 | mac             | A         |    17875671 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | event_id_2      |            1 | event_id        | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | event_id_2      |            2 | begin_site_id   | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | event_id_2      |            3 | end_site_id     | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | event_id_2      |            4 | end_timestamp   | A         |    17875671 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | event_id_2      |            5 | journey_time    | A         |    17875671 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | class           |            1 | class           | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
| Wifi_JourneyTime |          1 | event_id_3      |            1 | event_id        | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | event_id_3      |            2 | class           | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
| Wifi_JourneyTime |          1 | event_id_3      |            3 | begin_site_id   | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | event_id_3      |            4 | end_site_id     | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | event_id_3      |            5 | end_timestamp   | A         |    17875671 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | event_id_3      |            6 | mac             | A         |    17875671 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | event_id_3      |            7 | journey_time    | A         |    17875671 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | spd             |            1 | spd             | A         |       94082 |     NULL | NULL   | YES  | BTREE      |         |               |
| Wifi_JourneyTime |          1 | route_stats     |            1 | begin_site_id   | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | route_stats     |            2 | id              | A         |    17875671 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | end_site_id_id  |            1 | end_site_id     | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | end_site_id_id  |            2 | id              | A         |    17875671 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | timestamp_id    |            1 | end_timestamp   | A         |    17875671 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | timestamp_id    |            2 | id              | A         |    17875671 |     NULL | NULL   |      | BTREE      |         |               |
| Wifi_JourneyTime |          1 | spd_id          |            1 | spd             | A         |      145330 |     NULL | NULL   | YES  | BTREE      |         |               |
| Wifi_JourneyTime |          1 | spd_id          |            2 | id              | A         |    17875671 |     NULL | NULL   |      | BTREE      |         |               |
+------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

And here is the explain output from the previous query

+------+-------------+-------+------+---------------------------------------------------------------------------------------------------------------------+---------------+---------+------------------------+---------+-------------+
| id   | select_type | table | type | possible_keys                                                                                                       | key           | key_len | ref                    | rows    | Extra       |
+------+-------------+-------+------+---------------------------------------------------------------------------------------------------------------------+---------------+---------+------------------------+---------+-------------+
|    1 | SIMPLE      | start | ref  | begin_site_id,end_timestamp,event_id,mac,end_timestamp_2,event_id_2,event_id_3,spd,route_stats,timestamp_id,spd_id  | begin_site_id | 4       | const                  | 1198134 | Using where |
|    1 | SIMPLE      | end   | ref  | end_site_id,end_timestamp,event_id,mac,end_timestamp_2,event_id_2,event_id_3,spd,end_site_id_id,timestamp_id,spd_id | mac           | 14      | Telemetricor.start.mac |      24 | Using where |
+------+-------------+-------+------+---------------------------------------------------------------------------------------------------------------------+---------------+---------+------------------------+---------+-------------+

Why is the query not using a multi-column index and why is it using begin_site_id? Executing speed is dependent of time range but approximately 20s, how can i speed it up?

EDIT 1 - Add SHOW CREATE TABLE output

+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table            | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Wifi_JourneyTime | CREATE TABLE `Wifi_JourneyTime` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_id` int(11) NOT NULL,
  `begin_site_id` int(11) NOT NULL,
  `begin_timestamp` datetime NOT NULL,
  `end_site_id` int(11) NOT NULL,
  `end_timestamp` datetime NOT NULL,
  `journey_time` time NOT NULL,
  `mac` varchar(12) NOT NULL,
  `class` tinyint(4) DEFAULT NULL,
  `spd` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `begin_site_id` (`begin_site_id`),
  KEY `end_site_id` (`end_site_id`),
  KEY `end_timestamp` (`end_timestamp`),
  KEY `begin_timestamp` (`begin_timestamp`),
  KEY `event_id` (`event_id`),
  KEY `mac` (`mac`),
  KEY `end_timestamp_2` (`end_timestamp`,`mac`),
  KEY `event_id_2` (`event_id`,`begin_site_id`,`end_site_id`,`end_timestamp`,`journey_time`),
  KEY `class` (`class`),
  KEY `event_id_3` (`event_id`,`class`,`begin_site_id`,`end_site_id`,`end_timestamp`,`mac`,`journey_time`),
  KEY `spd` (`spd`),
  KEY `search_index` (`mac`,`event_id`,`begin_site_id`,`end_site_id`,`begin_timestamp`,`end_timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=28136205 DEFAULT CHARSET=latin1 |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  • I would try adding 2 indexes. On (event_id, begin_site_id, mac, end_timestamp, spd) and on (event_id, end_site_id, mac, end_timestamp, spd) – ypercubeᵀᴹ Nov 5 '14 at 15:04
  • This was my first approach and mysql choose to ignore the index. Forcing the index in the query didn't speed the execution time up by much – James Nov 5 '14 at 16:13
  • First, I suggested 2 indexes, not one. Second, is this part correct?: AND start.end_timestamp BETWEEN @startDate AND @endDate (asking because you also have a begin_timestamp column.) – ypercubeᵀᴹ Nov 5 '14 at 16:19
  • Also, edit your question and the SHOW CREATE TABLE Wifi_JourneyTime; output. – ypercubeᵀᴹ Nov 5 '14 at 16:24
  • ypercube, The 2 indexes you first suggested work and the query now executes in 0.8s. Thanks! – James Nov 5 '14 at 16:53

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.