Indexes in MySQL

Question

MySQL

MySQL database
There is a table with fields a and b, more than 10 million records The

task is to optimize the query as much as possible

UPDATE table SET a = b WHERE a > b

Which indexes should be used?

Answer 1

If the probability of a > b is high, then perhaps it would be better to disable the indexes altogether before UPDATE and recalculate them after the query completes.

Answer 2

It is better to check on the test table. Indexes will increase fetch speed but slow update speed. So it is necessary to start from a specific situation, indexes can, on the contrary, only slow down the whole process.

Answer 3

How many fields will it update? If one field changes out of 10 million, then it will be much more difficult to find the required field than to recalculate the necessary indices for it later, i.e. put an index on both.

Answer 4

If the task is periodic - then
- add a field z which will take the value 0 if a <= b and 1 otherwise
- UPDATE table SET a = b WHERE c = 1

Answer 5

For example, do not execute such a query at all, just instead of
SELECT a...
query
SELECT LEAST(a, b)...

Answer 6

Looking at what indexes you have. Updating an indexed field takes significantly longer than a non-indexed one.
- If the fields a and b do not have an index, then we update without any gestures.
- If fields a and b have an index, but not key ones, then we use the advice MiXei4 . Disable indexes on these fields and update.
- If the fields a and b have an index and key, then the only possible way to speed it up is to experiment with the test base, and then slip it instead of the "combat" one. But you understand, if the table is updated frequently, this method is also not possible.