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.
AND app = 'XXXX'
SIMPLE; traffic; NULL; ref; app,callend_timestamp,callstart_timestamp; app; 22; const; 1976467; 12.13; Using where;
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`);
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.