Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
A similar question with answers: stackoverflow.com/questions/586381/… –  user1931858 Dec 30 '12 at 22:15
1  
Using temporary and/or Using filesort would be necessary because your WHERE and ORDER BY do not reference the same columns. But KEY is NULL? Consider posting the output of EXPLAIN SELECT. –  Michael - sqlbot Dec 31 '12 at 6:26

1 Answer 1

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.