I've been trying to optimize this query and cannot get it to be under 15mins! Playing around left and inner joins I could not see any real performance benefit. I've tried to switch some of the joins to subqueries, nothing there either. Reviewed some of the indexes and reorder them according to the order of appearance in the queries. Nada. I can work with having this query run for 20s but currently it's running for minutes. The explain shows maximum results of 546 so I'm not sure why is it taking that long. The largest table is distro_company_locations with 92,000 records.
Any help would be appreciated. Running MySQL 5.6.
See below for 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 are the 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 - does not require location\r\n1 - requires location\r\n2 - requires location to show price\r\n3 - requires payer\r\n4 - requires payer to 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`
AS
select
`a`.`location_id` AS `location_id`,
ifnull(`b`.`payer_id`, `a`.`payer_id`) AS `payer_id`
from
(`distro_company_location_payer` `a`
left join `distro_company_payer_merge` `b` on (((`b`.`payer_to_replace_id` = `a`.`payer_id`) and (`b`.`complete_merge` = 1))));
CREATE ALGORITHM=UNDEFINED DEFINER='system'@'%' SQL SECURITY DEFINER VIEW `distro_company_product_payers`
AS
select
`a`.`product_id` AS `product_id`,
`b`.`payer_id` AS `payer_id`,
`c`.`logo` AS `logo`,
`c`.`name` AS `name`,
count(`a`.`location_id`) AS `number_of_locations`
from
((`distro_company_product_location` `a`
join `distro_company_location_payer` `b`)
join `distro_company_payers_merged` `c`)
where
((`b`.`location_id` = `a`.`location_id`) and
(`c`.`id` = `b`.`payer_id`) and
(not (`c`.`id` in (
select
`d`.`payer_id`
from
(`distro_company_disabled_payer` `d`
join `distro_company_product_country` `e`)
where
((`e`.`product_id` = `a`.`product_id`) and
(isnull(`d`.`product_id`) or
(`a`.`product_id` = `d`.`product_id`)) and
(isnull(`d`.`country_id`) or
(`d`.`country_id` = `e`.`country_id`)))
))))
group by
`a`.`product_id`,
`b`.`payer_id`;
CREATE ALGORITHM=UNDEFINED DEFINER='system'@'%' SQL SECURITY DEFINER VIEW `distro_company_payers_merged`
AS
select
if (`b`.`complete_merge`, `c`.`id`, `a`.`id`) AS `id`,
if (isnull(`c`.`logo`), `a`.`logo`, `c`.`logo`) AS `logo`,
if (isnull(`c`.`name`), `a`.`name`, `c`.`name`) AS `name`
from
((`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 by
`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, will also merge id\r\nIf false, only merge name/logo'
) ENGINE=InnoDB
AVG_ROW_LENGTH=8192 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'
;
MORE INFO - UPDATED
To try and narrow the problem. I've simplified the query a bit and got rid of some bells and whistles. Still taking more than 5 minutes to execute (I terminate after so not even sure how long it takes) Here is the 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` AS `special_field_id`,
distro_company_product_country.`country_id`,
`availability`,
distro_company_product.`require_location`
FROM `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` AND
`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 AND
(distro_company_payers_priority.company_id IS NULL OR distro_company_payers_priority.company_id = `distro_company_product`.company_id) AND
(distro_company_payers_priority.product_id IS NULL OR distro_company_payers_priority.product_id = `distro_company_product`.id) AND
(distro_company_payers_priority.country_id IS 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 AND 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 AND
`distro_company_location_payer`.payer_id = `distro_company_product_payers`.payer_id
LEFT JOIN `distro_company_disabled_currency` as dc ON dc.currency_id = distro_company_product_exchangerates.currency_id AND
(dc.company_id IS NULL OR dc.company_id = `distro_company_product`.company_id) AND
(dc.product_id IS 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 as product_id_limits ON
(product_id_limits.company_id IS NULL OR product_id_limits.company_id = `distro_company_product`.company_id) AND
(product_id_limits.product_id IS NULL OR product_id_limits.product_id = `distro_company_product`.id) AND
(product_id_limits.company_id IS NOT NULL OR product_id_limits.product_id IS NOT NULL)
LEFT JOIN distro_company_id_limits as `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
WHERE `distro_company_product`.company_id = 8 AND dc.rule_id IS NULL AND distro_company_disabled_product.product_id IS 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"
