Use seu índice, mesmo com um varchar | | serviço doméstico

Original post: http://anothermysqldba.blogspot.com/2014/01/use-your-index-even-with-varchar-char.html

Eu observei recentemente um post sobre o forums.mysql.com site: Como a jejuar em 3 milhões registro de pesquisa? 
O exemplo dado utilizado um LIKE '% eed' 

Isso não será aproveitado de um índice e vai fazer uma varredura completa da tabela. 
Abaixo está um exemplo usando o banco de dados do mundo, então não 3 milhões de discos, mas apenas tentando mostrar como ele funciona. 

> explain select * from City where Name LIKE '%dam' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: City
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4188
Extra: Using where
1 row in set (0.01 sec)

[world]> select count(*) FROM City;
+----------+
| count(*) |
+----------+
| 4079 |
+----------+

> select * from City where Name LIKE '%dam';
+------+------------------------+-------------+----------------+------------+
| ID | Name | CountryCode | District | Population
+------+------------------------+-------------+----------------+------------+
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
| 6 | Rotterdam | NLD | Zuid-Holland | 593321 |
| 1146 | Ramagundam | IND | Andhra Pradesh | 214384 |
| 1318 | Haldwani-cum-Kathgodam | IND | Uttaranchal | 104195 |
| 2867 | Tando Adam | PAK | Sind | 103400 |
| 3122 | Potsdam | DEU | Brandenburg | 128983 |
+------+------------------------+-------------+----------------+------------+

Para mostrar o ponto mais 

> explain select * from City where Name LIKE '%dam%' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: City
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4188
Extra: Using where
1 row in set (0.00 sec)

> select * from City where Name LIKE '%dam%';
+------+------------------------+-------------+----------------+------------+
| ID | Name | CountryCode | District | Population |
+------+------------------------+-------------+----------------+------------+<
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
| 6 | Rotterdam | NLD | Zuid-Holland | 593321 |
| 380 | Pindamonhangaba | BRA | São Paulo | 121904 |<
| 625 | Damanhur | EGY | al-Buhayra | 212203 |
| 1146 | Ramagundam | IND | Andhra Pradesh | 214384 |
| 1318 | Haldwani-cum-Kathgodam | IND | Uttaranchal | 104195 |
| 1347 | Damoh | IND | Madhya Pradesh | 95661 |
| 2867 | Tando Adam | PAK | Sind | 103400 |
| 2912 | Adamstown | PCN | – | 42 |
| 3122 | Potsdam | DEU | Brandenburg | 128983 |
| 3177 | al-Dammam | SAU | al-Sharqiya | 482300 |
| 3250 | Damascus | SYR | Damascus | 1347000 |
+------+------------------------+-------------+----------------+------------+<
12 rows in set (0.00 sec) 

A saída de explicar acima mostra que há índices estão sendo usados. 

CREATE TABLE `City` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB
<

Assim, por sorrisos vamos colocar uma chave no campo varchar. Repare que eu não colocar uma chave em toda a faixa de apenas os primeiros caracteres. Este é, naturalmente, dependente de seus dados. 

> ALTER TABLE City ADD KEY name_key(`Name`(5));
Query OK, 0 rows affected (0.54 sec)

CREATE TABLE `City` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
KEY `name_key` (`Name`(5)),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB

SO vai este mesmo assunto? 

> explain select * from City where Name LIKE '%dam' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: City
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4188
Extra: Using where
1 row in set (0.00 sec) 

Não, não vai importar, por causa do LIKE '% barragem "vai forçar uma varredura completa de qualquer maneira. 

> EXPLAIN select * from City where Name LIKE '%dam' AND CountryCode = 'IND' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: City
type: ref
possible_keys: CountryCode
key: CountryCode
key_len: 3
ref: const
rows: 341
Extra: Using index condition; Using where
1 row in set (0.00 sec) 

Notar a diferença na saída explicado acima. Esta consulta está usando um índice. Ele não está usando o nome do índice, mas ele está usando um índice. Então, como você pode tirar vantagem do índice varchar? 

> EXPLAIN select * from City where Name LIKE 'Ra%' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: City
type: range
possible_keys: name_key
key: name_key
key_len: 5
ref: NULL
rows: 35
Extra: Using where
1 row in set (0.00 sec) 

A consulta acima irá utilizar o índice name_key. 

O ponto é que você tem que ter cuidado como você escreve sua consulta SQL e garantir que você executar explica para encontrar o melhor índice de escolha para sua consulta.