Improving MySQL Query Speeds - 150,000+ Rows Returned Slows Query

Question

Hi I currently have a query which is taking 11(sec) to run. I have a report which is displayed on a website which runs 4 different queries which are similar and all take 11(sec) each to run. I don't really want the customer having to wait a minute for all of these queries to run and display the data.

I am using 4 different AJAX requests to call an APIs to get the data I need and these all start at once but the queries are running one after another. If there was a way to get these queries to all run at once (parallel) so the total load time is only 11(sec) that would also fix my issue, I don't believe that is possible though.

Here is the query I am running:

SELECT device_uuid,
     day_epoch,
     is_repeat
FROM tracking_daily_stats_zone_unique_device_uuids_per_hour
WHERE day_epoch >= 1552435200
AND day_epoch < 1553040000
AND venue_id = 46
AND zone_id IN (102,105,108,110,111,113,116,117,118,121,287)

I can't think of anyway to speed this query up at all, below are pictures of the table indexes and the explain statement on this query.

I think the above query is using relevant indexes in the where conditions.

If there is anything you can think of to speed this query up please let me know, I have been working on it for 3 days and can't seem to figure out the problem. It would be great to get the query times down to 5(sec) maximum. If I am wrong about the AJAX issue please let me know as this would also fix my issue.

" EDIT "

I have came across something quite strange which might be causing the issue. When I change the day_epoch range to something smaller (5th - 9th) which returns 130,000 rows the query time is 0.7(sec) but then I add one more day onto that range (5th - 10th) and it returns over 150,000 rows the query time is 13(sec). I have ran loads of different ranges and have came to the conclusion if the amount of rows returned is over 150,000 that has a huge effect on the query times.

Table Definition -

