Долгий запрос mysql, что переделать?

Question

[viktorross]

здравствуйте, подскажите как должен выглядеть этот запрос в нормальном виде, чтобы работал быстро?
Сейчас это 6 секунд, если убрать в самом начале class_ads.*, то 1,5 секунды, но тогда выведется только несколько столбцов, как это оптимизировать все

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 < '2020-01-31 08:13:36' 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_categories_lang on class_categories_lang.id = class_ads.category_id  
		inner join class_slugs on class_ads.id=class_slugs.object_id
		  where class_ads.active=1  and class_slugs.`type`='listing'  order by  `priority` desc,  date_added  desc  limit 0, 36;

Comments

[ettychel]

Попробуйте вписать поля, вместо использования *

[viktorross]

ettychel, тут проблема.. полей 140 штук

[Dmitry Tallmange]

EXPLAIN

[Андрей Николаев]

viktorross, А вам обязательно нужно все 140 полей? И зачем join в таблицу class_categories_lang?

[viktorross]

Dmitry Tallmange, сделал, а что там нужно увидеть?

SIMPLE | class_categories_lang | index | idx_id | idx_id | 4 | NULL | 329 | Using index; Using temporary; Using filesort

[viktorross]

Андрей Николаев, да, они в цикле потом выводятся , не все сразу, но определенные, в общем убирать звездочку нельзя, иначе весь сайт переписывать придется, проблема в том что этот код работал нормально в принципе, 1,5-2 секунды максимум занимал запрос, со вчерашнего дня долго стало, при этом ничего не менялось

[ettychel]

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

[Дмитрий]

Ну как минимум убрать преобразования даты в php

[ettychel]

viktorross, Количество строк растет, вы задаете условия скорее всего не по индексам, вот и проблема(

[viktorross]

ettychel, вот смотрите, у меня есть еще одна такая же таблица, но там 10 тысяч записей, в первой - 180 тысяч, я делаю тот же самый запрос в эту таблицу и мне выдает результат за 0.0023 сек, сделал explain как тут посоветовали , разница на фото приложил-быстрая таблица

-медленная

[viktorross]

Дмитрий, сократило время запроса на пол секунды)

[Руслан .]

viktorross, Андрей Николаев, не только про * писал.
Если убрать таблицу class_categories_lang из которой вообще ничего не выводится, то д. б. быстрее.
Звездочка же стоит только на class_ads.

Или у вас связка с этой таблицей нужна чтобы отсекать невалидные записи из class_ads?

[ettychel]

viktorross,

inner join class_categories_lang on class_categories_lang.id = class_ads.category_id

Этот join точно нужен?
Может хватит where?

from class_categories_lang l, class_ads a
where l.id = a.category_id

ну и соответственно в select указать столбцы которые нужны

[ettychel]

Руслан ., скорее всего чтобы отсекать, но это можно сделать и через where и будет работать гораздо быстрее

[viktorross]

ettychel, я сейчас начал тупо выполнять данный запрос несколько раз подряд в phpmyadmin и увидел что он выдает те же данные то за 0,0004 то за 5.0123 сек, через раз в общем, мне кажется проблема не в запросе, кстати говоря этот код работал быстро до вчерашнего дня..

[ettychel]

viktorross, кэш отдал так быстро скорее всего. а потом произошло переполнение и выбралось снова, скорее всего его недостаток с введением новых запросов стал причиной, либо за это время был какой либо insert или update, что также выбрасывает запрос из кэша, но во всяком случае уберите не нужный join, он тормозит выполнение.

Как часто происходят изменения записей или вставка новых?

[viktorross]

ettychel, вы правы были, убрал inner join class_categories_lang on class_categories_lang.id = class_ads.category_id, время 0,00022 стало, осталось только понять зачем он его туда добавлял)

[ettychel]

viktorross, часто это просто ошибка, коллега на такое наткнулся, когда я ещё был в начале своего пути. Я тогда просто не переваривал и не понимал запросы с join, поэтому всегда старался их избегать и как то раз он сидел и матерился на запрос такого рода, он отрабатывал около 5 минут)) тогда я просто предложил сделать where, потому как сам так делал и запрос выполнился за секунду)) Вот такая история, для меня она была очень яркой, помогла запомнить, что join не всегда и не везде нужен)
А добавлен он скорее всего туда для того чтобы отсечь ненужные строки, как и предполагали Руслан ., и я ранее

[viktorross]

ettychel, спасибо Вам огромное, очень помогли, я бы не догадался что тут не так, сидел бы дальше менял местами все))

[ettychel]

viktorross, и ещё совет по *, лучше отказаться от нее, посмотрите какие поля действительно используются, если же действительно используются все, то пропишите ручками их, это тоже сократит время запроса, я читал об этом, правда сейчас не могу найти ту статью
С вашего позволения оформлю как ответ, чтобы потом другие пользователи могли воспользоваться этой информацией.

[viktorross]

ettychel, хорошо, спасибо огромное)) я кстати пытался найти кнопку Отметить решением, уверен, что это будет действительно полезно еще кому-то, но почему-то кнопки такой нет..

Answer 1

[ettychel]

Решение было предложено в комментариях.

inner join class_categories_lang on class_categories_lang.id = class_ads.category_id

Этот join точно нужен?
Может хватит where?

from class_categories_lang l, class_ads a
where l.id = a.category_id

ну и соответственно в select указать столбцы которые нужны

Comments

[AUser0]

Что-то мне чудится, что и этот вариант не на долго даст скорости. Если база не стала пользоваться индексами при INNER JOIN, то и при WHERE не станет, особенно по мере наполнения таблиц данными.

Вердикт: Лучше бы автору вопроса подналечь на построение индексов, они для этого и придуманы...

[ettychel]

AUser0, проблема join в том как выстраивается план запроса, в случае where он выстраивается по другому, я и про это читал, но было очень давно и я уже не могу найти источника, но это зависит от БД, потому как план может быть и одинаков. Много индексов тоже не всегда хорошо, например, если поле под индексом часто изменяется, то это будет лишь тормозить запросы и работу БД. В этом случае лучше иметь несколько таблиц с распределенными данными и несколькими связями, так в случае частых изменений индексы могут быть не затронуты там где это не нужно, об этом также говорилось в комментариях

Answer 2

[Дмитрий]

Без структуры бд - Гадание на кофейной гуще. Нужны схемы таблиц, индексы, констрейнты.

Comments

[FanatPHP]

нужен explain.
схемы таблиц, индексы, констрейнты - гадание на кофейной гуще

[Дмитрий]

FanatPHP, ты по индексам и прочей штуковине сам не сможешь сделать explain? тут наверняка проблема в этом. И глубокий explain не понадобится.

[FanatPHP]

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