Почему долго выполняется sql запрос?

Question

[entermix]

База данных: https://github.com/x88/i18nGeoNamesDB (1,3 ГБ; 2,551,294 строк)

На выделенном сервере (Core i5 3.8ГГц (4 ядра) / 8Гб RAM / 2x500Гб SATA), LIKE запрос на поиск города по названию выполняется почти 3.7 сек, это нормально для такой БД? Как ускорить? Поможет ли индексация? Нужно сделать подсказку при вводе города пользователем, будет ли PostgreSQL работать быстрее для этих целей?

Answer 1

[Илья]

Полнотекстовый поиск наверное лучше (не точно).
---
Индекы помогут несомненно, но я думаю они уже заданы, без них было бы дольше 4 секунд. Какие вообще индексы заданы в таблицах?

Comments

[entermix]

> Полнотекстовый поиск наверное лучше (не точно).
Нужно искать именно с использованием LIKE, чтобы показать подсказку пользователю.

По поводу индексов, не заданы.
Поставил uniq на id города (в таблице городов, где наибольшее количество записей), время увеличилось до 4.8628 сек

[Илья]

entermix: вы хотите чтобы машина ехала быстрее, а оптимизируете салон)))) Нужен индекс на том, по которому поиск!

[Илья]

entermix: а про LIKE не понял, разве полнотекстовый поиск не умеют искать подстроку?

[entermix]

Илья: просто полнотекстовая индексация поддерживается только с версии 5.6.4, видимо придется обновить

[Илья]

entermix: ну а вы пробовали сделать индекс для поля по которому ищете? (LIKE для которого делаете)

[entermix]

Илья: FULLTEXT? Сейчас сделал fk индексы по region_id, country_id + uniq по id во всех таблицах, теперь если указать id страны - ищет за 0.0886 сек, а если добавить id региона - 0.0190 сек, но время поиска без country_id, region_id увеличился до 5 сек, в принципе так устраивает, так как поиск будет проходить в зависимости от выбранной страны/региона)

[Илья]

entermix: вот инфа по полнотекстовому индексу www.mysql.ru/docs/man/Fulltext_Search.html . Как с ним работать:
1. Создаем индекс по нужному полю:

ALTER TABLE `country_name` ADD FULLTEXT INDEX `ft_country` (`country_name` ASC);

2. Ищем по данному полю:

SELECT `country_id` FROM `country` WHERE MATCH(`country_name`) AGAINST("подстрока поиска");

Собственно для нужной таблице создайте такой индекс и будет счастье)
---
Можно просто создать индекс для LIKE:

CREATE INDEX `country_name` ON `country` (`country_name`(100));

Но тогда его использовать буду только запросы вида

SELECT `country_id` FROM `country` WHERE `country_name` LIKE "подстрока поиска";
SELECT `country_id` FROM `country` WHERE `country_name` LIKE "подстрока поиска%";

Если добавить '%' вначале строки, то индекс проигнорируется

[entermix]

Илья: говорю же, что полнотекстовые индексы для innodb доступны только с версии 5.6.4 , а в целом все понятно, спасибо :)

[Илья]

entermix: а чем вас MyISAM не устраивает? Как по мне так он заточен на чтение, а Innodb на ACID.

[entermix]

Илья: да ничем, просто там innodb, сменить тоже кстати вариант)

Answer 2

[Roman K]

1. Если выполняется LIKE с двумя wildcard'ами по бокам (LIKE '%something%') - обычные индексы не работают. Если с wildcard'ой на конце - работают.
2. Не знаю, что там у MySQL, у PostgreSQL есть pg_trgm расширение как раз для такого поиска (LIKE с wildcard'ами) + индекс gin/gist. Вот только сегодня отвечал. У нас сейчас стоит такой индекс, в таблице чуть больше 10млн записей. Работает достаточно быстро.
3. Конфиг дефолтный сразу в топку. Нужно, чтобы БД более агрессивно использовала ОЗУ.

Answer 3

[Мирон]

Если Вам нужен очень быстрый ответ, разбиваете имена городов на буквы, создаёте XML индекс, и выбираете имена городов во временную таблицу после чего заканчиваете запрос по ключу города. В Постгрес столько расширений что я почти уверен, вы найдете все ответы за несколько запросов в Яндексе.