我有一个需要几分钟的mysql查询,这不是很好,因为它用于创建网页。
使用三个表:poster_data包含有关各个海报的信息。poster_categories列出了所有类别(电影,艺术品等),而poster_prodcat列出了posterid编号及其类别,例如,一个海报将有多行,例如电影,印第安纳·琼斯,哈里森·福特,冒险电影等。
这是慢查询:
select * from poster_prodcat, poster_data, poster_categories where poster_data.apnumber = poster_prodcat.apnumber and poster_categories.apcatnum = poster_prodcat.apcatnum and poster_prodcat.apcatnum='623'
ORDER BY aptitle ASC LIMIT 0, 32 据解释:
说明
花了几分钟。Poster_data有80万行,而poster_prodcat有1700万行。使用此选择的其他类别查询几乎不会引起注意,而poster_prodcat.apcatnum ='623'大约有40万个结果,并且正在写到磁盘上
希望对您有所帮助-http: //pastie.org/1105206
drop table if exists poster; create table poster ( poster_id int unsigned not null auto_increment primary key, name varchar(255) not null unique ) engine = innodb;
drop table if exists category; create table category ( cat_id mediumint unsigned not null auto_increment primary key, name varchar(255) not null unique ) engine = innodb;
drop table if exists poster_category; create table poster_category ( cat_id mediumint unsigned not null, poster_id int unsigned not null, primary key (cat_id, poster_id) -- note the clustered composite index !! ) engine = innodb;
-- FYI http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html
500,000
1,000,000
125,675,688
342,820
explain select p., c. from poster_category pc inner join category c on pc.cat_id = c.cat_id inner join poster p on pc.poster_id = p.poster_id where pc.cat_id = 623 order by p.name limit 32;
id select_type table type possible_keys key key_len ref rows == =========== ===== ==== ============= === ======= === ==== 1 SIMPLE c const PRIMARY PRIMARY 3 const 1
1 SIMPLE p index PRIMARY name 257 null 32
1 SIMPLE pc eq_ref PRIMARY PRIMARY 7 const,foo_db.p.poster_id 1
select p., c. from poster_category pc inner join category c on pc.cat_id = c.cat_id inner join poster p on pc.poster_id = p.poster_id where pc.cat_id = 623 order by p.name limit 32;
Statement:21/08/2010 0:00:00.021: Query OK来源:stack overflow
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。