Mysql table index problem help

Question

I encountered several problems when setting up a data table index. Please ask for help:

1.

UPDATE table SET version = 2 WHERE id = ? AND version =?

In this statement, the id is a self-increasing id, and the version is an optimistic lock. At this time, should we create a separate index for version, a joint index with id, or no need to create an index.

1. SELECT * FROM table WHERE a in () AND b =? ORDER BY c DESC

If

in is used, the index should not be used. How to build this?

1. SELECT * FROM tabler WHERE a = ? AND b > ? AND b < ? ORDER BY c DESC

How to create an index

Answer 1

1. Just index verson separately. Since id is the primary key and unique, the indexing efficiency is actually the highest.
2.in can enable indexing, but when in reaches a certain number, it may fail
3.a and b can be indexed separately. Or do a and b as a joint index

Answer 2

Under normal circumstances, it is enough to create an index for the id field alone.
If according to the business scenario, it is expected that each ID will be modified a lot, you can create a joint index for ID and version.