Mysql – How to re-write this query or restructure the table for SPEED

datetimegroup byMySQLperformance

I have an InnoDB MySQL table of this structure:

column	type
service	varchar(16)
url	varchar(255)
datetime	datetime

It contains logs for requests made to one of multiple services. It contains ~3 million rows, and gets ~3 million more rows a month (but I will delete old data if need be).

I am trying to generate a report, and get the number of requests made to each service within a date range.

Here is my current query:

  SELECT service, COUNT(*) as lastMonthCount 
    FROM request_logs 
   WHERE datetime > '2021-02-16 10:51:05' 
GROUP BY service

This works, but is painfully slow (~28 seconds).

It outputs this:

service	lastMonthCount
API A	3056752
API B	38451

I have indexes on datetime and service. I can see they are of type BTREE.

How can I radically speed up this query, or restructure my table/indexes so I can achieve the same use case another way?

Best Answer

For a relatively simple query like that there isn't a lot you can do to optimise it as there is little room for change (there isn't a simple way of asking for that data).

You can probably reduce the amount of pages being touched as it runs significantly by having an index on datetime and service. This way the data it needs to group by will already be available in what it has read to perform the filter on the date. This will increase the amount of data on disk as the index will be larger, and slow down writes a touch for the same reason. You would probably want to replace the existing index on datetime with the new composite index instead of just adding it, for those reasons.

A little more detail on why this will be faster:

With the index just on the datetime, it will need to read the base table data pages to get the service column for each matching row which as well as being extra page reads just because of referencing the other structure it will possibly be many more page reads because those pages contain less rows each due to the larger data per row (they include the URL column and any other properties that you add, or might already have, for the services).

If that still isn't fast enough...

You may need to look at some form of caching for the counts. There are several options here:

a materialised view may work if they are intelligent enough in mysql
a little denormalising, by including a counts table updated by trigger when the main table is updated (this is essentially a more "manual" version of a materialised view)
something in the application layer, if you can live with the result not necessarily being 100% up-to-date every time (the fastest option by far if done right, but obviously with that key disadvantage)
if the data is big enough that the query is reading from storage rather than RAM each time and you don't want to use one/more of the three options above: throw hardware at the problem and buy oodles of RAM! (this is usually not a good solution, though sometimes can be)

1. Table partitioning

Because of the [AND traffic.time >= 1343772000 AND traffic.time < 1346450399] clause, I imagine that you never delete data from this table, or that the table is currently storing data for multiple months. The values in the column [time] seem to be unix timestamps (1346450399 = Fri, 31 Aug 2012 21:59:59 GMT) Partition the table based on time column. That will speed up the data retrieval, as the DB will scan the coresponding partition (much faster then to scan whole table).

A great partitioning tutorial can be found here: the http://www.arachna.com/roller/spidaman/entry/scaling_rails_with_mysql_table
You will need to calculate the timestamp ranges for that, but it should not be hard to do.
Ex: (1346450399 - 1343772000) / 60 / 60 / 24 =~ 31 days. So the max value for the partition holding the data for september (has also 31 days) will be: 1346450399 + ( 31 * 24 * 60 * 60)
An unix to date calculator can be found here: http://www.onlineconversion.com/unix_time.htm

2. Rewrite the query

Because the "OR" in your WHERE block, the optimizer will choose not to use the index defined. Try to split the query in 2 selects, and make an union.

SELECT 
    traffic.time, 
    SUM(traffic.bytesin), 
    SUM(traffic.bytesout) 
FROM 
    traffic 
WHERE traffic.type LIKE 'v4_assignment' 
    AND type_id IN (1,2,3,4)
    AND traffic.time >= 1343772000 AND traffic.time <= 1346450399 
GROUP BY 
    traffic.time
UNION
SELECT 
    traffic.time, 
    SUM(traffic.bytesin), 
    SUM(traffic.bytesout) 
FROM 
    traffic 
WHERE traffic.type LIKE 'v4_host' 
    AND type_id IN (5,6,7,8)
    AND traffic.time >= 1343772000 AND traffic.time <= 1346450399 
GROUP BY 
    traffic.time
ORDER BY 
    traffic.time

3. New index based on data cardinality

Based on your explain output, I don't see the index beeing used. Maybe because the optimizer decides that it will be easier (cheaper) to make a full table scan then to follow the index. Also, in your current index, the first column has a lower cardinality then the next 2 ones. The first column in any index should be the column with the best (max) cardinality.

Create a new index as:

MYSQL> CREATE INDEX MTIhai_traffic_idx1 ON traffic(time, type, type_id)

Mysql – How to speed up queries on a large 220 million rows table (9 gig data)

Thoughts on the issue, thrown in random order:

The obvious index for this query is: (rated_user_id, rating). A query that gets data for only one of the million users and needs 17 seconds is doing something wrong: reading from the (rated_user_id, rater_user_id) index and then reading from the table the (hundreds to thousands) values for the rating column, as rating is not in any index. So, the query has to read many rows of the table which are located in many different disk locations.
Before starting adding numerous indexes in the tables, try to analyze the performance of the whole database, the whole set of slow queries, examine again the choices of the datatypes, the engine you use and the configuration settings.
Consider moving to a newer version of MySQL, 5.1, 5.5 or even 5.6 (also: Percona and MariaDB versions.) Several benefits as bugs have been corrected, the optimizer improved and you can set the low threshold for slow queries to less than 1 second (like 10 milliseconds). This will give you far better info about slow queries.
The choice for the datatype of rating is weird. VARCHAR(1)? Why not CHAR(1)? Why not TINYINT? This will save you some space, both tin the table and in the indexes that (will) include that column. A varchar(1) column needs one more byte over char(1) and if they are utf8, the (var)char columns will need 3 (or 4) bytes, instead of 1 (tinyint).

Best Answer

Related Solutions

Mysql – How to improve the speed of a query on a 20 million+ row table

1. Table partitioning

2. Rewrite the query

3. New index based on data cardinality

Mysql – How to speed up queries on a large 220 million rows table (9 gig data)

Related Question