W warunku WHERE, jeśli chcemy wyciągnąć rekordy o danej zawartości kolumny, można zastosować dwie różne formy – WHERE kolumna=’coś’ i WHERE kolumna LIKE ‚coś’. Z punktu widzenia efektu, jaki twórca zapytania chce uzyskać oba sposoby dają identyczny efekt:

mysql> SELECT * FROM actor WHERE last_name='SWANK';
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        9 | JOE        | SWANK     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM actor WHERE last_name LIKE 'SWANK';
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        9 | JOE        | SWANK     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

Pojawia się pytanie, czy oba sposoby są identyczne z punktu widzenia wydajności?

Sprawdźmy jak wyglądają plany obu zapytań.

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM actor WHERE last_name LIKE 'SWANK'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: range
possible_keys: idx_actor_last_name
key: idx_actor_last_name
key_len: 137
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM actor WHERE last_name='SWANK'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: ref
possible_keys: idx_actor_last_name
key: idx_actor_last_name
key_len: 137
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)

Jak widać, w obydwu przypadkach wykorzystany jest indeks na kolumnie `last_name`. W przypadku operatora LIKE typ JOINa określany jest jako ‚range’. Zgodnie z dokumentacją znaczy to tyle, że pobierane są dane z podanego zakresu. W tym zapytaniu zakresem jest pojedyncza wartość. Jeśli zamienimy operator z LIKE na ‚=’ w efekcie uzyskamy JOIN typu ref. Stosowany jest on wtedy, gdy JOIN korzysta tylko z części indeksu, bądź też indeks nie gwarantuje unikalności kolumny (czyli nie jest to PRIMARY KEY, bądź też nie jest zdefiniowny jako UNIQUE). Tak po polsku, oznacza to tyle, że JOIN, na podstawie danych w indeksie, nie może wybrać jednego rekordu (bo aktorów o nazwisku „SWANK” może być więcej niż jeden).
Według dokumentacji, JOIN typu ref jest szybszym rozwiązaniem niż JOIN typu range.

Tyle dokumentacja. W praktyce, nie udało mi się tego potwierdzić. Przygotowałem tabelę wielkości 100 tysięcy rekordów i na niej wykonywana była dwadzieścia pętli po 10 tysięcy zapytań. Z wykonanych testów wynika, że zapytanie z operatorem ‚=’ to ok. 9900 qps, a operator ‚LIKE’ to ok. 50 zapytań na sekundę więcej. Biorąc pod uwagę zastosowanie nie koniecznie profesjonalnego podejścia do badania wydajności, można swobodnie przyjąć, że w tym konkretnym przypadku wydajność obu operatorów jest podobna.

Operator LIKE ma także inną cechę, której nie posiada operator ‚=’ – umożliwia stosowanie wildcardów. Jeśli chcemy wyszukać wszystkich aktorów, których nazwisko zaczyna się od liter ‚AL’, możemy skorzystać z następującego zapytania:

mysql> SELECT * FROM actor WHERE last_name LIKE 'AL%';
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      118 | CUBA       | ALLEN     | 2006-02-15 04:34:33 |
|      145 | KIM        | ALLEN     | 2006-02-15 04:34:33 |
|      194 | MERYL      | ALLEN     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
3 rows in set (0.00 sec)

Jego plan wygląda następująco:

mysql> EXPLAIN SELECT * FROM actor WHERE last_name LIKE 'AL%';
+----+-------------+-------+-------+---------------------+---------------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys       | key                 | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------------+---------------------+---------+------+------+-------------+
|  1 | SIMPLE      | actor | range | idx_actor_last_name | idx_actor_last_name | 137     | NULL |    3 | Using where |
+----+-------------+-------+-------+---------------------+---------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

Jak widać, korzysta ono z indeksu na kolumnę `last_name` – indeksy BTREE umożliwiają korzystanie także w przypadku niepełnego dopasowania. Ważne jest tylko to, aby to szukany ciąg zaczynał się od znanego ciągu, a wildcard był po prawej stronie. Konkretnie, na przykładzie wygląda to tak.

Zapytanie:

SELECT * FROM actor WHERE last_name LIKE 'AL%' AND first_name='CUBA';

będzie w stanie skorzystać z indeksu na kolumnę `last_name` – sprawdzana będzie tylko ta część kolumny, która jest przed wildcardem. Wykorzystany także może być indeks na dwie kolumny `last_name`, `first_name` – w praktyce wykorzystana zostanie tylko pierwsza kolumna tego indeksu, ze względu na wildcard w warunku. Nie mając pełnego dopasowania dla kolumny `last_name`, nie ma możliwości skorzystania z kolejnej kolumny w indeksie. Można także skorzystać z indeksu na dwie kolumny `first_name` i `last_name` – tu zapytanie wykorzysta obie kolumny. Pierwsza kolumna w indeksie (`first_name`) ma pełne dopasowanie, dlatego też można szukać dalej, w drugiej kolumnie.

Zastosowanie lewostronnego wildcardu uniemożliwia zastosowanie indeksów. To tak, jakby w słowniku czy encyklopedii szukać haseł kończących się na „ona” – trzeba przeglądnąć wszystkie wpisy i wynotowywać spełniające warunek. Jeśli nie wiadomo od czego zacząć, nie można korzystać z indeksów typu BTREE:

mysql> EXPLAIN SELECT * FROM actor WHERE last_name LIKE '%LEN';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |  200 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

To samo jest w przypadku obustronnego wildcardu:

mysql> EXPLAIN SELECT * FROM actor WHERE last_name LIKE '%LE%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |  200 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Jest to jeden z powodów, dla których wyszukiwanie konkretnych wyrazów czy fraz w bazie najlepiej realizować przy pomocy indeksów pełnotekstowych, o których pisałem w jednym z poprzednich postów.