Ускорение запроса к БД

Есть таблица с 6 млн записей следующего вида:
id (primary key, инкремент)
artik (уникальные поля, varchar(30) , utf8_general_ci)
data
descr
price

Я осуществляю поиск по полю artik , следующим запросом ( ... where artik LIKE %2345% ...)
Поиск занимает около 10 сек.

Подскажите, пожалуйста, как можно ускорить данный запрос?

Операция LIKE сама по себе работает очень медленно, до какой-то степени Вы можете ускорить этот запрос, если сделаете индекс по полю artik, но драматических изменений ожидать не стоит. Пожалуй, более радикальные результаты получатся если каким-то образом избавиться от операции LIKE, например заменить ее каким-то арифметическим сравнением. Разумеется, все надо будет тщательно тестировать и измерять.

PS. Вообще-то 10 сек - это Вам крупно повезло. Недавно ждал тут 11 минут пока похожий запрос чего-то там вернул.

Цитата ("itif"):

Я осуществляю поиск по полю artik , следующим запросом ( ... where artik LIKE %2345% ...)

Так и должно быть.
Чтобы использовались индексы, ищи по шаблону "2345%".

Цитата ("Illarion_SA"):

Операция LIKE сама по себе работает очень медленно, до какой-то степени Вы можете ускорить этот запрос, если сделаете индекс по полю artik, но драматических изменений ожидать не стоит.

Дело не в Like, а в шаблоне поиска.
Шаблоны вида "expression%" индексы вполне ускоряют.

Illarion_SA, спасибо
попробую сделать индекс по данному полю.

alibek,
"Шаблоны вида "expression%" индексы вполне ускоряют." - когда запрос выполняется 10 секунд это не очень хорошее ускорение...

1. какие данные хранятся с поле artik? если только числа и латинские буквы, то можете отказаться от UTF-8 в пользу любой однобайтовой кодировки, LIKE будет работать намного быстрее.
2. поиск по LIKE - регистронезависимый, если по Вашей задаче это не нужно (скорее всего так и есть), то можно для этого поля вообще использовать тип BINARY.
3. если в поле artik хранятся только числа, и если они не очень длинные, вообще откажитесь от VARCHAR в пользу целочисленных типов
4. определите максимальную длину значения в поле artik. если меньше 30, то уменьшите размер поля. чем меньше размер, тем больше записей одновременно будет в памяти, и меньше будет обращений к диску. производительность вырастет весьма заметно.
5. делать индекс имеет смысл только в том случае, если первым символом в маске поиска не стоит %, если же стоит - то лучше от индекса избавиться (если конечно он не нужен для других запросов), он только память будет занимать.
6. подредактируйте конфиг базы данных. поэкспериментируйте с выделяемой памятью, буферами и т.д.

IMHO - 6 миллионов записей это вообще ничто, уверен, можно соптимизировать на этом запросе вплоть до 0.1 сек и менее )

Еще прием, ультимативно ускоряет такие запросы.
Создайте вместо одной - две таблицы.

id (primary key, инкремент)
artik (уникальные поля, varchar(30) , utf8_general_ci) - только тут с размером поля и кодировкой подумайте, как я и написал выше

и

id (primary key, инкремент)
data
descr
price

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

Цитата ("tvv"):

id (primary key, инкремент)
...
и
...
id (primary key, инкремент)

Да?

Tvv опередил, хотел предложить тоже самое.... Отличная идея, попробуйте так реализовать...

tvv, спасибо большое.

artik содержит только цифры и латинские буквы.


идея с разделом на две таблицы не очень удобна, т.к. на процесс конвертации с аксесовской базы, добавления некоторых полей, заливка на хостинг и так ушло около 16 часов. и добавление этого действия еще добавит часа 4. Буду применять его в крайнем случае.

alibek, конечно же автоинкремент только в одной из таблиц, в моем посте выше - опечатка (из-за копипаста).

itif, в любом случае, конвертация - это разовая операция, а запросы, как я понял, будут делаться постоянно. Поэтому все же имеет смысл разок это сделать.

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

Первые результаты тестирования:

1) Без оптимизации время среднее время поиска: 8.1 сек
2) После изменения с varchar (30),utf8_general_ci в varchar(19),utf8_general_ci : 8.1 сек
3) после выставления типа binary (19): 7.1 сек (вместо пробелов в конце появились символы \0), таблица занимает 561 мб
4) после выставления index : 7 сек , таблица занимает 700 мб

Тестирование продолжается ....

После того, как что-то изменяете, например меняете varchar(30) на varchar(19), пожалуйста обязательно делайте OPTIMIZE TABLE.

tvv, спасибо.

Этого я не делал.

Также, какой движок для работы с данной таблицей Вы используете, MyISAM или InnoDB?
Если Вам не нужна поддержка транзакций в данном случае, смело можете использовать MyISAM, выигрыш производительности на таких запросах может достигать 30-50%, некоторое время назад мне это советовал друг, который работал в компании MySQL AB.

А вот индекс пока рекомендую временно удалить, и достигнуть максимум возможного без него. Потом добавить его всегда сможете.
И пожалуйста, не забудьте вариант с сокращением таблицы только лишь до двух полей - id и artik, должно очень помочь.

Продолжение тестирования:

5) после optimize table : 7.2 сек
6) по совету tvv разделил данную таблицу на две. Затем optimize . Итого: среднее время поиска составило 2.5 сек.

Данные показатели уже устраивают. Спасибо.


Несколько вопросов:

- Подскажите, пожалуйста, что значит int(11) ? 11 - это кол-во байт на одну цифру в числе или макс кол-во цифр в хранимом числе?
- чем отличается int(3) от tinyint(3) ?


Цитата ("tvv"):

6. подредактируйте конфиг базы данных. поэкспериментируйте с выделяемой памятью, буферами и т.д.

С какими именно переменными стоит поиграться в my.cnf ?

Цитата ("tvv"):

Также, какой движок для работы с данной таблицей Вы используете, MyISAM или InnoDB?

используется тип MyISAM

Кстати, в 6 пункте я удалил index


А можно ли сделать что-нибудь еще для уменьшения времени?

Цитата ("itif"):

- Подскажите, пожалуйста, что значит int(11) ? 11 - это кол-во байт на одну цифру в числе или макс кол-во цифр в хранимом числе?
- чем отличается int(3) от tinyint(3) ?

int(11) - это максимум 11 знаков, не только цифры, но и возможный знак минус.

int(3) - занимает 4 байта, дозволенные значения от -99 до 999.
tinyint(3) - 1 байт, дозволенные значения от -99 до 127.

Цитата ("itif"):

А можно ли сделать что-нибудь еще для уменьшения времени?

Как я и говорил, поработать с конфигом.
Что именно там делать - нужно владеть теорией БД, поэтому долго рассказывать, сожалею...
Попробуйте разобраться по мануалам и поэкспериментировать.
Цель - минимизировать обращение к диску, буквально считать количество обращений к диску при каждом запросе.

Далее, есть еще варианты ускорения. Но для этого нужно знать больше о природе данных.
Можете пожалуйста привести пару десятков примеров значений поля artik?
Также, какое среднее соотношение цифр и латинских букв в значениях этого поля? Чего больше, и насколько?
Может ли такое быть, что все значение целиком состоит только из букв, или только из цифр?