Как ускорить поиск для LIKE?

Question

[6b9ff689d67]

У меня есть база sqlite на 22млн строк, в ней много разных полей, по типу name, phone и т.д.
На параметр phone у меня стоит индекс: DESC и BINARY

Когда я делаю прямой запрос по телефону:
SELECT * FROM data WHERE phone == "79123456789"
То поиск занимает не больше одной секунды.

Но если мне нужно сделать поиск через LIKE, то время поиска вырастает до 20-30 секунд.
SELECT * FROM data WHERE phone LIKE "7912345678%"
Можно ли как-нибудь ускорить поиск через LIKE?

Comments

[Олег]

секунда на простой кей-валую запрос ? Индекс постройте по номеру телефона.
В норме такое делается за милисикунды.

лайк гоняйте в виде неполная левая часть LIKE "XXXX%"

Почитайте про эксплайн запросов. У вас сейчас фулл скан всей таблицы.
Должен использоваться индекс или хотя бы ренге сеач

[mayton2019]

Но если мне нужно сделать поиск через LIKE

SQL-Оптимизатор обычно не любит лайк потому-как это осложняет поиск по индексу. В частных случаях когда ты ищешь префикс LIKE "79123%" - это может сработать. Но надо смотреть explain plan. Для триграммных индексов Postgresql можно использовать произвольные выражения типа LIKE "%1%3%" но скорость поиска уже не будет как в варианте с B+Tree индексом.

Вообще если ты ступил на такую скользую дорожку как оптимизации - то должен уметь читать планы запросов. Это так-же важно как отладка и профилирование в программировании. Без этого - ты будешь незнайкой.

[сергей кузьмин]

покажите что дает команда

EXPLAIN QUERY PLAN
SELECT * FROM info WHERE phone LIKE "79123456789"

[6b9ff689d67]

Dr. Bacon, like мне нужен тогда, когда нужно сделать запрос где какая-то цифра в номере будет неизвестна "7912345678%"

[6b9ff689d67]

сергей кузьмин,
[(2, 0, 0, 'SCAN data')]

[сергей кузьмин]

это НЕхорошо

должно говорить

SEARCH TABLE data USING INDEX idx_data_email (email=?)

то есть упомянуть индекс и дополнительно сказать сколько штук сканировало - какими словами не помню

но когда я ваш пример затестировал онлайн
https://www.sqlitetutorial.net/tryit/query/sqlite-...
оно мне тоже сказало

TABLE SCAN

на LIKE

[сергей кузьмин]

@6b9ff689d67

вот решение как создать индекс помогающий LIKE

CREATE TABLE contacts (
	first_name text NOT NULL,
	last_name text NOT NULL,
	email text NOT NULL
);

CREATE UNIQUE INDEX idx_contacts_email
ON contacts (email COLLATE NOCASE) ;

EXPLAIN QUERY PLAN 
SELECT
	first_name,
	last_name,
	email
FROM
	contacts
WHERE
	email LIKE '[e-mail]%';

получаем:

SEARCH TABLE contacts USING INDEX idx_contacts_email (email>? AND email<?)

должно быть быстрее чем TABLE SCAN

если помогло - я положу в ответы вы отметите решением

[6b9ff689767]

сергей кузьмин, я не могу создать индекс с уникальным значением, выдаёт ошибку о том что значения не являются уникальными.

[сергей кузьмин]

6b9ff689767, попробуйте создать убрав в команде UNIQUE
ключевым условием чтобы индекс заработал для LIKE является COLLATE NOCASE (по умолчанию не так)
замерьте производительность и покажите вывод EXPLAIN QUERY PLAN после создания индекса

Answer 1

[сергей кузьмин]

@6b9ff689d67

вот решение как создать индекс помогающий LIKE

CREATE TABLE contacts (
	first_name text NOT NULL,
	last_name text NOT NULL,
	email text NOT NULL
);

CREATE UNIQUE INDEX idx_contacts_email
ON contacts (email COLLATE NOCASE) ;

EXPLAIN QUERY PLAN 
SELECT
	first_name,
	last_name,
	email
FROM
	contacts
WHERE
	email LIKE '[e-mail]%';

получаем:

SEARCH TABLE contacts USING INDEX idx_contacts_email (email>? AND email<?)

должно быть быстрее чем TABLE SCAN