4

I have a poorly performing query:

SELECT  user_id, count(item_id) as count
FROM table items 
WHERE category = 'magazine'
AND created_at > 1384754400
GROUP BY user_id
ORDER BY count(item_id) desc
LIMIT 100

Whats the optimal indexing strategy in order to optimize this query?

Table Details

500million records with the following structure / cardinalities:

  • PRIMARY KEY (item_id) - cardinality: 500 M
  • user_id - cardinality: ~ 25 M
  • category - cardinality: ~ 2.5 M
  • created_at - cardinality: ~ 150 M

Indexing:

  • I have individual indexes on each the user_id, category and created_at fields

I also have the following covering indexes:

  • (category, user_id) - this is the one the query optimizer defaults to when running explain
  • (category, created_at)
  • (category, created_at, user_id) - this is one I attempted to create in order to optimize this query, however, it doesn't seem to be working very well.
5
  • 1
    What does "mm" mean? Do you mean "m" as millions?
    – fejesjoco
    Jan 17, 2014 at 8:45
  • mm means millions.
    – alex
    Jan 17, 2014 at 14:08
  • 2
    no, "mm" means millimeters, and "M" means millions :)
    – fejesjoco
    Jan 17, 2014 at 14:16
  • @fejesjoco "M" could also mean thousand :)
    – alex
    Jan 17, 2014 at 23:14
  • M = 1024 * 1024 :-) Jan 26, 2015 at 11:35

2 Answers 2

1

If you ONLY want to optimise for this query. This is the best index:

ALTER TABLE items ADD INDEX (category, created_at, user_id)

This optimises the value of the filters, which reduces the total amount of data you touch. By adding user_id, item_id at the end of the query, you make the index covering and it saves you a lookup into the primary index.

We can assume that item_id is NOT NULL (as it is the PRIMARY index).

However, because the MySQL optimiser is pretty stupid, you may need to rewrite like this:

SELECT  user_id, SUM(count) AS count
FROM
(
  SELECT category, created_at, user_id, COUNT(*) as count
  FROM items
  WHERE category = 'magazine'
  AND created_at > 1384754400
  GROUP BY category, created_at, user_id
) AS d
GROUP BY user_id
ORDER BY count DESC
LIMIT 100
0
0

I would delete all indexes and start fresh. I think a category index is sufficient, or a (category, created_at) at most. Given one category, there should be around 200 matches, so it depends whether the created_at filter discards a lot of rows or not. Can you show query plans and numbers to verify how the query is performing? Also I don't know if it matters or not, but count(1) would be sufficient as well, instead of naming a column.

1
  • Thanks for the reply. You're right that it depends on whether the created_at filter discards a lot of rows or not...but 200 matches might not be right. For some categories, there are a upwards of 1 million matches. The other issue with using (category, created_at) is that it adds a "hidden join". It would need to get each primary key pointer, and then grab the entire row of data in order to get the user_id field value, instead of getting all of the needed field values straight from the index (a non-trivial, and large operation, since this table has many other columns).
    – alex
    Jan 17, 2014 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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