MySQL ORDER BY optimization in many to many tables

Question

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.* FROM posts, subscriptions 

WHERE posts.id=subscriptions.subscribe_id AND subscriptions.id=1 

ORDER BY date DESC LIMIT 0, 15

It`s so slow because used indexes "id", "subscribe_id" but not index "date" thus ordering is very slow.

Is there any options to change the query, indexes, architecture?

Answer 1

If you're able to modify the table, you could add a multi-field index containing both ID and date. (or modify one of the existing keys to contain them both).

If you can't make changes to the database, and if you know that your result set is going to be small, you can force it to use a specific named key, with USE KEY(name). The ordering would then be done after the fact, just on the reslts returned.

Hope that helps.

Answer 2

Possible Improvements:

First, you'll gain a couple microseconds per query if you name your fields instead of using SELECT posts.* which causes a schema lookup. Change your query to:

SELECT posts.post_n, posts.id, posts.date 
FROM posts, subscriptions 
WHERE posts.id=subscriptions.subscribe_id 
  AND subscriptions.id=1 
ORDER BY date DESC 
LIMIT 0, 15

Next, this requires MySQL 5.1 or higher, but you might want to consider partitioning your tables. You might consider KEY partitioning for both tables.

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

E.g.

SET SQL_MODE = 'ANSI';
-- to 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 BY 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 BY KEY(subscribe_id) PARTITIONS 32;

I had to adjust your primary key a bit. So, beware, this may NOT work for you. Please test it and make sure. I hope, this does though. Make sure to run sysbench against the old and new structures/queries to compare results before going to production. :-)