How to make a composite index for duplicate fields in where with and and or, mysql?

Question

MySQL

A query like this
FROM `doctors`WHERE
`doctors`.`name_last` LIKE 'ROBBERT MO%' OR
`doctors`.`name_first` LIKE 'ROBBERT MO%' OR
`doctors`.`name_first` LIKE 'ROBBERT%' AND
` doctors`.`name_last` LIKE 'MO%'
Likes two fields name_last and name_first
What compound index should I do for this query?

Answer 1

here is an index. if it is very large, or the selectivity of the fields is very different, you can limit yourself to the most selective field.
CREATE INDEX idx1 ON doctors (name_first, name_last)
query with OR does not use indexes, it must be rewritten to UNION

Answer 2

The first query needs two indexes starting with name_last and name_first respectively. Because OR is used - one composite index will not be enough.
For the second query, one composite index from both fields is technically sufficient, but in practice it depends on the sql engine. The order is not important, but for the performance of the selection it is desirable to put the field with the maximum variability first in the index, i.e. then where there are more different options for the value.
In total, for both queries, two indexes are needed - one composite from both fields, the second - from one field, which ends with the first index:
CREATE INDEX idx1 ON doctors (name_first, name_last)
CREATE INDEX idx2 ON doctors (name_last)