MySQL Forums
Forum List  »  Performance

Re: MySQL query execution time differs between executions
Posted by: Ofer Bechar
Date: November 17, 2014 02:57AM

Thanks Rick,

Here's the query - I'm sorry for the hibernate aliases:

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_.id as col_11_0_, stationeve0_.user_id as col_12_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>='2014-11-07 19:18:07.0' and stationeve0_.notify_on<='2014-11-10 19:18:07.0' order by stationeve0_.notify_on desc, stationeve0_.id desc limit 50;

The create table for station_event_log:

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 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 NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `uk_station_event_log_1` (`id`),
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`),
CONSTRAINT `fk_station_event_log_2_station_id` FOREIGN KEY (`station_id`) REFERENCES `station` (`id`),
CONSTRAINT `fk_station_event_log_2_station_socket_id` FOREIGN KEY (`station_socket_id`) REFERENCES `station_socket` (`id`)
)


This is the explain plan again:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE stationimp1_ ref_or_null PRIMARY,idx_station_complex_1 idx_station_complex_1 2 const 55 Using where; Using index; Using temporary; Using filesort
1 SIMPLE stationeve0_ ref idx_station_event_log_1,idx_station_event_log_complex_1 idx_station_event_log_complex_1 9 DMS.stationimp1_.id 575 Using index condition


The station table is a table with over 100 columns, the relevant columns and index:

CREATE TABLE `station` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`caption` VARCHAR(255) NULL DEFAULT NULL,
`deleted` BIT(1) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_station_complex_1` (`deleted`, `caption`)
)


I know I can do the deletes and updates with one statement (for each), but the safest way for me, that causes as few locking time as possible is the current way (and without the many deletes, for the reason I'm trying to understand, the query is much slower).

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MySQL query execution time differs between executions
1086
November 17, 2014 02:57AM


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.