# Query time huge difference

**URL:** <https://forums.percona.com/t/query-time-huge-difference/4183>\
**Category:** Other MySQL® Questions\
**Created:** [April 26, 2015, 8:01am UTC](https://forums.percona.com/t/query-time-huge-difference/4183 "2015-04-26T08:01:16Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![oferbec](https://avatars.discourse-cdn.com/v4/letter/o/c77e96/32.png) [@oferbec](https://forums.percona.com/u/oferbec)\
**Post date:** [April 26, 2015, 8:01am UTC](https://forums.percona.com/t/query-time-huge-difference/4183/1 "2015-04-26T08:01:16Z")

</div>

Hi,

I’m using version 5.6.23.

The query I’m running:

SELECT stationeve0\_.id AS col\_0\_0\_, stationeve0\_.notify\_on AS col\_1\_0\_, stationeve0\_.event\_date AS col\_2\_0\_, stationeve0\_.source\_id AS col\_3\_0\_, stationeve0\_.event\_type AS col\_4\_0\_, stationeve0\_.log\_level\_id AS col\_5\_0\_, stationeve0\_.action\_result AS col\_6\_0\_, stationeve0\_.relevent\_data AS col\_7\_0\_, stationeve0\_.extra\_data AS col\_8\_0\_, stationeve0\_.transaction\_id AS col\_9\_0\_, stationimp1\_.caption AS col\_10\_0\_, stationimp1\_.identity\_key AS col\_11\_0\_, stationimp1\_.id AS col\_12\_0\_, stationeve0\_.user\_id AS col\_13\_0\_, stationeve0\_.request\_uuid AS col\_14\_0\_  
FROM station\_event\_log stationeve0\_  
INNER JOIN station stationimp1\_ ON stationeve0\_.station\_Id=stationimp1\_.id  
WHERE (stationimp1\_.deleted IS NULL OR stationimp1\_.deleted=0) AND stationeve0\_.service\_provider\_id=3 AND stationeve0\_.notify\_on\>=‘2015-04-25 13:17:27’ AND stationeve0\_.notify\_on\<=‘2015-04-26 13:17:27.593’  
ORDER BY stationeve0\_.notify\_on DESC, stationeve0\_.id DESC  
LIMIT 50

This query is taking a few milliseconds.  
When I’m changing the dates in the where clause and ask for 48 hours instead of 24 it still takes a few milliseconds. When it is 72 hours I have to manually kill the query after 2-3 minutes because the query didn’t finish yet. I modified the ranges until I found a limit of which 1 minute change is the difference between lightning fast results and no results at all. The range limit is dynamic, if yesterday it was 50 hours and 20 minutes (was fast, in 50 hours and 21 minutes it was very slow), today it can be 47 hours and 32 minutes for example.  
I can choose a completely different time range (in the beginning of April or in March for example) and I still see the exact same behavior.  
I’m really clueless at this point, can anyone point me in the right direction and help me understand it and solve it?

The create table of the relevant table:

CREATE TABLE `station_event_log` (  
`id` BIGINT(20) NOT NULL AUTO\_INCREMENT,  
`source_id` INT(11) NOT NULL,  
`event_type` BIGINT(11) NOT NULL,  
`notify_on` DATETIME(3) NOT NULL,  
`extra_data` VARCHAR(3000) NULL DEFAULT NULL,  
`station_id` BIGINT(20) NULL DEFAULT NULL,  
`log_level_id` INT(11) NULL DEFAULT NULL,  
`relevent_data` VARCHAR(3000) NULL DEFAULT NULL,  
`root_action_id` VARCHAR(255) NULL DEFAULT NULL,  
`action_result` VARCHAR(1000) NULL DEFAULT NULL,  
`transaction_id` BIGINT(20) NULL DEFAULT NULL,  
`error_code_id` INT(11) NULL DEFAULT NULL,  
`user_id` BIGINT(20) NULL DEFAULT NULL,  
`station_socket_id` BIGINT(20) NULL DEFAULT NULL,  
`service_provider_id` BIGINT(20) NULL DEFAULT NULL,  
`event_date` DATETIME(3) NOT NULL,  
`request_uuid` VARCHAR(250) NULL DEFAULT NULL,  
PRIMARY KEY (`id`, `notify_on`),  
INDEX `idx_station_event_log_1` (`notify_on`),  
INDEX `idx_station_event_log_2` (`event_type`),  
INDEX `fk_station_event_log_station_socket_id` (`station_socket_id`),  
INDEX `idx_station_event_log_3` (`log_level_id`),  
INDEX `idx_station_event_log_complex_1` (`station_id`, `notify_on`, `service_provider_id`, `event_type`)  
)  
COLLATE=‘utf8\_general\_ci’;

In addition, I have partitions by year on `notify_on` and sub-partitions by month (on the same column).

---

<div class="post-metadata">

**Author:** ![wagnerbianchi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/wagnerbianchi/32/802_2.png) [@wagnerbianchi](https://forums.percona.com/u/wagnerbianchi)\
**Post date:** [April 27, 2015, 1:45pm UTC](https://forums.percona.com/t/query-time-huge-difference/4183/2 "2015-04-27T13:45:31Z")

</div>

Can you share the EXPLAIN and profiler of this query? Additionally, can you run pt-duplicate-keys or even, review the table’s structure in order to check duplicate indexes or unnecessary columns around the index definition on CREATE TABLE?