sql - MySQL query slowdown with added another AND clause -
i'm using doctrine2 in symfony2 project , orm produces following query:
select count(p0_.id) sclr0 purchase p0_ left join user u1_ on p0_.user_id = u1_.id left join config c2_ on u1_.id = c2_.id , (c2_.deletedat null) (u1_.organization_id = 7) , (p0_.deletedat null); it returns 176336 , takes server 30 seconds launch it.
if remove and (p0_.deletedat null) clause takes 0.1 sec. why , clause (not or!), should make resutset <= without it, slow down heavily? prove that, here screenshot select profiles; output after query and:
here output of same query, without and (p0_.deletedat null), note newer queries appended below (see query_id = 5):
to make sure not cache matter, launched same query without and (p0_.deletedat null) , sql_no_cache added (see query_id = 8)
this reproducible both in windows mysql ver 14.14 distrib 5.6.14, win64 (x86_64) , ubuntu 5.5.37-0ubuntu0.12.04.1-log. here same experiment on linux more powerful server:
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | query_id | duration | query | +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | 0.00025925 | select sql_no_cache count(p0_.id) sclr0 purchase p0_ left join user u1_ on p0_.user_id = u1_.id left join config c2_ on u1_.id = c2_.id , (c2_.deletedat null) (u1_.organization_id = 7) | | 2 | 0.04896325 | select sql_no_cache count(p0_.id) sclr0 purchase p0_ left join `user` u1_ on p0_.user_id = u1_.id left join config c2_ on u1_.id = c2_.id , (c2_.deletedat null) (u1_.organization_id = 7) | | 3 | 8.35424850 | select sql_no_cache count(p0_.id) sclr0 purchase p0_ left join `user` u1_ on p0_.user_id = u1_.id left join config c2_ on u1_.id = c2_.id , (c2_.deletedat null) (u1_.organization_id = 7) , (p0_.deletedat null) | +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) the query and (p0_.deletedat null) , inner join's takes same time: 
here explain outputs , without and:
table structures.
purchase:
create table `purchase` ( `id` int(11) not null auto_increment, `user_id` int(11) default null, `credit_card_user_id` int(11) default null, `requestor_id` int(11) default null, `organization_supplier_id` int(11) default null, `organization_id` int(11) default null, `deletedat` datetime default null, `status` int(11) not null, `number` int(11) not null, `created_at` datetime not null, `amount` double not null, `xml` longtext collate utf8_unicode_ci not null, `comments` longtext collate utf8_unicode_ci, `payload_id` varchar(255) collate utf8_unicode_ci not null, `error` varchar(255) collate utf8_unicode_ci default null, `shipping_name` varchar(255) collate utf8_unicode_ci not null, `shipping_email` varchar(100) collate utf8_unicode_ci not null, `shipping_phone_name` varchar(20) collate utf8_unicode_ci default null, `shipping_phone` varchar(100) collate utf8_unicode_ci default null, `shipping_fax` varchar(20) collate utf8_unicode_ci default null, `shipping_deliver_to_1` varchar(255) collate utf8_unicode_ci not null, `shipping_deliver_to_2` varchar(255) collate utf8_unicode_ci default null, `is_payment_info_amount_percent` tinyint(1) default null, `temporary_old_id` int(11) default null, `user_address_id` int(11) default null, primary key (`id`), unique key `number_organization` (`number`,`organization_id`), key `idx_9861b36da76ed395` (`user_id`), key `idx_9861b36dd68143a9` (`credit_card_user_id`), key `idx_9861b36da7f43455` (`requestor_id`), key `idx_9861b36d1a81c9f7` (`organization_supplier_id`), key `idx_9861b36d32c8a3de` (`organization_id`), key `idx_9861b36d52d06999` (`user_address_id`), constraint `fk_9861b36d1a81c9f7` foreign key (`organization_supplier_id`) references `organizationsupplier` (`id`), constraint `fk_9861b36d32c8a3de` foreign key (`organization_id`) references `organization` (`id`), constraint `fk_9861b36d52d06999` foreign key (`user_address_id`) references `useraddress` (`id`), constraint `fk_9861b36da76ed395` foreign key (`user_id`) references `user` (`id`), constraint `fk_9861b36da7f43455` foreign key (`requestor_id`) references `requestor` (`id`), constraint `fk_9861b36dd68143a9` foreign key (`credit_card_user_id`) references `creditcarduser` (`id`) ) engine=innodb auto_increment=359199 default charset=utf8 collate=utf8_unicode_ci user
create table `user` ( `id` int(11) not null, `organization_id` int(11) default null, `username` varchar(100) collate utf8_unicode_ci not null, `password` varchar(100) collate utf8_unicode_ci default null, `salt` varchar(100) collate utf8_unicode_ci not null, `roles` longtext collate utf8_unicode_ci not null comment '(dc2type:json_array)', `firstname` varchar(255) collate utf8_unicode_ci not null, `lastname` varchar(255) collate utf8_unicode_ci not null, `middlename` varchar(255) collate utf8_unicode_ci default null, `email` varchar(255) collate utf8_unicode_ci not null, `is_enabled` tinyint(1) default null, `login_attempts` int(11) not null, `employee_id` varchar(255) collate utf8_unicode_ci default null, `created_at` datetime not null, `updated_at` datetime default null, `password_changed_at` datetime not null, `allow_all_suppliers` tinyint(1) not null, primary key (`id`), key `idx_2da1797732c8a3de` (`organization_id`), constraint `fk_2da1797732c8a3de` foreign key (`organization_id`) references `organization` (`id`), constraint `fk_2da17977bf396750` foreign key (`id`) references `config` (`id`) on delete cascade ) engine=innodb default charset=utf8 collate=utf8_unicode_ci config
create table `config` ( `id` int(11) not null auto_increment, `deletedat` datetime default null, `discr` varchar(255) collate utf8_unicode_ci not null, primary key (`id`) ) engine=innodb auto_increment=5041 default charset=utf8 collate=utf8_unicode_ci
for query and clause, mysql has actual purchase record retrieve deletedat value. according explain result, occurs 35 times each 1968 user records.
without and, mysql able use idx_9861b36da76ed395 index retrieve purchase user_id , id (for count), doesn't have actual purchase record other information.
adding following multi-column index may improve situation: purchase (user_id, deletedat)
Comments
Post a Comment