search

Home  >  Q&A  >  body text

Mysql table index problem help

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

高洛峰高洛峰2797 days ago857

reply all(2)I'll reply

  • 淡淡烟草味

    淡淡烟草味2017-06-10 09:51:40

    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

    reply
    0
  • 我想大声告诉你

    我想大声告诉你2017-06-10 09:51:40

    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.

    reply
    0
  • Cancelreply