How to reduce rows lookup when using LIMIT MySQL

user_1822704 0
Viewed 47

I have the following table with Index on id and Foreign Key on activityID:

comment (id, activityID, text)

and the following query:

SELECT <cols> FROM `comment` WHERE `comment`.`activityID` = 1257 ORDER BY `id` DESC LIMIT 20;

I basically want to get only the first 20 comments for this activity that has 1165, however, this is the result of a describe:

id  select_type table   type    possible_keys   key         key_len ref     rows    Extra
1   SIMPLE      comment ref     activityID      activityID  4       const   1165    NULL

Essentially, it is looking through all comments for this activity before deciding to limit it.

We tested this query under high load when an activity has 200,000 comments and the query takes 5+ seconds, whereas on the same load, an activity with 30 comments takes a couple of ms.

PS: If I remove the WHERE clause, an EXPLAIN says it will only lookup a single row (don't know if that's the case really):

id  select_type table   type    possible_keys   key         key_len ref     rows    Extra
1   SIMPLE      comment index   NULL            PRIMARY     4       NULL    1       NULL

Is it possible to optimize this kind of query in any way?

Thank you.

mysql
3 Answers

The ordering is causing the slowness.

The query uses the activityID index to find all the rows with that ID. Then it has to read all 200,000 comments and sort them by id to find the last 20.

Add a composite index so it can use an index for the ordering:

ALTER TABLE comment ADD INDEX (activityID, id);

Note that you will no longer need the index on activityID by itself, since it's a prefix of this new index.

user_14918950

Use offset

SELECT <cols> FROM `comment` WHERE `comment`.`activityID` = 1257 ORDER BY `id` DESC LIMIT 0,20;

In limit clause add 0 as an offset to get only the first 20 comments

user_101278330

Just add two separate indexes on activityID and id. That should help you in ORDER BY too. There is no hard and fast rules in optimizations, but you need to try various methods.

Do it this way:

ALTER TABLE comment ADD INDEX (id);
ALTER TABLE comment ADD INDEX (activityID);

I think this will help.

user_18431750
Related