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 subscription_type=1
but the IN
in the query seems to make all keys unusable. How can I make the queries use the indexes?
Best Answer
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.