What Kind Of Index Would Be Efficient When There Is In Clause?

Here is my query: DELETE FROM events WHERE type = 4 AND author_id IN (?, ?) AND post_id IN (?, ?) And here is all my current indexes: (id) -- PK (author_id, seen) (author_id, dat

Solution 1:

I would guess an index on type would probably be the only useful one; because A IN (B, C, D) translates to A = B OR A = C OR A = D, and MySQL seems to ignore indices once it encounters an OR.

Alternatively, make/execute four copies of the query with the possible combinations of author_id and post_id; but unless it is a drastic performance issue I probably would not recommend it.

Solution 2:

INDEX(type, author_id)
INDEX(type, post_id)

Those have type first because it is compared = constant. Then they have one IN clause. Newer versions of MySQL will efficiently "leapfrog" ("MRR") through the IN set. I don't think they can do two INs.

The optimizer will look at statistics and choose which of those two indexes is likely to be better.

Still, it may be possible that INDEX(type, other_id, author_id) will be beneficial. What version of MySQL are you running? Can you do EXPLAIN DELETE ... to see how it is likely to perform the query? If you see "ICP" or "Index condition pushdown" or "Using index condition", that will be better. (That depends on a relatively new optimization.)