重写mysql select以减少时间并将tmp写入磁盘 [英] Rewriting mysql select to reduce time and writing tmp to disk
问题描述
我有一个耗时数分钟的mysql查询,因为它是用来创建网页的,所以效果不是很好.
I have a mysql query that's taking several minutes which isn't very good as it's used to create a web page.
使用三个表:poster_data包含有关单个海报的信息. poster_categories列出所有类别(电影,艺术品等),而poster_prodcat列出后继编号及其可能位于的类别.一张海报将包含多行内容,例如电影,印第安纳·琼斯,哈里森·福特,冒险电影等.
Three tables are used: poster_data contains information on individual posters. poster_categories lists all the categories (movies, art, etc) while poster_prodcat lists the posterid number and the categories it can be in e.g. one poster would have multiple lines for say, movies, indiana jones, harrison ford, adventure films, etc.
这是慢速查询:
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万个结果,并且正在写到磁盘上
It was taking a few minutes. Poster_data has just over 800,000 rows, while poster_prodcat has just over 17 million. Other category queries with this select are barely noticeable, while poster_prodcat.apcatnum='623' has about 400,000 results and is writing out to disk
推荐答案
希望对您有所帮助- 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
select count(*) from category
count(*)
========
500,000
select count(*) from poster
count(*)
========
1,000,000
select count(*) from poster_category
count(*)
========
125,675,688
select count(*) from poster_category where cat_id = 623
count(*)
========
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
这篇关于重写mysql select以减少时间并将tmp写入磁盘的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!