以下查询需要花费时间(2.3s)才能执行。
SELECT *, COUNT(c2.id) AS c2__0
FROM countryMaster c, stateMaster s, cityMaster c2, categoryMaster c3, categoryMaster c4, product p
INNER JOIN user u ON ((u.id = p.user_id AND u.is_active = 1))
WHERE (p.category_id = c4.id
AND c2.id = p.x_area_id
AND c2.parent_id = s.id
AND s.parent_id = c.id AND c3.id IN ('1271')
AND c4.rgt = (c4.lft + 1)
AND c4.lft BETWEEN c3.lft AND c3.rgt AND p.status = 1)
GROUP BY c.id;
Explain:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: c3
type: const
possible_keys: PRIMARY,lft_rgt_inx
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: p
type: ALL
possible_keys: INX_cmp_sx,status
key: NULL
key_len: NULL
ref: NULL
rows: 29958
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: c4
type: eq_ref
possible_keys: PRIMARY,lft_rgt_inx
key: PRIMARY
key_len: 4
ref: yozoa1_live.p.category_id
rows: 1
Extra: Using where
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: u
type: eq_ref
possible_keys: PRIMARY,is_active
key: PRIMARY
key_len: 4
ref: yozoa1_live.p.user_id
rows: 1
Extra: Using where
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: c2
type: eq_ref
possible_keys: PRIMARY,parent_id
key: PRIMARY
key_len: 4
ref: yozoa1_live.p.x_area_id
rows: 1
Extra:
*************************** 6. row ***************************
id: 1
select_type: SIMPLE
table: s
type: eq_ref
possible_keys: PRIMARY,parent_id
key: PRIMARY
key_len: 4
ref: yozoa1_live.c2.parent_id
rows: 1
Extra:
*************************** 7. row ***************************
id: 1
select_type: SIMPLE
table: c
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: yozoa1_live.s.parent_id
rows: 1
Extra:
Show create table -
CREATE TABLE `categoryMaster` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`description` text NOT NULL,
`logo` varchar(255) NOT NULL,
`is_visible` tinyint(1) NOT NULL DEFAULT '1',
`is_featured` tinyint(1) NOT NULL DEFAULT '0',
`is_map` tinyint(1) NOT NULL DEFAULT '0',
`is_price_required` tinyint(1) NOT NULL DEFAULT '1',
`parent_id` int(11) NOT NULL DEFAULT '0',
`lft` int(11) NOT NULL DEFAULT '0',
`rgt` int(11) NOT NULL DEFAULT '0',
`level` tinyint(4) NOT NULL DEFAULT '0',
`sort_order` smallint(6) NOT NULL DEFAULT '0',
`product_count` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `lft_rgt_inx` (`lft`,`rgt`),
KEY `parent_id` (`parent_id`),
KEY `product_count` (`product_count`)
) ENGINE=MyISAM
CREATE TABLE `productMaster` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`description` longtext NOT NULL,
`sub_name` varchar(255) NOT NULL,
`sub_description` longtext NOT NULL,
`image` varchar(255) NOT NULL,
`category_id` int(11) NOT NULL,
`is_new` tinyint(1) NOT NULL DEFAULT '1',
`status` tinyint(1) NOT NULL DEFAULT '-1',
`user_id` int(11) NOT NULL,
`currency_main` varchar(4) NOT NULL DEFAULT 'MNT',
`price_original` double NOT NULL DEFAULT '0',
`price_global` double NOT NULL DEFAULT '0',
`attribute_value_ids` text,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`confirmed_at` datetime DEFAULT NULL,
`duration` int(4) NOT NULL DEFAULT '7',
`rental_duration` varchar(50) DEFAULT NULL,
`buy_online` tinyint(1) NOT NULL DEFAULT '0',
`internal` tinyint(1) NOT NULL DEFAULT '0',
`delivery_status` smallint(1) NOT NULL DEFAULT '1'
`delivery_type` smallint(1) NOT NULL DEFAULT '1'
`phone_cell` varchar(50) DEFAULT NULL,
`phone_home` varchar(50) DEFAULT NULL,
`surname` varchar(100) DEFAULT NULL,
`x_area_id` int(11) NOT NULL,
`x_area_location_id` int(11) DEFAULT NULL,
`sublocality_id` int(11) DEFAULT NULL,
`product_posting_price` double NOT NULL,
`is_paid` tinyint(1) NOT NULL,
`product_posting_currency` varchar(3) NOT NULL,
`map_lat` double NOT NULL,
`map_lng` double NOT NULL,
`product_company_contact_info_id` int(11) NOT NULL,
`backup_status` tinyint(1) NOT NULL DEFAULT '4',
`is_scraped` tinyint(1) DEFAULT NULL,
`sources_id` int(3) NOT NULL,
`product_source_url` varchar(255) NOT NULL,
`country_id` int(4) NOT NULL,
`state_id` int(5) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNQ_cmp_icsu` (`id`,`category_id`,`status`,`user_id`),
KEY `country_id` (`country_id`),
KEY `state_id` (`state_id`),
KEY `INX_cmp_sx` (`status`,`x_area_id`),
KEY `status` (`status`),
FULLTEXT KEY `name` (`name`),
FULLTEXT KEY `attribute_value_ids` (`attribute_value_ids`)
) ENGINE=MyISAM
CREATE TABLE `cityMaster` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`parent_id` int(6) NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`map_lat` double NOT NULL,
`map_lng` double NOT NULL,
`has_sublocality` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`)
) ENGINE=MyISAM
CREATE TABLE `stateMaster` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`parent_id` int(3) NOT NULL,
`name` varchar(255) NOT NULL,
`map_lat` double NOT NULL,
`map_lng` double NOT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`)
) ENGINE=MyISAM
CREATE TABLE `countryMaster` (
`id` int(3) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`country_code` varchar(2) CHARACTER SET latin1 NOT NULL,
`map_lat` double NOT NULL,
`map_lng` double NOT NULL,
`is_active` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=MyISAM
请建议我如何通过索引减少此查询的执行时间。还让我知道为什么查询说明在ref列中显示null?还让我知道优化查询的步骤吗?
好了,仔细研究一下,我认为您需要做的是在productMaster的x_area_id上添加一个索引,或者是一个多列索引。原因如下:
SELECT *, COUNT(c2.id) AS c2__0
FROM countryMaster c, stateMaster s, cityMaster c2, categoryMaster c3, categoryMaster c4, product p
INNER JOIN user u ON ((u.id = p.user_id AND u.is_active = 1))
WHERE (p.category_id = c4.id
AND c2.id = p.x_area_id
AND c2.parent_id = s.id
AND s.parent_id = c.id AND c3.id IN ('1271')
AND c4.rgt = (c4.lft + 1)
AND c4.lft BETWEEN c3.lft AND c3.rgt AND p.status = 1)
GROUP BY c.id;
请注意此处的连接条件:
AND c2.id = p.x_area_id
还要注意,在产品主表中,您具有以下索引:
PRIMARY KEY (`id`),
UNIQUE KEY `UNQ_cmp_icsu` (`id`,`category_id`,`status`,`user_id`),
KEY `country_id` (`country_id`),
KEY `state_id` (`state_id`),
KEY `INX_cmp_sx` (`status`,`x_area_id`),
KEY `status` (`status`),
FULLTEXT KEY `name` (`name`),
FULLTEXT KEY `attribute_value_ids` (`attribute_value_ids`)
显然,不是x_area_id。
SQL提及可能的键的事实与您的SQL语句的这一部分有关:
AND c4.lft BETWEEN c3.lft AND c3.rgt AND p.status = 1)
本质上,SQL注意到它可能能够使用status = 1的事实来利用INX_cmp_sx密钥。自从我在MyISAM中完成许多工作以来已经有一段时间了,但是我怀疑这是因为它被选择为使用p.user_id上的索引来针对用户表进行联接,并且查询优化器决定不执行索引合并。
如果是我,我会尝试两件事。首先,我尝试在x_area_id上简单地添加一个键,看看是否可以提高性能(一定要在那上面运行解释……显然)。如果没有,那么我会考虑在(user_id,status和x_area_id)上添加复合索引。确切地说,哪一列应该是索引中的第一位,从我的角度来看这是完全清楚的……我会猜测状态,但是您需要对其进行测试。
另外,我想指出的是,您在productMaster表上拥有的状态索引
KEY `INX_cmp_sx` (`status`,`x_area_id`),
KEY `status` (`status`),
由于INX_cmp_sx键,因此是多余的。因此,您可能希望尝试使用(user_id,status和x_area_id)或(user_id,x_area_id,status)之类的内容替换状态索引或类似的内容。
从技术上讲,您可以仅对它们全部进行索引,但是有时会适得其反(例如,http : //www.percona.com/blog/2009/09/19/multi-column-indexes-vs-index-merge/)
底线:摆脱状态键,并将其替换为(user_id,status和x_area_id)的某些变体。甚至尝试摆脱INX_cmp_sx并将其替换为(status,user_id,x_area_id)或(status,x_area_id,user_id)。说明的输出基本上是在告诉您,MySQL无法找到一种方法来提取与(status,user_id,x_area_id)组合相对应的数据的横截面,该方法将比简单地读取整个数据更有效。 productMaster表。试一下这些列上的索引,您应该找到一些效果更好的东西。而且,再次添加x_area_id可以解决所有问题。我肯定会先试一试。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句