Single table selections
Take a table of users with a set of personal information, indexed by e-mail address. Here are a couple of simple conditions that can benefit from indexes:
EmailAddress = 'vasya.pupkin@mail.ru' wrote instead of LIKE because I was surprised by the results of EXPLAIN, additional research is needed - it is always possible to use an index for a specific value, the server will go through the tree and receive an exact indication of a record or several records in the table. Immediately it should be noted that, by defining a unique column, an index is automatically created on it. What for? Otherwise, it would be expensive before each insertion or change in the table to check all field values for compliance with the condition of uniqueness, and you can quickly check the existence of the record. EmailAddress LIKE 'vacya.pupkin@%' - partial use of the index. I repeat that the leftmost part must be defined and only it can be used to search by index. For example, for the condition EmailAddress LIKE 'vasya.%@mail.ru', the index will also be used, but only vasya will participate in the search in the index structure, and the rest will be checked by sequential scanning of the lines found using the index. We will return to this example when considering the features of composite indices. The same conditions apply to operations with numbers: equality, more, less and others. It is not hard to guess that some conditions can be brought to alternative expressions using elementary operations, for example, BETWEEN, which is also optimized.
( reference to the source )
In this case, you can try using the directive USE INDEX
or FORCE INDEX
.
UPD
As an optimization, you can also try in the query instead of selecting all the fields, "*"
, to list only those fields that you really need. This will reduce the number of selectable and forwarding data.