Will MySQL reorder where condition based on multi columns index order?

21
January 12, 2019, at 02:10 AM

e.g., an index is (column1,column2). Supposing we run a query select * from some_table where column2 > 3 and column1<2, will MySQL be smart enough to reorder query to select * from some_table where column1<2 and column2 > 3 and then use index (column1,column2)?

Answer 1

Yes, MySQL's optimizer can tell that boolean AND is algebraically commutative, that is, A AND B is logically the same as B AND A.

It can reorder terms to match index order when it can do so while preserving logical equivalence.

However, in the case you show, the condition on column1 is a range condition, so MySQL can't use the index to search in the second column. The values in column2 are sorted per value of column1, so they're practically in no order. Searching for rows where column2 > 3 would have to search every subset of rows that match the first term anyway, and by that time, the storage engine will have read those rows into memory, so there's little value in using the index.

READ ALSO
How do I convert a number enum column to tinyint?

How do I convert a number enum column to tinyint?

Developing in Laravel 57, using a MySQL database

35
Ask for a quick update method for records that containing duplicate values

Ask for a quick update method for records that containing duplicate values

I have a table with millions of records, but each object could have multiple columns that share the same values while some other columns have different values

38
How to enter username in form and then search in database to produce if else statement

How to enter username in form and then search in database to produce if else statement

How would I get the $UN Variable to be set by the input provided by the formThen use the variable to search in the database to provide a users details

20
WHERE IN clause with multiple values

WHERE IN clause with multiple values

I'm trying to select items from a table when any values from a list appear in an array in the table

21