MySQL query takes a long time to run-CodePudding

My table is defined as following:

CREATE TABLE `tracking_info` (
 `tid` int(25) NOT NULL AUTO_INCREMENT,
 `tracking_customer_id` int(11) NOT NULL DEFAULT '0',
 `tracking_content` text NOT NULL,
 `tracking_type` int(11) NOT NULL DEFAULT '0',
 `time_recorded` int(25) NOT NULL DEFAULT '0',
 PRIMARY KEY (`tid`),
 KEY `time_recorded` (`time_recorded`),
 KEY `tracking_idx` (`tracking_customer_id`,`tracking_type`,
                     `time_recorded`,`tid`)
) ENGINE=MyISAM

The table contains about 150 million records. Here is the query:

SELECT tracking_content, tracking_type, time_recorded 
FROM tracking_info 
WHERE FROM_UNIXTIME(time_recorded) > DATE_SUB( NOW( ) , 
                    INTERVAL 90 DAY )
  AND tracking_customer_id = 111111 
ORDER BY time_recorded DESC 
LIMIT 0,10

It takes about a minute to run the query even without ORDER BY. Any thoughts? Thanks in advance!

CodePudding user response：

You can rephrase the query to isolate time_recorded, as in:

SELECT tracking_content, tracking_type, time_recorded 
FROM tracking_info 
WHERE time_recorded > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 90 DAY))
  AND tracking_customer_id = 111111 
ORDER BY time_recorded DESC 
LIMIT 0,10

Then, the following index will probably make the query faster:

create index ix1 on tracking_info (tracking_customer_id, time_recorded);

CodePudding user response：

First, refactor the query so it's sargable.

SELECT tracking_content, tracking_type, time_recorded 
FROM tracking_info 
WHERE time_recorded > UNIX_TIMESTAMP(DATE_SUB( NOW( ) , INTERVAL 90 DAY )
  AND tracking_customer_id = 111111 
ORDER BY time_recorded DESC 
LIMIT 0,10;

Then add this multi-column index:

ALTER TABLE tracking_info 
    ADD INDEX cust_time (tracking_customer_id, time_recorded DESC);

Why will this help?

It compares the raw data in a column with a constant, rather than using the FROM_UNIXTIME() function to transform all the data in that column of the table. That makes the query sargable.
The query planner can random-access the index I suggest to the first eligible row, then read ten rows sequentially from the index and look up what it needs from the table, then stop.