Good afternoon, there is such a table:

CREATE TABLE IF NOT EXISTS `ip_city` ( `ip_from` int(10) unsigned NOT NULL, `ip_to` int(10) unsigned NOT NULL, `locid` int(10) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Indices:

 ip_from BTREE ip_from ip_to BTREE ip_to ip_from_2 BTREE ip_from, ip_to 

And request:

 SELECT * FROM `ip_city` WHERE (`ip_from` < 2995257412) AND (`ip_to` > 2995257412) 

I just can’t achieve the imputed result, no index clings.

  • And what does "no index cling" mean? - Ksenia
  • EXPLAIN shows that no index is used - Artem Bondarenko
  • Add, please, the output of the command with explain - Ksenia
  • id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE ip_city ALL ip_from, ip_to, ip_from_2 1709292 Using where - Artem Bondarenko
  • The composite index cannot be used here precisely, because it would be useful if ip_from were checked for strict equality. in this case, MySQL could use any one of the two indices, but apparently he believes that according to the given condition, he will select too many records. In this case, the work on the index will be slower than a full table scan. That the index was used MySQL should understand that the index is selective, i.e. by a given condition (one of the two indicated), we will get, let's say, no more than 1% of all records. on more / less terms it is unrealistic - Mike

2 answers 2

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.

  • FORCE INDEX helped a bit - Artem Bondarenko

The optimizer starts using indexes only if, according to its preliminary estimate, the number of records as a result will be no more than 30% of the total number of records in the table. Otherwise, he absolutely rightly believes that it will be faster to stupidly shovel all the records in the table without using indexes.

The index will not be used if the use of the index requires MySQL to pass more than 30% of the rows in this table (in such cases, viewing the table is likely to be much faster because you will need to perform fewer search operations).

Therefore, put at least FORCE INDEX or USE INDEX - will not become faster.