做这个查询要花我3秒的时间,不知道能不能更快点呢,谢谢大家SELECT count(ID) AS amount FROM pb_products AS Product WHERE Product.status=1 AND Product.industry_id1=583 LIMIT 1我试了下,去掉Product.status=1还是很慢,不知道杂办了
表结构CREATE TABLE `pb_products` (
`id` int(10) NOT NULL auto_increment,
`member_id` int(10) NOT NULL,
`company_id` int(10) NOT NULL default '0',
`cache_companyname` varchar(100) NOT NULL default '',
`sort_id` tinyint(1) NOT NULL default '1',
`brand_id` smallint(6) NOT NULL default '0',
`category_id` smallint(6) NOT NULL default '0',
`industry_id1` smallint(6) NOT NULL default '0',
`industry_id2` smallint(6) NOT NULL default '0',
`industry_id3` smallint(6) NOT NULL default '0',
`area_id1` smallint(6) NOT NULL default '0',
`area_id2` smallint(6) NOT NULL default '0',
`area_id3` smallint(6) NOT NULL default '0',
`name` varchar(255) NOT NULL default '',
`price` float(9,2) NOT NULL default '0.00',
`sn` varchar(20) NOT NULL default '',
`spec` varchar(20) NOT NULL default '',
`produce_area` varchar(50) NOT NULL default '',
`packing_content` varchar(100) NOT NULL default '',
`picture` varchar(500) NOT NULL,
`content` text,
`producttype_id` smallint(6) NOT NULL default '0',
`status` tinyint(1) NOT NULL default '0',
`state` tinyint(1) NOT NULL default '1',
`ifnew` tinyint(1) NOT NULL default '0',
`ifcommend` tinyint(1) NOT NULL default '0',
`priority` tinyint(1) NOT NULL default '0',
`tag_ids` varchar(255) default '',
`clicked` smallint(6) NOT NULL default '1',
`formattribute_ids` text,
`created` int(10) NOT NULL default '0',
`modified` int(10) NOT NULL default '0',
`PickUrl` varchar(500) NOT NULL,
PRIMARY KEY (`id`),
KEY `company_id` (`company_id`),
KEY `member_id` (`member_id`),
KEY `industry_id1` (`industry_id1`),
KEY `industry_id2` (`industry_id2`),
KEY `industry_id3` (`industry_id3`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=835924 ;
表结构CREATE TABLE `pb_products` (
`id` int(10) NOT NULL auto_increment,
`member_id` int(10) NOT NULL,
`company_id` int(10) NOT NULL default '0',
`cache_companyname` varchar(100) NOT NULL default '',
`sort_id` tinyint(1) NOT NULL default '1',
`brand_id` smallint(6) NOT NULL default '0',
`category_id` smallint(6) NOT NULL default '0',
`industry_id1` smallint(6) NOT NULL default '0',
`industry_id2` smallint(6) NOT NULL default '0',
`industry_id3` smallint(6) NOT NULL default '0',
`area_id1` smallint(6) NOT NULL default '0',
`area_id2` smallint(6) NOT NULL default '0',
`area_id3` smallint(6) NOT NULL default '0',
`name` varchar(255) NOT NULL default '',
`price` float(9,2) NOT NULL default '0.00',
`sn` varchar(20) NOT NULL default '',
`spec` varchar(20) NOT NULL default '',
`produce_area` varchar(50) NOT NULL default '',
`packing_content` varchar(100) NOT NULL default '',
`picture` varchar(500) NOT NULL,
`content` text,
`producttype_id` smallint(6) NOT NULL default '0',
`status` tinyint(1) NOT NULL default '0',
`state` tinyint(1) NOT NULL default '1',
`ifnew` tinyint(1) NOT NULL default '0',
`ifcommend` tinyint(1) NOT NULL default '0',
`priority` tinyint(1) NOT NULL default '0',
`tag_ids` varchar(255) default '',
`clicked` smallint(6) NOT NULL default '1',
`formattribute_ids` text,
`created` int(10) NOT NULL default '0',
`modified` int(10) NOT NULL default '0',
`PickUrl` varchar(500) NOT NULL,
PRIMARY KEY (`id`),
KEY `company_id` (`company_id`),
KEY `member_id` (`member_id`),
KEY `industry_id1` (`industry_id1`),
KEY `industry_id2` (`industry_id2`),
KEY `industry_id3` (`industry_id3`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=835924 ;
SELECT count( `id` ) AS amount
FROM pb_products AS Product
WHERE Product.status = '0'
AND Product.industry_id1 = '0'试下这个,刚才也用的80多W数据测试。我这里很快的。
用你那个就很慢
看来还是加不加单引号的问题。到底为什么就不懂了。只有去数据库区问下。我的80W数据全是status = '0' and industry_id1 = '0'的。
按理说,你的应该会更快
两个操作要都做,才可以把速度提高
但是带来了另外一个问题: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 ;
另外加引号没什么效果,我的status基本上都是1
FROM pb_products AS Product
WHERE Product.status = '1'
AND Product.industry_id1 = '583'
你用的是这个语句吗?
AND Product.industry_id1 = '0'我这里可以说是非常快。我就用的你发的表结构,没加任何其他索引了。而你那里某种条件下的数据肯定没这么多,应该会更快的。不解。建议到数据库区去问问吧。
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=610 ORDER BY Product.ifcommend desc,Product.id desc LIMIT 0,9;这个很慢,我误以为是上面那个还很慢,不好意思
SELECT count( `id` ) AS amount
FROM pb_products AS Product
WHERE Product.status = '1'
AND Product.industry_id1 = '583'
这个语句
status都是1 ,或者status为1的分布情况较广,那么这个status索引将不会被采用,Product.industry_id1也一样,如果583记录占了8,90%的记录量,那么mysql会认为使用索引还不如不使用索引好。
要综合你的记录分布情况,索引安排,好好分析explain吧,很多时候,索引并不能解决问题。
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