MySQL: Use index in background query

indexmysql-5.7optimizationquery-performancetype conversion

I'm using MySQL 5.7.10
I'm checking a new query for an audit report.
I'll execute it in a simple background Unix process, whick invoke mysql from the console.
To check the query, I use a worksheet in HeidiSQL.

The table is:

CREATE TABLE `services` (
`assigned_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`service_id` VARCHAR(10) NOT NULL,
`name` VARCHAR(50) NOT NULL,
...
`audit_insert` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
...
INDEX `idx_audit_insert` (`audit_insert`),
...

);

The simple worksheet is:

SET @numberOfMonths:=6;
SET @today:=CURRENT_TIMESTAMP();
SET @todaySubstractnumberOfMonths=TIMESTAMP( date_sub(@today, interval @numberOfMonths MONTH) );

EXPLAIN SELECT service_id from services where audit_insert between @todaySubstractnumberOfMonths and @today;

The explain output for that query is:
id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,services,[all partitions],ALL,idx_audit_insert,,,,47319735,21.05,Using where

So, index 'idx_audit_insert' is not used.

If I change the query to:

EXPLAIN SELECT service_id where audit_insert between '2020-01-01 00:00:00' and '2020-03-10 23:59:59';

The output is:
id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,tdom_transitos,[all partitions],range,idx_audit_insert,idx_audit_insert,4,,4257192,100.00,Using index condition

Now, the index is used and the rows value is dramatically reduced.

So, my questions are:

How can I force the variables to be timestamp? Is there any wrong in my worksheet?

or maybe

How can I use the index (trying to avoid hints like USE INDEX, FORCE INDEX…)?

(Excuse me if this question is in stackoverflow too. But this related web is more properly for DB questions)
Thanks a lot.

Best Answer

Well, maybe it's not the answer I thought I'd find, but it works perfectly.

I have splitted audit_insert field in another one, audit_insert_datetype, of DATE type. This field has a new index too.

I have changed the query to execute with this field, and I have tried to force the @... variables to be date type (with current_date and date).

The results: the new index is used and the execution time is dramatically reduced. Maybe it's bad style, but it works as I need.

Related Solutions

Mysql – Deciding which MySQL 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.

Mysql – Optimizing a simple query on a large table

It would run faster with InnoDB,

The PRIMARY KEY is 'clustered'; this makes the lookup into name faster.
A suitable setting of innodb_buffer_pool_size of, say, 5G (for your 8GB machine) would cache a lot of stuff, thereby minimizing the I/O for 10K operations.

You could also shrink the data (and decrease the I/O) by using MEDIUMINT UNSIGNED or SMALLINT UNSIGNED where appropriate.

There are redundant indexes that could (should) be removed; this would speed up the load. Also name(6) is probably useless.

Best Answer

Related Solutions

Mysql – Deciding which MySQL execution plan is better

Mysql – Optimizing a simple query on a large table

Related Question