Otimização de pesquisa em parte de campo texto

Qual das duas consultas abaixo será mais rápida?

1. SELECT * FROM table1 WHERE field1 LIKE 'value%'
2. SELECT * FROM table1 WHERE (field1 >= 'value' AND field1 <= 'valuezzzzzz')

Quando fazemos uma consulta como a #1 (field1 LIKE 'value%') normalmente o banco de dados despreza qualquer índice que possa ser utilizado para uma pesquisa neste campo, realizando assim um varredura completa (full scan) em cada registro da tabela, o que pode ser muito oneroso. Digo 'normalmente' pois alguns bancos (MySQL, se não estou enganado), sabendo que esta pesquisa normalmente indica trata-se de um pesquisa do tipo 'começando com', verifica se é possível utilizar um índice, ou seja, trata-se de uma otimização específica de cada SGBD, não podendo ser considerada como padrão.

Quando fazemos uma consulta como a #2 (field1 >= 'value' AND field1 <= 'valuezzzzzz'), se não tivermos um índice a varredura também deverá ser completa. Entretanto, se tivermos um índice para o campo 'field1',o banco de dados deverá utilizar os mecanismos de índice para esta pesquisa. Mas o que esta instrução indica ao SGBD? qualquer campo 'varchar' permite pesquisar usando 'maior que' e/ou 'menor que', entretanto, uma base como a apresentada abaixo.

CREATE TABLE table1 (

id bigint(20) unsigned NOT NULL auto_increment,

field1 varchar(200) default NULL,

PRIMARY KEY (id),

KEY idx_field1 (field1)

)

INSERT INTO table1 (field1) VALUES ('ana beatriz');

INSERT INTO table1 (field1) VALUES ('anadia cordoba');

INSERT INTO table1 (field1) VALUES ('celso portela');

INSERT INTO table1 (field1) VALUES ('yasmin');

INSERT INTO table1 (field1) VALUES ('yasmin macardi');

INSERT INTO table1 (field1) VALUES ('yasmin elondiz');

INSERT INTO table1 (field1) VALUES ('value');

Dadas as consultas:

1. SELECT * FROM table1 WHERE field1 >= 'ana'
2. SELECT * FROM table1 WHERE field1 >= 'yasmin'
3. SELECT * FROM table1 WHERE (field1 >= 'ana' AND field1 <= 'anazzzzzz')
4. SELECT * FROM table1 WHERE field1 BETWEEN 'ana' AND 'anazzzzzz'

Se executarmos a consulta #1, retornará todos os registros, tendo em vista que todos os registro tem o campo 'field1' com valores maiores ou iguais a 'ana', visto que, dado o método de classificação por ordem alfabética, não temos letra menor que 'a'.

Se executarmos a consulta #2, obteremos apenas 3 registros ('yasmin', 'yasmin macardi', 'yasmin elondiz'), conforme método anteriormente citado. Lembrando que o registro que armazena 'value' não será retornado pois a ordem utilizada não é por inserção e sim por ordem alfabética.

Na consulta #3, temos a explicação de um (AND field1 <= 'anazzzzzz') ser adicionado. Ao menos nas línguas que usam o alfabeto de origem grega (nosso alfabeto, por exemplo, assim como o inglês), o 'z' sendo o último caractere deste alfabeto, e repetido de 5 a 10 vezes (por ser pouco provável que alguém tenha um nome assim, por exemplo), nos dá uma boa 'string' delimitadora para a critério de maior faixa de string a ser pesquisada.

A consulta #4 é excelente para quem gosta de escrever menos e deixar claro o que deseja (aqui os questionamentos dos leitores deverá ser maior...), já que é, digamos, igual à consulta #3.

Cuidados necessários:

1. Se teu banco de dados realizar uma pesquisa 'case sensitive', pode não lhe ser útil, o uso da função 'upper' forçará uma pesquisa 'full scan' de qualquer forma, salvo se teu banco suportar a criação de índices com funções.
2. Caso seja utilizado um 'alfabeto' (code page) que não siga esta lógica apresentada.
3. Caso alguém se chame 'josé dos zzzzzzzzzzzzzzzzzzzzz', por exemplo.
4. Em algumas bases, se for informado um espaço em branco após o valor (field1 >= 'yasmin ' AND field1 <= 'yasmin zzzzzz') pode trazer os que tem espaço em branco e os que não têm ('yasmin', 'yasmin macardi', 'yasmin elondiz'), ou seja, (field1 >= 'yasmin ' AND field1 <= 'yasmin zzzzzz') pode (field1 >= 'yasmin' AND field1 <= 'yasminzzzzzz') darão resultados iguais.
5. Lembre-se, sempre teste com sua massa de dados, depende muito de como seu SGBD utiliza os índices. Ou seja, faça um benchmark entre os métodos, o que lhes apresento aqui, o LIKE e outro que seu SGBD oferece.

O uso de índice em pesquisa com parte do texto é de extrema relevância em bases de dados muito grande, se sua base não oferecer uma solução que seja melhor que 'LIKE' para consultas em parte de campo, ao menos do tipo 'começando com', procure utilizar as dicas apresentadas.