Mysql – Query slower with index on 14M rows table

MySQLoptimizationperformancequery-performance

DB: Amazon RDS MySQL (OS: Linux, 2 vCPU, Memory: 8GB)

I have a table with almost 14M rows of data.

CREATE TABLE `meterreadings` (
 `Id` bigint(20) NOT NULL AUTO_INCREMENT,
 `meterid` varchar(16) DEFAULT NULL,
 `metervalue` int(11) DEFAULT NULL,
 `date_time` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`Id`),
 KEY `meterid` (`meterid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

As you can see, I use an index on meterid.

Another table which stores device IDs (around 100 rows of data)

CREATE TABLE `devices` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`meterid` varchar(16) DEFAULT NULL,
`location` varchar(8) DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `meterid_UNIQUE` (`meterid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

To get 15 minute aggregated data, I use the below query

SELECT AVG(metervalue) as value
 , DATE_FORMAT(date_time, "%d %b %Y %H:%i") as label 
FROM meterreadings 
WHERE meterid IN (SELECT meterid from devices) 
  AND date_time BETWEEN '2018-07-23' AND '2018-07-24' 
GROUP BY DATE(date_time), HOUR(date_time), MINUTE(date_time) DIV 15 
ORDER BY date_time ASC;

Query performance is very bad – It takes approximately around 12 seconds to execute, and causes a temporary spike in DB server usage as well.

EXPLAIN on this query returned this:

1   SIMPLE  devices index   meterid_UNIQUE  meterid_UNIQUE  19      125  
   Using where; Using index; Using temporary; Using filesort
1   SIMPLE  meterreadings   ref meterid meterid 19  devices.meterid 322  
   Using where

I dropped the index on meterreadings and surprisingly the query performance is better – almost about 6 seconds now. I am still wondering why?

EXPLAIN on the query after dropping the index

1   SIMPLE  meterreadings   ALL                 14580167    Using where; 
Using temporary; Using filesort
1   SIMPLE  devices ref meterid_UNIQUE  meterid_UNIQUE  19   
   meterreadings.meterid    1   Using index

I am currently doing my query operation on the table without index – Is there a way I can optimize the table / query to do the operation faster (like a composite index on two columns?)

[The table is growing approximately by around 40 rows per second]

Best Answer

You should play with it a bit, because it might not be clear beforehand what solution will produce the best results.

A few points to consider

It is very likely that an index on the date column will provide you with better selectivity, as it has a higher selectivity.
Composite indexes are usually a good idea, but please make sure to chose the order correctly date_time, meterid vs. meterid, date_time. In most cases it makes more sense to leave columns with dense values (i.e dates, floats) to the end, as any column in the index following them is unlikely to have any effect. ( try meterid, date_time for an index.)
Subselects might force the optimizer to use a specific plan. Try converting it into a join if possible.

Related Solutions

Mysql – Identical query, tables, but different EXPLAIN and performance

Just make a simple join. Sub-queries does not provide the best result quite often

EXPLAIN SELECT l.id, l.level_name, l.date_published, l.rating
FROM levels AS l
INNER JOIN users_favorites AS uf 
ON uf.level_id = l.id
WHERE l.user_id = 2;

MySQL – How to Decide Which Execution Plan is Better

Multiplying the rows is invalid for several reasons:

Many times, the rows examined are an approximation (based on statistics, not accurate), good for query plan selection, but not for performance calculation
The total number of rows examined on a nested loop join (A, B) is not rows_examined_on_table_A * rows_examined_on_table_B, but rows_examined_on_table_A + rows_returned_from_table_A * rows_examined_on_table_B. Where clauses can make a huge difference on that, although it is true that the mentioned calculations is many times used as a broad approximation, assuming the indexes are being created properly and the main causes of filtering out results.
Modern MySQL versions do not use always a nested loop join approach for executing joins and subqueries. Check 5.6 subquery optimizations and other optimization documents on the same manual. Additionally, some of the new optimization techniques do not modify the predicted examined rows, which at some times can be way lower than the one printed, even if it has been calculated exactly.

In particular, on your first query, you are hitting a well know MySQL bug? limitation? in which an IN subquery is identified as a DEPENDENT SUBQUERY, even if it really isn't, forcing the outmost query to be executed without an index (full table scan) in order to test all possible values of the first table. That is usually an indicator that it is a bad query. It seems not to bee too bad in this case, as the table is small, but it is usually an indication of bad performance.

The other thing that should bring your attention is the Using temporary; Using filesort. Filtering is not the only thing where you should focus, as these extra pieces of information are telling you that a large sorting has to be done using a temporary table (that may or may not end up on disk, but at least has to be materialized). That is another indicator of potential bad performance, that in some cases can be avoided with the right indexes.

I will not tell you which is the right query to use (partially, because I do not know all the variables: indexes, tables structure, etc., and in most cases it will depend on the particular hardware/resources available), but I will tell you the tools to decide:

Profile the query- obtain the post execution times and how much of it it is being invested in what. You can use SHOW PROFILES up to 5.5, and the performance_schema starting with 5.6.
As time can be sometimes variable (for example, depending on other queries being executed at the same time, depending on the buffer pool contents) Obtain post-execution statistics with SHOW SESSION STATUS. In particular:
```
FLUSH SESSION STATUS;
SELECT ... ;
SHOW STATUS like 'Hand%';
```
will give you the exact number of handler calls done (approximately, the number of rows read and written for that particular query- although that is not 100% accurate, as it depends on the particular engine implementation).

You may also want to monitor other status variables, like the created temporary tables, created temporary tables on disk and sort passes/sorted rows.

All of these will give you post-execution, exact, time-independent parameters to evaluate the performance of a query. Percona even has a patch for the slow log to output that information on the logs instead of using performance_schema.

With those extra pieces of information you will be able to evaluate more objectively which query is better, and not relying exclusively on EXPLAIN, which only provides limited pre-execution information.

Best Answer

Related Solutions

Mysql – Identical query, tables, but different EXPLAIN and performance

MySQL – How to Decide Which Execution Plan is Better

Related Question