0

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.

0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.