How to efficiently hit an index in a query that uses IN (mysql)

I have a table like this where people can subscribe to certain feeds and the query fetches from the subscription.

Here is a sample table:

+--------+-----------------+-------------------+---------------------+-----------+
| by     | subscription_id | subscription_type | feed_created_time   | content   |
+--------+-----------------+-------------------+---------------------+-----------+
| Peter  |              12 |                 1 | 2012-12-30 10:00:00 | asdf      |
| George |              34 |                 2 | 2012-12-21 00:19:00 | qwerty    |
+--------+-----------------+-------------------+---------------------+-----------+

and its full detail

CREATE TABLE `table` (
  `by` varchar(20) NOT NULL,
  `subscription_id` int(11) NOT NULL,
  `subscription_type` int(11) NOT NULL,
  `feed_created_time` datetime NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`subscription_id`,`subscription_type`,`feed_created_time`),
  KEY `subscription` (`subscription_id`,`subscription_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I've created the primary key like so to create a cluster which would allow most likely types of pulls to be most efficient. So the "next applicable" is immediately following.

And here is how I'm making the query right now:

SELECT * FROM table 
WHERE subscription_id IN (12,34) AND subscription_type=1 
ORDER BY feed_created_time DESC;

I would also like to make a more complicated queries like...

SELECT * FROM table 
WHERE (subscription_id IN (12,34) AND subscription_type=1 )
   OR (subscription_id IN (34) AND subscription_type=2 )
   ...
ORDER BY feed_created_time DESC;

It works fine, but I noticed that when I do an explain, it's unable to use any indexes. Or, it at least reports that it hasn't chosen to use any indexes despite the primary and subscription key being available. It just creates a temporary table and sorts them.

It has no problem selecting keys when making queries like `subscription_id = 12 AND subscripti

Reverse the 'subscription' index. As it is, MySQL will probably use the PRIMARY KEY for lookups by a single subscription_id.

Once MySQL hits a RANGE query, which an IN clause is, it doesn't use the rest of the index.

CREATE TABLE `table` (
  `by` varchar(20) NOT NULL,
  `subscription_id` int(11) NOT NULL,
  `subscription_type` int(11) NOT NULL,
  `feed_created_time` datetime NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`subscription_id`,`subscription_type`,`feed_created_time`),
  KEY `subscription` (`subscription_type`,`subscription_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

StackExchange Database Administrators Q#31200, answer score: 2