What Should Be Indexed To Improve Performance?

Given this query, which column or columns should be indexed to optimize query performance? SELECT * FROM `activities` WHERE (user_id = 90000 AND activity_type_id IN(300,400,808,

Solution 1:

In general, the selection filters can use indexes on user_id or activity_type_id or both (in either order).

The ordering operation might be able to use a filter on created_at.

It is likely that for this query, a composite index on (user_id, activity_type_id) would give the best result, assuming that MySQL can actually make use of it. Failing that, it is likely to be better to index user_id than activity_type_id because it is likely to provide better selectivity. One reason for thinking that is that there would be 4 subsections of the index to scan if it uses an index on activity_type_id, compared with just one subsection to scan if it uses an index on user_id alone.

Trying to rely on an index for the sort order is likely to mean a full table scan, so it is less likely to be beneficial. I would not create an index on created_at to support this query; there might be other queries where it would be beneficial.

Solution 2:

Certainly all columns in WHERE clauses should be indexed.

But the IN clause is likely to require a table scan.

I think reading these answers might help:

MySQL not using indexes with WHERE IN clause?

Solution 3:

You are doing a lookup on user_id and activity_type_id, so create indexes on both columns.

Solution 4:

I would index just user_id..

Solution 5:

Assuming you're not hiding a JOIN on the actual production code, indexing the "activity_type_id" should be the best one.