CODEWITHSUNDEEP
mysqlperformancequery-optimization
Vaeianor
Vaeianor

Reputation: 179

MySQL query takes a long time to run

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!

Upvotes: 0

Views: 103

Answers (3)

Rick James
Rick James

Reputation: 142208

There are 3 things to do:

  • Change to InnoDB.

  • Add INDEX(tracking_customer_id, time_recorded)

  • Rephrase to time_recorded > NOW() - INTERVAL 90 DAY)

Non-critical notes:

  • int(25) -- the "25" has no meaning. You get a 4-byte signed number regardless.
  • There are datatypes DATETIME and TIMESTAMP; consider using one of them instead of an INT that represents seconds since sometime. (It would be messy to change, so don't bother.)
  • When converting to InnoDB, the size on disk will double or triple.

Upvotes: 0

O. Jones
O. Jones

Reputation: 108641

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?

  1. 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.

  2. 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.

Upvotes: 2

The Impaler
The Impaler

Reputation: 48770

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);

Upvotes: 1

Related Questions