MySQL Forums
Forum List  »  Performance

Query time difference
Posted by: Ofer Bechar
Date: April 26, 2015 07:28AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Query time difference
1711
April 26, 2015 07:28AM
643
April 27, 2015 02:39PM
681
April 27, 2015 11:29PM
653
April 29, 2015 05:50PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.