CODEWITHSUNDEEP
mysqlsql
TareK Khoury
TareK Khoury

Reputation: 13021

Painfully slow MySQL query with indexed columns

I have a table with 4 million rows in total.

When I run the following query, it takes 40 seconds to complete

SELECT * FROM `traffic` 
WHERE `callstart_timestamp` >= '2016-09-01 00:00:00' 
AND `callend_timestamp` <= '2016-09-18 00:00:00' 
AND app = 'XXXX'

416040 total, Query took 40.0631 seconds.

If i remove the condition AND app = 'XXXX' from the query, it will finish in less than a second.

Can you please advise what might be causing the problem, since all the columns are indexed?

Query EXPLAIN:

SIMPLE; traffic; NULL; ref; app,callend_timestamp,callstart_timestamp; app; 22; const; 1976467; 12.13; Using where;

CREATE:

CREATE TABLE `traffic` (
  `id` varchar(20) NOT NULL,
  `user_cli` varchar(15) NOT NULL,
  `ddi` varchar(15) DEFAULT NULL,
  `callstart_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `callend_timestamp` timestamp NULL DEFAULT NULL,
  `app` varchar(20) NOT NULL,
  `lang` char(2) NOT NULL DEFAULT 'en'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE `traffic`
  ADD PRIMARY KEY (`id`),
  ADD KEY `app` (`app`),
  ADD KEY `callend_timestamp` (`callend_timestamp`),
  ADD KEY `callstart_timestamp` (`callstart_timestamp`),
  ADD KEY `ddi` (`ddi`);

UPDATE:

I have implemented some of the answers below and they helped a lot! I will try to figure out which answer better suites my case. I will update with the results.

Upvotes: 2

Views: 87

Answers (2)

Bohemian
Bohemian

Reputation: 425053

The standard answer is to create an index on all 3 columns:

create index traffic_001 on traffic(app, callstart_timestamp, callend_timestamp)

Which follows the general principle of putting exact matching columns before open-ended range matching ones in the index column list.

But there's another idea that I haven't seen done before that might work:

SELECT * FROM traffic 
WHERE callstart_timestamp between '2016-09-01 00:00:00' and callend_timestamp
AND callend_timestamp between callstart_timestamp and '2016-09-18 00:00:00' 
AND app = 'XXXX'

Logically the start/end values are bounded by each other. Maybe coding this fact into the query will help without adding an index.

Upvotes: 2

sagi
sagi

Reputation: 40481

Try adding 1 index for all 3 columns instead of separate indexes for each one which may confuse the optimizer with the execution plan:

CREATE INDEX idx_1
ON traffic(app,callstart_timestamp,callend_timestamp);

Upvotes: 1

Related Questions