sql - 重写 mysql select 以减少时间并将 tmp 写入磁盘

Question

我有一个需要几分钟的 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 有超过 800,000 行，而 poster_prodcat 有超过 1700 万行。使用此选择的其他类别查询几乎不会引起注意，而 poster_prodcat.apcatnum='623' 有大约 400,000 个结果并且正在写入磁盘

Answer 1

希望这对您有所帮助 - 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