Почему идет долгий запрос в mysql?

Question

[viktorross]

Здравствуйте, подскажите пожалуйста , кто понимает как подобное исправить, у меня есть такой запрос к примеру, он формируется в php в зависимости от параметров ,которые передаются, выполняется он почти 2 секунды

select class_ads.*, class_ads.id as adid, date_format(class_ads.`date_added`,'%d.%m в %H:%i') as date_nice, date_format(class_ads.`date_expires`,'%d.%m в %H:%i') as date_expires_nice, UNIX_TIMESTAMP(class_ads.`date_added`) as `time_added`, (class_ads.date_expires < '2021-03-14 04:57:27' and class_ads.date_expires!='0000-00-00 00:00:00' and class_ads.active=0) as expired
		, class_slugs.slug 
		
		from class_ads 
		  
		inner join class_slugs on class_ads.id=class_slugs.object_id
		  where class_ads.active=1  and class_ads.category_id in (239,241,242,243,245,246,248,249,201) and ( (class_ads.country like 'Астраханская область')   or class_ads.country like "all"  ) and ( (class_ads.region like 'астрахань')   or class_ads.region like "all"  ) and class_slugs.`type`='listing'  order by  `priority` desc,  date_added  desc  limit 0, 24;

Если убрать параметры ниже, то запрос выполняется 0.0034, что в принципе нормально, учитывая, что это inner join да и записей в базе почти 300 000, при этом индексы на class_ads.country и class_ads.region есть, думал что они отсутствуют, почему так и что можно придумать? подозреваю, что это из-за поиска по тексту, неужели придется создавать отдельную таблицу с id городов , записывать эти id в бд и потом при выполнении данного запроса, передавать из шаблона уже id, а не текст? опять же, если осуществить поиск по заголовку, а там тоже текст 50-200 символов, то та же самая ерунда, это будет долгий запрос, хотя я видел на сайтах поиск и работает он давольно шустро , а база там гораздо объемнее, как вообще решаются подобные вещи?просто увеличивается мощность сервера или есть фишка в запросе, которая устроит процесс?

and ( (class_ads.country like 'Астраханская область')   or class_ads.country like "all"  ) and ( (class_ads.region like 'астрахань')   or class_ads.region like "all"  )

Comments

[AUser0]

Зачем вам LIKE там, где нужно банальное равенство? Можно даже IN.

[viktorross]

AUser0, кстати, я пробовал равенство, тоже самое по времени выходит

[AUser0]

Тогда стройте индекс.
А ещё есть волшебное EXPLAIN SELECT ..., для выяснения в каком месте тормозит.

[viktorross]

AUser0, так там есть индексы, но я уверен что это именно из-за like который долго ищет по 300 000 строк, только вот почему = также долго работает не понятно, а вот in гораздо быстрее

[AUser0]

EXPLAIN SELECT ... - и будет видно, на что БД тратит время.

А IN() - это просто короткий вариант `x`='z' or `x`=2 or `x`=DATE().

[VitalyChaikin]

не уверен что поможет, но что если некоторые условия where перенести в on (строчкой выше)
может тогда то он и начнет использовать индексы ?

[viktorross]

AUser0, VitalyChaikin, в общем сейчас попробовал explain, когда использую in:



а вот когда тот вариант что изначально писал



у кого-нибудь есть догадки почему так? судя по всему в in используются индексы плюс записей 2500 а не 80000, но почему так не понятно

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

Если поставить region и country сразу после where , то в 2 раза быстрее работает, 1,3 секунды

[AUser0]

Включите режим полного отображения полей, а то в первом варианте поле Extra обрезалось. Кроме того непонятно, а где данные для class_slugs?
И 2500 вместо 80000 - это и есть выигрыш от использования индекса.
Можно еще перенести одно условие из WHERE в INNER JOIN.

Answer 1

[Алексей Смирнов]

Судя по структуре запроса тормоза скорее всего из-за поиска в тексте.
1) попробуйте использовать индекс FULLTEXT
2) проверьте на всякий случай наличие индексов на числовых полях; для анализа запроса можно использовать EXPLAIN
3) возможно стоит, как вы и писали, нормализовать структуру таблиц, чтобы текстовый поиск поменять на поиск по ID; это был бы самый надежный способ; как например вы сделали здесь (это удачное решение):

and class_ads.category_id in (239,241,242,243,245,246,248,249,201)

4) в крайнем случае, когда от текстового поиска совсем не уйти, можно применить функцию CRC32(), которая подсчитывает контрольную сумму по строке и эту контрольную сумму сохранить в числовом поле `crc`, по полю построить индекс:

CREATE TABLE `table` (
`crc` INT UNSIGNED NOT NULL DEFAULT '0',
`word` VARCHAR(30) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
INDEX (`crc`);

Потом в запросе искать так:

SELECT * FROM `table` WHERE (`crc`=CRC32(`слово')) AND (`word`='слово');

Но п.4 скорее всего не ваш случай. Я думаю п.3 (создание справочников и выполнение поиска по id) был бы лучшим решением.