MySQL ORDER BY optimization in many to many tables -

tables:

create table if not exists `posts` (    `post_n` int(10) not null auto_increment,   `id` int(10) default null,   `date` datetime not null default '0000-00-00 00:00:00',    primary key  (`post_n`,`visibility`),   key `id` (`id`),   key `date` (`date`) ) engine=myisam  default charset=utf8 collate=utf8_bin;  create table if not exists `subscriptions` (    `subscription_n` int(10) not null auto_increment,   `id` int(10) not null,   `subscribe_id` int(10) not null,    primary key  (`subscription_n`),   key `id` (`id`),   key `subscribe_id` (`subscribe_id`) ) engine=myisam  default charset=utf8 collate=utf8_bin; 

query:

select posts.* posts, subscriptions   posts.id=subscriptions.subscribe_id , subscriptions.id=1   order date desc limit 0, 15 

it`s slow because used indexes "id", "subscribe_id" not index "date" ordering slow.

is there options change query, indexes, architecture?

possible improvements:

first, you'll gain couple microseconds per query if name fields instead of using select posts.* causes schema lookup. change query to:

select posts.post_n, posts.id, posts.date  posts, subscriptions  posts.id=subscriptions.subscribe_id    , subscriptions.id=1  order date desc  limit 0, 15 

next, requires mysql 5.1 or higher, might want consider partitioning tables. might consider key partitioning both tables.

this should started. http://dev.mysql.com/doc/refman/5.1/en/partitioning-types.html

e.g.

set sql_mode = 'ansi'; -- allow default date create table if not exists `posts` (   `post_n` int(10) not null auto_increment,   `id` int(10) default null,   `date` datetime not null default '0000-00-00 00:00:00',   primary key  (`post_n`,`id`),   key `id` (`id`),   key `date` (`date`) ) engine=myisam  default charset=utf8 collate=utf8_bin partition key(id) partitions 32; -- create table if not exists `subscriptions` (   `subscription_n` int(10) not null auto_increment,   `id` int(10) not null,   `subscribe_id` int(10) not null,   primary key  (`subscription_n`,`subscribe_id`),   key `id` (`id`),   key `subscribe_id` (`subscribe_id`) ) engine=myisam  default charset=utf8 collate=utf8_bin partition key(subscribe_id) partitions 32; 

i had adjust primary key bit. so, beware, may not work you. please test , make sure. hope, though. make sure run sysbench against old , new structures/queries compare results before going production. :-)