刚测试了一下,改成联合索引+把ID改成* 两个操作要都做,才可以把速度提高 但是带来了另外一个问题:SELECT m.username,m.space_name AS userid,m.membergroup_id,m.credits,Product.cache_companyname AS companyname,Product.* FROM pb_products AS Product LEFT JOIN pb_members m ON m.id=Product.member_id WHERE Product.status=1 AND Product.industry_id1=700 ORDER BY Product.ifcommend desc,Product.id desc LIMIT 0,9;这个就变得到特别慢了,把ORDER BY Product.ifcommend desc去掉也仍然慢 附:pb_members CREATE TABLE `pb_members` ( `id` int(10) NOT NULL auto_increment, `space_name` varchar(255) NOT NULL default '', `templet_id` smallint(3) NOT NULL default '0', `username` varchar(25) NOT NULL default '', `userpass` varchar(50) NOT NULL default '', `email` varchar(100) NOT NULL default '', `points` smallint(6) NOT NULL default '0', `credits` smallint(6) NOT NULL default '0', `balance_amount` float(7,2) NOT NULL default '0.00', `trusttype_ids` varchar(25) NOT NULL default '', `status` enum('3','2','1','0') NOT NULL default '0', `photo` varchar(100) NOT NULL default '', `membertype_id` smallint(3) NOT NULL default '0', `membergroup_id` smallint(3) NOT NULL default '0', `last_login` varchar(11) NOT NULL default '0', `last_ip` varchar(25) NOT NULL default '0', `service_start_date` varchar(11) NOT NULL default '0', `service_end_date` varchar(11) NOT NULL default '0', `office_redirect` smallint(6) NOT NULL default '0', `created` varchar(10) NOT NULL default '0', `modified` varchar(10) NOT NULL default '0', PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`), KEY `space_name` (`space_name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=101259 ;
贴出你的explain SELECT count(ID) AS amount FROM pb_products AS Product WHERE Product.status=1 AND Product.industry_id1=583 LIMIT 1;show index from pb_products
我教你个方法你试试看可以么? SELECT SQL_CALC_FOUND_ROWS ID FROM pb_products AS Product WHERE Product.status=1 AND Product.industry_id1=583 LIMIT 1; SELECT FOUND_ROWS();
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Product ref industry_id1_2 industry_id1_2 2 const 31593 Using where Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment pb_products 0 PRIMARY 1 id A 834513 NULL NULL BTREE pb_products 1 company_id 1 company_id A 104314 NULL NULL BTREE pb_products 1 member_id 1 member_id A 104314 NULL NULL BTREE pb_products 1 industry_id2 1 industry_id2 A 1165 NULL NULL BTREE pb_products 1 industry_id3 1 industry_id3 A 2425 NULL NULL BTREE pb_products 1 industry_id1_2 1 industry_id1 A 44 NULL NULL BTREE
create index xxxx on pb_products(status,industry_id1);创建这个复合索引。
SELECT count(ID) AS amount FROM pb_products AS Product WHERE Product.status=1 AND Product.industry_id1=583 LIMIT 1 你这个语句为什么后面要跟一个LIMIT 1?按理说只要只要增加Product.status和Product.industry_id1应该就可以提高效率了,count(ID)和count(*)应该是一样的吧,因为ID本身就是唯一主键
我之前是创建过复合索引的,但会带来另外一个副作用:SELECT m.username,m.space_name AS userid,m.membergroup_id,m.credits,Product.cache_companyname AS companyname,Product.* FROM pb_products AS Product LEFT JOIN pb_members m ON m.id=Product.member_id WHERE Product.status=1 AND Product.industry_id1=700 ORDER BY Product.ifcommend desc,Product.id desc LIMIT 0,9;这个就变得到特别慢了,把ORDER BY Product.ifcommend desc去掉也仍然慢 附:pb_members CREATE TABLE `pb_members` ( `id` int(10) NOT NULL auto_increment, `space_name` varchar(255) NOT NULL default '', `templet_id` smallint(3) NOT NULL default '0', `username` varchar(25) NOT NULL default '', `userpass` varchar(50) NOT NULL default '', `email` varchar(100) NOT NULL default '', `points` smallint(6) NOT NULL default '0', `credits` smallint(6) NOT NULL default '0', `balance_amount` float(7,2) NOT NULL default '0.00', `trusttype_ids` varchar(25) NOT NULL default '', `status` enum('3','2','1','0') NOT NULL default '0', `photo` varchar(100) NOT NULL default '', `membertype_id` smallint(3) NOT NULL default '0', `membergroup_id` smallint(3) NOT NULL default '0', `last_login` varchar(11) NOT NULL default '0', `last_ip` varchar(25) NOT NULL default '0', `service_start_date` varchar(11) NOT NULL default '0', `service_end_date` varchar(11) NOT NULL default '0', `office_redirect` smallint(6) NOT NULL default '0', `created` varchar(10) NOT NULL default '0', `modified` varchar(10) NOT NULL default '0', PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`), KEY `space_name` (`space_name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=101259 ;
贴你的explain 结果出来看看。
创建(status,industry_id1,ifcommend ,id)索引。
创建这个索引后,导致以前很快的下面这条语句变得奇慢: SELECT p.id as productid,p.id,p.name as productname,p.clicked AS hits,p.name,p.price,picture,created,cache_companyname as companyname FROM pb_products p WHERE p.status=1 AND p.state=1 AND p.ifcommend>0 ORDER BY id desc LIMIT 10; 于是我把上面那个索引中增加了state,在id之前,结果全都变慢了 ,现在这个问题变成了一个恶循环了,解决了上面的问题,下面会带来另外一个问题.真是苦恼呀
EXPLAIN SELECT p.id AS productid, p.id, p.name AS productname, p.clicked AS hits, p.name, p.price, picture, created, cache_companyname AS companyname FROM pb_products p WHERE p.status =1 AND p.ifcommend >0 ORDER BY id DESC LIMIT 10 ;1 SIMPLE p ref status status 1 const 834510 Using where; Using filesort下面这个增加state字段后explain的结果一样explain SELECT p.id as productid,p.id,p.name as productname,p.clicked AS hits,p.name,p.price,picture,created,cache_companyname as companyname FROM pb_products p WHERE p.status=1 AND p.state=1 AND p.ifcommend>0 ORDER BY id desc LIMIT 10;1 SIMPLE p ref status status 1 const 834510 Using where; Using filesort
两个操作要都做,才可以把速度提高
但是带来了另外一个问题:SELECT m.username,m.space_name AS userid,m.membergroup_id,m.credits,Product.cache_companyname AS companyname,Product.* FROM pb_products AS Product LEFT JOIN pb_members m ON m.id=Product.member_id WHERE Product.status=1 AND Product.industry_id1=700 ORDER BY Product.ifcommend desc,Product.id desc LIMIT 0,9;这个就变得到特别慢了,把ORDER BY Product.ifcommend desc去掉也仍然慢
附:pb_members
CREATE TABLE `pb_members` (
`id` int(10) NOT NULL auto_increment,
`space_name` varchar(255) NOT NULL default '',
`templet_id` smallint(3) NOT NULL default '0',
`username` varchar(25) NOT NULL default '',
`userpass` varchar(50) NOT NULL default '',
`email` varchar(100) NOT NULL default '',
`points` smallint(6) NOT NULL default '0',
`credits` smallint(6) NOT NULL default '0',
`balance_amount` float(7,2) NOT NULL default '0.00',
`trusttype_ids` varchar(25) NOT NULL default '',
`status` enum('3','2','1','0') NOT NULL default '0',
`photo` varchar(100) NOT NULL default '',
`membertype_id` smallint(3) NOT NULL default '0',
`membergroup_id` smallint(3) NOT NULL default '0',
`last_login` varchar(11) NOT NULL default '0',
`last_ip` varchar(25) NOT NULL default '0',
`service_start_date` varchar(11) NOT NULL default '0',
`service_end_date` varchar(11) NOT NULL default '0',
`office_redirect` smallint(6) NOT NULL default '0',
`created` varchar(10) NOT NULL default '0',
`modified` varchar(10) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
KEY `space_name` (`space_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=101259 ;
SELECT SQL_CALC_FOUND_ROWS ID
FROM pb_products AS Product
WHERE Product.status=1 AND Product.industry_id1=583 LIMIT 1;
SELECT FOUND_ROWS();
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Product ref industry_id1_2 industry_id1_2 2 const 31593 Using where
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
pb_products 0 PRIMARY 1 id A 834513 NULL NULL BTREE
pb_products 1 company_id 1 company_id A 104314 NULL NULL BTREE
pb_products 1 member_id 1 member_id A 104314 NULL NULL BTREE
pb_products 1 industry_id2 1 industry_id2 A 1165 NULL NULL BTREE
pb_products 1 industry_id3 1 industry_id3 A 2425 NULL NULL BTREE
pb_products 1 industry_id1_2 1 industry_id1 A 44 NULL NULL BTREE
你这个语句为什么后面要跟一个LIMIT 1?按理说只要只要增加Product.status和Product.industry_id1应该就可以提高效率了,count(ID)和count(*)应该是一样的吧,因为ID本身就是唯一主键
附:pb_members
CREATE TABLE `pb_members` (
`id` int(10) NOT NULL auto_increment,
`space_name` varchar(255) NOT NULL default '',
`templet_id` smallint(3) NOT NULL default '0',
`username` varchar(25) NOT NULL default '',
`userpass` varchar(50) NOT NULL default '',
`email` varchar(100) NOT NULL default '',
`points` smallint(6) NOT NULL default '0',
`credits` smallint(6) NOT NULL default '0',
`balance_amount` float(7,2) NOT NULL default '0.00',
`trusttype_ids` varchar(25) NOT NULL default '',
`status` enum('3','2','1','0') NOT NULL default '0',
`photo` varchar(100) NOT NULL default '',
`membertype_id` smallint(3) NOT NULL default '0',
`membergroup_id` smallint(3) NOT NULL default '0',
`last_login` varchar(11) NOT NULL default '0',
`last_ip` varchar(25) NOT NULL default '0',
`service_start_date` varchar(11) NOT NULL default '0',
`service_end_date` varchar(11) NOT NULL default '0',
`office_redirect` smallint(6) NOT NULL default '0',
`created` varchar(10) NOT NULL default '0',
`modified` varchar(10) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
KEY `space_name` (`space_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=101259 ;
创建这个索引后,导致以前很快的下面这条语句变得奇慢:
SELECT p.id as productid,p.id,p.name as productname,p.clicked AS hits,p.name,p.price,picture,created,cache_companyname as companyname FROM pb_products p WHERE p.status=1 AND p.state=1 AND p.ifcommend>0 ORDER BY id desc LIMIT 10;
于是我把上面那个索引中增加了state,在id之前,结果全都变慢了
,现在这个问题变成了一个恶循环了,解决了上面的问题,下面会带来另外一个问题.真是苦恼呀
FROM pb_products p
WHERE p.status =1
AND p.ifcommend >0
ORDER BY id DESC
LIMIT 10 ;1 SIMPLE p ref status status 1 const 834510 Using where; Using filesort下面这个增加state字段后explain的结果一样explain SELECT p.id as productid,p.id,p.name as productname,p.clicked AS hits,p.name,p.price,picture,created,cache_companyname as companyname FROM pb_products p WHERE p.status=1 AND p.state=1 AND p.ifcommend>0 ORDER BY id desc LIMIT 10;1 SIMPLE p ref status status 1 const 834510 Using where; Using filesort
(1)为什么创建了新的联合索引之后,以前跑得很快的查询却变得很慢了?按理说应该只会影响到insert,update之类才对呀
(2)(a,b,c,d,e)这样的索引对a,b,c,e或者a,b,d,e的查询无效吗?
(a,b,c,d,e)这样的索引 能用的组合是如下:
a b c d e
a b c d
a b c
a b
a
a,b,c,d,e)这样的索引对a,b,c,e或者a,b,d,e的查询无效吗?
==》
回答 无效~
自己可以试试
a b c d
a b c
a b
a都试过么?应该都有效果 有时候不用索引也跟数据分布有关系