希望对您有所帮助-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来源：stack overflow