CREATE TABLE `tracking_daily_stats_zone_unique_device_uuids_per_hour` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `day_epoch` int(10) NOT NULL,
 `day_of_week` tinyint(1) NOT NULL COMMENT 'day of week, monday = 1',
 `hour` int(2) NOT NULL,
 `venue_id` int(5) NOT NULL,
 `zone_id` int(5) NOT NULL,
 `device_uuid` binary(16) NOT NULL COMMENT 'binary representation of the device_uuid, unique for a single day',
 `device_vendor_id` int(5) unsigned NOT NULL DEFAULT '0' COMMENT 'id of the device vendor',
 `first_seen` int(10) unsigned NOT NULL DEFAULT '0',
 `last_seen` int(10) unsigned NOT NULL DEFAULT '0',
 `is_repeat` tinyint(1) NOT NULL COMMENT 'is the device a repeat for this day?',
 `prev_last_seen` int(10) NOT NULL DEFAULT '0' COMMENT 'previous last seen ts',
 PRIMARY KEY (`id`,`venue_id`) USING BTREE,
 KEY `venue_id` (`venue_id`),
 KEY `zone_id` (`zone_id`),
 KEY `day_of_week` (`day_of_week`),
 KEY `day_epoch` (`day_epoch`),
 KEY `hour` (`hour`),
 KEY `device_uuid` (`device_uuid`),
 KEY `is_repeat` (`is_repeat`),
 KEY `device_vendor_id` (`device_vendor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=450967720 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (venue_id)
PARTITIONS 100 */

Answer 1

The straight forward solution is to add this query specific index to the table:

ALTER TABLE tracking_daily_stats_zone_unique_device_uuids_per_hour 
ADD INDEX complex_idx (`venue_id`, `day_epoch`, `zone_id`)

WARNING This query change can take a while on DB.

And then force it when you call:

SELECT device_uuid,
     day_epoch,
     is_repeat
FROM tracking_daily_stats_zone_unique_device_uuids_per_hour
USE INDEX (complex_idx)
WHERE day_epoch >= 1552435200
AND day_epoch < 1553040000
AND venue_id = 46
AND zone_id IN (102,105,108,110,111,113,116,117,118,121,287)

It is definitely not universal but should work for this particular query.

UPDATE When you have partitioned table you can get profit by forcing particular PARTITION. In our case since that is venue_id just force it:

SELECT device_uuid,
     day_epoch,
     is_repeat
FROM tracking_daily_stats_zone_unique_device_uuids_per_hour
PARTITION (`p46`)
WHERE day_epoch >= 1552435200
AND day_epoch < 1553040000
AND zone_id IN (102,105,108,110,111,113,116,117,118,121,287)

Where p46 is concatenated string of p and venue_id = 46

And another trick if you go this way. You can remove AND venue_id = 46 from WHERE clause. Because there is no other data in that partition.

Answer 2

What happens if you change the order of conditions? Put venue_id = ? first. The order matters.

Now it first checks all rows for:
- day_epoch >= 1552435200
- then, the remaining set for day_epoch < 1553040000
- then, the remaining set for venue_id = 46
- then, the remaining set for zone_id IN (102,105,108,110,111,113,116,117,118,121,287)

When working with heavy queries, you should always try to make the first "selector" the most effective. You can do that by using a proper index for 1 (or combination) index and to make sure that first selector narrows down the most (at least for integers, in case of strings you need another tactic).

---

Sometimes, a query simply is slow. When you have a lot of data (and/or not enough resources) you just cant really do anything about that. Thats where you need another solution: Make a summary table. I doubt you show 150.000 rows x4 to your visitor. You can sum it, e.g., hourly or every few minutes and select from that way smaller table.

---

^{Offtopic: Putting an index on everything only slows you down when inserting/updating/deleting. Index the least amount of columns, just the once you actually filter on (e.g. use in a WHERE or GROUP BY).}

Answer 3

450M rows is rather large. So, I will discuss a variety of issues that can help.

Shrink data A big table leads to more I/O, which is the main performance killer. ('Small' tables tend to stay cached, and not have an I/O burden.)

• Any kind of INT, even INT(2) takes 4 bytes. An "hour" can easily fit in a 1-byte TINYINT. That saves over a 1GB in the data, plus a similar amount in INDEX(hour).
• If hour and day_of_week can be derived, don't bother having them as separate columns. This will save more space.
• Some reason to use a 4-byte day_epoch instead of a 3-byte DATE? Or perhaps you do need a 5-byte DATETIME or TIMESTAMP.

Optimal INDEX (take #1)

If it is always a single venue_id, then either this is a good first cut at the optimal index:

INDEX(venue_id, zone_id, day_epoch)

First is the constant, then the IN, then a range. The Optimizer does well with this in many cases. (It is unclear whether the number of items in an IN clause can lead to inefficiencies.)

Better Primary Key (better index)

With AUTO_INCREMENT, there is probably no good reason to include columns after the auto_inc column in the PK. That is, PRIMARY KEY(id, venue_id) is no better than PRIMARY KEY(id).

InnoDB orders the data's BTree according to the PRIMARY KEY. So, if you are fetching several rows and can arrange for them to be adjacent to each other based on the PK, you get extra performance. (cf "Clustered".) So:

PRIMARY KEY(venue_id, zone_id, day_epoch,  -- this order, as discussed above;
            id)    -- to make sure that the entire PK is unique.
INDEX(id)      -- to keep AUTO_INCREMENT happy

And, I agree with DROPping any indexes that are not in use, including the one I recommended above. It is rarely useful to index flags (is_repeat).

UUID

Indexing a UUID can be deadly for performance once the table is really big. This is because of the randomness of UUIDs/GUIDs, leading to ever-increasing I/O burden to insert new entries in the index.

Multi-dimensional

Assuming day_epoch is sometimes multiple days, you seem to have 2 or 3 "dimensions":

• A date range
• A list of zones
• A venue.

INDEXes are 1-dimensional. Therein lies the problem. However, PARTITIONing can sometimes help. I discuss this briefly as "case 2" in http://mysql.rjweb.org/doc.php/partitionmaint .

There is no good way to get 3 dimensions, so let's focus on 2.

• You should partition on something that is a "range", such as day_epoch or zone_id.
• After that, you should decide what to put in the PRIMARY KEY so that you can further take advantage of "clustering".

Plan A: This assumes you are searching for only one venue_id at a time:

PARTITION BY RANGE(day_epoch)  -- see note below

PRIMARY KEY(venue_id, zone_id, id)

Plan B: This assumes you sometimes srefineearch for venue_id IN (.., .., ...), hence it does not make a good first column for the PK:

Well, I don't have good advice here; so let's go with Plan A.

The RANGE expression must be numeric. Your day_epoch works fine as is. Changing to a DATE, would necessitate BY RANGE(TO_DAYS(...)), which works fine.

You should limit the number of partitions to 50. (The 81 mentioned above is not bad.) The problem is that "lots" of partitions introduces different inefficiencies; "too few" partitions leads to "why bother".

Note that almost always the optimal PK is different for a partitioned table than the equivalent non-partitioned table.

Note that I disagree with partitioning on venue_id since it is so easy to put that column at the start of the PK instead.

Analysis

Assuming you search for a single venue_id and use my suggested partitioning & PK, here's how the SELECT performs:

1. Filter on the date range. This is likely to limit the activity to a single partition.
2. Drill into the data's BTree for that one partition to find the one venue_id.
3. Hopscotch through the data from there, landing on the desired zone_ids.
4. For each, further filter based the date.