Stack Stack - 1 year ago 53
SQL Question

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, date_time)
(type, other_id, author_id)
(comment_id, type, author_id)

Well, Should I add any new index for query above?

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download