performance - Super slow mysql query due to joins -
i've been trying optimize query , cannot under 15mins! playing around left , inner joins not see real performance benefit. i've tried switch of joins subqueries, nothing there either. reviewed of indexes , reorder them according order of appearance in queries. nada. can work having query run 20s it's running minutes. explain shows maximum results of 546 i'm not sure why taking long. largest table distro_company_locations 92,000 records.
any appreciated. running mysql 5.6.
see below simplified query
explain:
"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "filtered" "extra" "1" "primary" "distro_company_product" "index" "primary,distro_company_product_idx1" "distro_company_product_idx1" "439" "546" "75.09" "using where; using index; using temporary; using filesort" "1" "primary" "distro_company_product_country" "ref" "primary" "primary" "4" "forex.distro_company_product.id" "1" "100" "using index" "1" "primary" "<derived2>" "ref" "<auto_key1>" "<auto_key1>" "4" "forex.distro_company_product.id" "15" "100" "1" "primary" "distro_company_payers_priority" "ref" "distro_company_payers_priority_idx1" "distro_company_payers_priority_idx1" "6" "distro_company_product_payers.payer_id,const" "2" "100" "using where; using index" "1" "primary" "distro_company_product_exchangerates" "ref" "distro_company_product_exchangerates_idx1" "distro_company_product_exchangerates_idx1" "4" "forex.distro_company_product.id" "1" "100" "using index" "1" "primary" "distro_company_product_location" "ref" "primary" "primary" "4" "forex.distro_company_product.id" "17" "100" "using index" "1" "primary" "a" "ref" "primary" "primary" "8" "forex.distro_company_product_location.location_id" "1" "100" "using index" "1" "primary" "b" "all" "1" "100" "using where" "1" "primary" "distro_company_payers_link" "eq_ref" "primary,distro_company_payers_link_idx1" "primary" "8" "func,const" "1" "100" "using where" "1" "primary" "distro_company_locations" "ref" "primary,location_data,distro_company_locations_idx1" "primary" "8" "forex.distro_company_product_location.location_id" "1" "100" "using where" "1" "primary" "distro_company_location_payer" "eq_ref" "primary,distro_company_location_payer_idx1" "primary" "12" "forex.distro_company_locations.id,distro_company_product_payers.payer_id" "1" "100" "using index" "1" "primary" "currencies" "ref" "primary,currencies_idx1" "primary" "2" "forex.distro_company_product_exchangerates.currency_id" "1" "100" "1" "primary" "dc" "ref_or_null" "distro_company_disabled_currency_idx1" "distro_company_disabled_currency_idx1" "8" "forex.distro_company_product_exchangerates.currency_id,const" "2" "100" "using where; not exists; using index" "1" "primary" "distro_company_location_currency" "eq_ref" "primary,distro_company_location_currency_idx1" "primary" "10" "forex.distro_company_product_location.location_id,forex.distro_company_product_exchangerates.currency_id" "1" "100" "1" "primary" "distro_company_automatic_promo" "index" "distro_company_automatic_promo_idx1" "distro_company_automatic_promo_idx1" "18" "2" "100" "using where; using index; using join buffer (block nested loop)" "1" "primary" "promos" "eq_ref" "primary,promos_idx1" "primary" "4" "forex.distro_company_automatic_promo.promo_id" "1" "100" "using where" "1" "primary" "product_id_limits" "ref_or_null" "distro_company_id_limits_idx1" "distro_company_id_limits_idx1" "3" "const" "2" "100" "using where; using index" "1" "primary" "payer_id_limits" "ref" "distro_company_id_limits_idx2" "distro_company_id_limits_idx2" "5" "distro_company_product_payers.payer_id" "1" "100" "using index" "1" "primary" "distro_company_disabled_product" "eq_ref" "primary" "primary" "4" "forex.distro_company_product.id" "1" "100" "using where; not exists; using index" "2" "derived" "<derived4>" "all" "289" "100" "using where; using temporary; using filesort" "2" "derived" "b" "ref" "primary,distro_company_location_payer_idx1" "distro_company_location_payer_idx1" "4" "c.id" "16" "100" "using where; using index" "2" "derived" "a" "ref" "distro_company_product_location_idx1" "distro_company_product_location_idx1" "8" "forex.b.location_id" "1" "100" "using where; using index" "4" "derived" "a" "all" "289" "100" "using temporary; using filesort" "4" "derived" "b" "all" "1" "100" "using where; using join buffer (block nested loop)" "4" "derived" "c" "eq_ref" "primary" "primary" "4" "forex.b.payer_id" "1" "100" "3" "dependent subquery" "d" "all" "1" "100" "using where" "3" "dependent subquery" "e" "ref" "primary" "primary" "4" "forex.a.product_id" "1" "100" "using where; using index" here schemas:
create table `distro_company_product` ( `id` integer(11) not null auto_increment, `company_id` smallint(6) unsigned not null, `delivery_type` tinyint(4) unsigned default null comment '1 - bank deposit\r\n2 - cash pickup\r\n3 - mobile\r\n4 - home delivery\r\n5 - bill pay\r\n6 - top up', `fee_ach` double(15,3) default null, `fee_cc` double(15,3) default null, `fee_debit` double(15,3) default null, `max_limit` double(15,3) unsigned default null, `min_limit` double(15,3) unsigned default null, `fee_misc` double(15,3) default null, `number_of_locations` mediumint(9) unsigned default 0, `special_fields` integer(10) unsigned not null, `partner_id` varchar(20) collate utf8_general_ci default null, `availability` varchar(100) collate utf8_general_ci default null, `require_location` enum('not_required','required_location','required_location_for_price','required_payer','required_payer_for_price') collate utf8_general_ci not null default 'not_required' comment '0 - not require location\r\n1 - requires location\r\n2 - requires location show price\r\n3 - requires payer\r\n4 - requires payer show price', `priority` tinyint(3) unsigned default 0, primary key using btree (`id`, `company_id`), key `distro_company_product_idx1` using btree (`id`, `company_id`, `delivery_type`, `fee_ach`, `fee_cc`, `fee_debit`, `max_limit`, `min_limit`, `priority`, `fee_misc`, `number_of_locations`, `special_fields`, `availability`, `require_location`, `partner_id`) ) engine=innodb auto_increment=3813 character set 'utf8' collate 'utf8_general_ci' ; create table `distro_company_product_country` ( `country_id` smallint(6) unsigned not null, `product_id` integer(11) not null, primary key using btree (`product_id`, `country_id`) ) engine=innodb character set 'utf8' collate 'utf8_general_ci' ; create table `distro_company_payers_priority` ( `payer_id` integer(11) not null, `country_id` smallint(5) unsigned not null, `currency_id` smallint(5) unsigned default null, `company_id` smallint(5) unsigned not null, `priority` smallint(6) not null, `product_id` integer(11) not null, key `distro_company_payers_priority_idx1` using btree (`payer_id`, `company_id`, `product_id`, `country_id`, `currency_id`, `priority`) ) engine=innodb character set 'utf8' collate 'utf8_general_ci' ; create table `distro_company_product_exchangerates` ( `product_id` integer(11) not null, `exchange_rate` double(15,7) unsigned default null, `currency_id` smallint(6) unsigned not null, `last_update` timestamp null default current_timestamp on update current_timestamp, `max_amount` float(9,3) unsigned default null, `min_amount` float(9,3) unsigned default null, `rule_id` integer(11) unsigned not null, primary key using btree (`currency_id`, `product_id`, `rule_id`), unique key `distro_company_product_exchangerates_idx1` using btree (`product_id`, `rule_id`, `currency_id`, `last_update`, `exchange_rate`) ) engine=innodb character set 'utf8' collate 'utf8_general_ci' ; create table `distro_company_product_location` ( `product_id` integer(11) not null, `location_id` bigint(20) not null, primary key using btree (`product_id`, `location_id`), unique key `distro_company_product_location_idx1` using btree (`location_id`, `product_id`) ) engine=innodb character set 'utf8' collate 'utf8_general_ci' ; create table `distro_company_payers_link` ( `payer_id` integer(10) unsigned not null, `company_id` integer(10) unsigned not null, `require_location` tinyint(1) unsigned not null default 0, `fee_rule_id` integer(11) default null, `exchange_rate_rule_id` integer(11) default null, primary key using btree (`payer_id`, `company_id`), key `distro_company_payers_link_idx1` using btree (`payer_id`, `company_id`, `require_location`) ) engine=innodb character set 'utf8' collate 'utf8_general_ci' ; create table `distro_company_locations` ( `id` bigint(20) not null auto_increment, `country_id` smallint(6) unsigned not null, `state` varchar(25) collate utf8_general_ci default null, `city` varchar(50) collate utf8_general_ci default null, `address1` varchar(200) collate utf8_general_ci default null, `address2` varchar(200) collate utf8_general_ci not null, `geocoordinates` point default null, `location_name` varchar(100) collate utf8_general_ci default null, `locationlimit` double(15,3) not null default 0.000 comment 'maximum location payment amount in usd', `special_fields` integer(10) unsigned not null, `hours_of_ops` varchar(100) collate utf8_general_ci default null, `phone` varchar(20) collate utf8_general_ci default null, primary key using btree (`id`, `country_id`), unique key `location_data` using btree (`id`, `country_id`, `state`, `city`, `address1`, `address2`, `geocoordinates`, `location_name`, `phone`), key `distro_company_locations_idx1` using btree (`id`, `special_fields`) ) engine=innodb auto_increment=100502 character set 'utf8' collate 'utf8_general_ci' ; create table `distro_company_location_payer` ( `location_id` bigint(20) not null, `payer_id` integer(11) not null, primary key using btree (`location_id`, `payer_id`), unique key `distro_company_location_payer_idx1` using btree (`payer_id`, `location_id`) ) engine=innodb character set 'utf8' collate 'utf8_general_ci' ; create table `currencies` ( `id` smallint(6) unsigned not null auto_increment, `currency_name` varchar(50) collate latin1_swedish_ci not null, `symbol` char(3) collate latin1_swedish_ci not null, `iso` smallint(6) unsigned not null, primary key using btree (`id`, `symbol`), unique key `symbol` using btree (`symbol`), unique key `iso` using btree (`iso`), key `currencies_idx1` using btree (`id`, `symbol`, `currency_name`) ) engine=innodb auto_increment=175 character set 'latin1' collate 'latin1_swedish_ci' ; create table `distro_company_disabled_currency` ( `currency_id` smallint(5) unsigned default null, `company_id` integer(11) default null, `product_id` integer(11) default null, `rule_id` integer(11) not null auto_increment, primary key using btree (`rule_id`), key `distro_company_disabled_currency_idx1` using btree (`currency_id`, `company_id`, `product_id`, `rule_id`) ) engine=innodb auto_increment=2 character set 'utf8' collate 'utf8_general_ci' ; create table `distro_company_location_currency` ( `location_id` bigint(20) not null, `currency_id` smallint(6) unsigned not null, `exchange_rate` double(15,7) unsigned not null, `last_update` timestamp null default current_timestamp on update current_timestamp, primary key using btree (`location_id`, `currency_id`), key `distro_company_location_currency_idx1` using btree (`location_id`, `currency_id`, `exchange_rate`, `last_update`) ) engine=innodb character set 'utf8' collate 'utf8_general_ci' ; create table `distro_company_automatic_promo` ( `promo_id` integer(10) unsigned default null, `product_id` integer(11) default null, `country_id` smallint(5) unsigned default null, `company_id` integer(11) default null, key `distro_company_automatic_promo_idx1` using btree (`product_id`, `country_id`, `company_id`, `promo_id`) ) engine=innodb character set 'utf8' collate 'utf8_general_ci' ; create table `promos` ( `id` integer(10) unsigned not null auto_increment, `promo_code` varchar(50) collate latin1_swedish_ci not null, `amount` decimal(10,2) not null default 0.00, `start_promo` date not null, `end_promo` date not null, `percentage` tinyint(1) not null default 0, `usage_limit` tinyint(4) unsigned not null default 0, `sticky_promo_mins` integer(11) unsigned default null , `first_time_only` tinyint(1) default 0 , `max_usage_limit` smallint(6) default null , `min_send_amount` float default 0 , `max_send_amount` float default 0 , `is_bonus_promo` tinyint(1) default 0 , primary key using btree (`id`), key `promos_idx1` using btree (`id`, `start_promo`, `end_promo`, `promo_code`) ) engine=innodb auto_increment=43 character set 'latin1' collate 'latin1_swedish_ci' ; create table `distro_company_id_limits` ( `company_id` smallint(5) unsigned default null, `product_id` integer(11) default null, `payer_id` integer(11) default null, `id_limit` float unsigned, key `distro_company_id_limits_idx2` using btree (`payer_id`, `id_limit`), key `distro_company_id_limits_idx1` using btree (`company_id`, `product_id`, `id_limit`) ) engine=innodb character set 'utf8' collate 'utf8_general_ci' ; create table `distro_company_disabled_product` ( `product_id` integer(11) not null, primary key using btree (`product_id`) ) engine=innodb character set 'utf8' collate 'utf8_general_ci' ; create algorithm=undefined definer='system'@'%' sql security definer view `distro_company_location_payer_merged` select `a`.`location_id` `location_id`, ifnull(`b`.`payer_id`, `a`.`payer_id`) `payer_id` (`distro_company_location_payer` `a` left join `distro_company_payer_merge` `b` on (((`b`.`payer_to_replace_id` = `a`.`payer_id`) , (`b`.`complete_merge` = 1)))); create algorithm=undefined definer='system'@'%' sql security definer view `distro_company_product_payers` select `a`.`product_id` `product_id`, `b`.`payer_id` `payer_id`, `c`.`logo` `logo`, `c`.`name` `name`, count(`a`.`location_id`) `number_of_locations` ((`distro_company_product_location` `a` join `distro_company_location_payer` `b`) join `distro_company_payers_merged` `c`) ((`b`.`location_id` = `a`.`location_id`) , (`c`.`id` = `b`.`payer_id`) , (not (`c`.`id` in ( select `d`.`payer_id` (`distro_company_disabled_payer` `d` join `distro_company_product_country` `e`) ((`e`.`product_id` = `a`.`product_id`) , (isnull(`d`.`product_id`) or (`a`.`product_id` = `d`.`product_id`)) , (isnull(`d`.`country_id`) or (`d`.`country_id` = `e`.`country_id`))) )))) group `a`.`product_id`, `b`.`payer_id`; create algorithm=undefined definer='system'@'%' sql security definer view `distro_company_payers_merged` select if (`b`.`complete_merge`, `c`.`id`, `a`.`id`) `id`, if (isnull(`c`.`logo`), `a`.`logo`, `c`.`logo`) `logo`, if (isnull(`c`.`name`), `a`.`name`, `c`.`name`) `name` ((`distro_company_payers` `a` left join `distro_company_payer_merge` `b` on ((`b`.`payer_to_replace_id` = `a`.`id`))) left join `distro_company_payers` `c` on ((`c`.`id` = `b`.`payer_id`))) group `id`; create table `distro_company_payer_merge` ( `payer_to_replace_id` integer(11) default null, `payer_id` integer(11) default null, `complete_merge` tinyint(1) default 0 comment 'if true, merge id\r\nif false, merge name/logo' ) engine=innodb avg_row_length=8192 character set 'latin1' collate 'latin1_swedish_ci' ; more info - updated
to try , narrow problem. i've simplified query bit , got rid of bells , whistles. still taking more 5 minutes execute (i terminate after not sure how long takes) here updated query:
select `distro_company_product`.`id`, `delivery_type`, `fee_ach`, `fee_cc`, `fee_debit`, `max_limit`, `min_limit`, `distro_company_product`.`priority`, `fee_misc`, distro_company_product.`number_of_locations`, `distro_company_product`.`special_fields` `special_field_id`, distro_company_product_country.`country_id`, `availability`, distro_company_product.`require_location` `distro_company_product` inner join `distro_company_product_country` on `distro_company_product_country`.product_id = `distro_company_product`.id inner join `distro_company_product_exchangerates` on distro_company_product_exchangerates.product_id = `distro_company_product`.id inner join `currencies` on `currencies`.id = `distro_company_product_exchangerates`.currency_id inner join `distro_company_product_location` on `distro_company_product_location`.product_id = `distro_company_product`.id inner join `distro_company_location_currency` on `distro_company_product_location`.`location_id` = `distro_company_location_currency`.`location_id` , `distro_company_location_currency`.currency_id = distro_company_product_exchangerates.currency_id left join distro_company_product_payers on distro_company_product_payers.product_id = `distro_company_product`.id left join distro_company_payers_priority on distro_company_payers_priority.payer_id = `distro_company_product_payers`.payer_id , (distro_company_payers_priority.company_id null or distro_company_payers_priority.company_id = `distro_company_product`.company_id) , (distro_company_payers_priority.product_id null or distro_company_payers_priority.product_id = `distro_company_product`.id) , (distro_company_payers_priority.country_id null or distro_company_payers_priority.country_id = `distro_company_product_country`.country_id) left join `distro_company_location_payer_merged` on distro_company_product_location.location_id = distro_company_location_payer_merged.location_id left join distro_company_payers_link on distro_company_payers_link.payer_id = `distro_company_location_payer_merged`.payer_id , distro_company_payers_link.company_id = `distro_company_product`.company_id left join `distro_company_location_payer` on `distro_company_location_payer`.location_id = `distro_company_product_location`.location_id , `distro_company_location_payer`.payer_id = `distro_company_product_payers`.payer_id left join `distro_company_disabled_currency` dc on dc.currency_id = distro_company_product_exchangerates.currency_id , (dc.company_id null or dc.company_id = `distro_company_product`.company_id) , (dc.product_id null or dc.product_id = `distro_company_product`.id) left join distro_company_automatic_promo on (distro_company_automatic_promo.product_id = distro_company_product.id or distro_company_automatic_promo.country_id = distro_company_product_country.country_id or distro_company_automatic_promo.company_id = `distro_company_product`.company_id) left join promos on distro_company_automatic_promo.promo_id = promos.id left join distro_company_id_limits product_id_limits on (product_id_limits.company_id null or product_id_limits.company_id = `distro_company_product`.company_id) , (product_id_limits.product_id null or product_id_limits.product_id = `distro_company_product`.id) , (product_id_limits.company_id not null or product_id_limits.product_id not null) left join distro_company_id_limits `payer_id_limits` on payer_id_limits.payer_id = `distro_company_product_payers`.payer_id left join distro_company_disabled_product on distro_company_disabled_product.product_id = `distro_company_product`.id `distro_company_product`.company_id = 8 , dc.rule_id null , distro_company_disabled_product.product_id null the updated explain:
"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "filtered" "extra" "1" "primary" "distro_company_product" "ref" "primary,distro_company_product_idx1,company_id,id" "company_id" "2" "const" "10" "100" "1" "primary" "distro_company_product_exchangerates" "ref" "primary,distro_company_product_exchangerates_idx1,product_id" "distro_company_product_exchangerates_idx1" "4" "forex.distro_company_product.id" "1" "100" "using index" "1" "primary" "currencies" "ref" "primary,currencies_idx1" "primary" "2" "forex.distro_company_product_exchangerates.currency_id" "1" "100" "using index" "1" "primary" "distro_company_product_country" "ref" "primary,product_id" "primary" "4" "forex.distro_company_product.id" "1" "100" "using index" "1" "primary" "distro_company_product_location" "ref" "primary,distro_company_product_location_idx1,product_id,location_id" "primary" "4" "forex.distro_company_product.id" "71" "100" "using index" "1" "primary" "distro_company_location_currency" "eq_ref" "primary,distro_company_location_currency_idx1,location_id" "primary" "10" "forex.distro_company_product_location.location_id,forex.distro_company_product_exchangerates.currency_id" "1" "100" "using index" "1" "primary" "<derived2>" "ref" "<auto_key1>" "<auto_key1>" "4" "forex.distro_company_product.id" "122" "100" "1" "primary" "distro_company_payers_priority" "ref" "distro_company_payers_priority_idx1,payer_id,product_id,company_id" "payer_id" "4" "distro_company_product_payers.payer_id" "1" "100" "using where" "1" "primary" "a" "ref" "primary,location_id" "primary" "8" "forex.distro_company_product_location.location_id" "1" "100" "using index" "1" "primary" "b" "all" "payer_to_replace_id,payer_to_replace_id_2" "1" "100" "using where" "1" "primary" "distro_company_payers_link" "eq_ref" "primary,distro_company_payers_link_idx1,payer_id" "primary" "8" "func,const" "1" "100" "using where; using index" "1" "primary" "distro_company_location_payer" "eq_ref" "primary,distro_company_location_payer_idx1,location_id,payer_id" "primary" "12" "forex.distro_company_product_location.location_id,distro_company_product_payers.payer_id" "1" "100" "using index" "1" "primary" "dc" "ref_or_null" "distro_company_disabled_currency_idx1,product_id" "distro_company_disabled_currency_idx1" "8" "forex.distro_company_product_exchangerates.currency_id,const" "2" "100" "using where; not exists; using index" "1" "primary" "distro_company_automatic_promo" "index" "distro_company_automatic_promo_idx1,product_id" "distro_company_automatic_promo_idx1" "18" "3" "100" "using where; using index; using join buffer (block nested loop)" "1" "primary" "promos" "eq_ref" "primary,promos_idx1" "primary" "4" "forex.distro_company_automatic_promo.promo_id" "1" "100" "using index" "1" "primary" "product_id_limits" "ref_or_null" "distro_company_id_limits_idx1,product_id" "distro_company_id_limits_idx1" "3" "const" "2" "100" "using where; using index" "1" "primary" "payer_id_limits" "ref" "distro_company_id_limits_idx2,payer_id" "distro_company_id_limits_idx2" "5" "distro_company_product_payers.payer_id" "1" "100" "using index" "1" "primary" "distro_company_disabled_product" "eq_ref" "primary" "primary" "4" "forex.distro_company_product.id" "1" "100" "using where; not exists; using index" "2" "derived" "<derived4>" "all" "301" "100" "using where; using temporary; using filesort" "2" "derived" "b" "ref" "primary,distro_company_location_payer_idx1,location_id,payer_id" "distro_company_location_payer_idx1" "4" "c.id" "150" "100" "using where; using index" "2" "derived" "a" "ref" "distro_company_product_location_idx1,location_id" "distro_company_product_location_idx1" "8" "forex.b.location_id" "1" "100" "using where; using index" "4" "derived" "a" "index" "distro_company_payers_idx1" "1074" "301" "100" "using index; using temporary; using filesort" "4" "derived" "b" "all" "payer_to_replace_id,payer_to_replace_id_2" "1" "100" "using where; using join buffer (block nested loop)" "4" "derived" "c" "eq_ref" "primary,distro_company_payers_idx1" "primary" "4" "forex.b.payer_id" "1" "100" "3" "dependent subquery" "d" "all" "1" "100" "using where" "3" "dependent subquery" "e" "ref" "primary,product_id" "primary" "4" "forex.a.product_id" "1" "100" "using where; using index"
Comments
Post a Comment