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.
SELECT * FROM table WHERE a in () AND b =? ORDER BY c DESC
in is used, the index should not be used. How to build this?
SELECT * FROM tabler WHERE a = ? AND b > ? AND b < ? ORDER BY c DESC
How to create an index
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
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
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.