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

Question

[viktorross]

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

вот запрос из лога mysql он постоянно дублируется

# Time: 190819 16:02:08
# User@Host: t_one[t_one] @ localhost []  Id: 25065
# Schema: t_db  Last_errno: 0  Killed: 0
# Query_time: 6.323997  Lock_time: 0.000257  Rows_sent: 12  Rows_examined: 264767  Rows_affected: 0
# Bytes_sent: 29507
SET timestamp=1566219728;
select distinct 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 < '2019-08-19 16:02:01' 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_slugs.`type`='listing'  order by  `priority` desc,  date_added  desc  limit 0, 12;

индексы все есть насколько я вижу

Comments

[Модератор]

viktorross дорогой пользователь, настоятельно рекомендуем еще раз обратить самое пристальное внимание на п. 3.1 регламента работы сервиса (и, в особенности, на его последний абзац). В противном случае, ваши вопросы будут удаляться по причине тег-спама, а систематические нарушения приведут к блокировке учетной записи.

[Евгений Самсонов]

Начать можно c EXPLAIN

[viktorross]

Евгений Самсонов, вот этот запрос

$str_index = "";
		if($this->use_index) $str_index = " use index ({$this->use_index}) ";

		$sql = "select distinct ".TABLE_ADS.".*$mlang_vars, ".TABLE_ADS.".id as adid, date_format(".TABLE_ADS.".`date_added`,'$date_format') as date_nice, date_format(".TABLE_ADS.".`date_expires`,'$date_format') as date_expires_nice, UNIX_TIMESTAMP(".TABLE_ADS.".`date_added`) as `time_added`, (".TABLE_ADS.".date_expires < '$timestamp' and ".TABLE_ADS.".date_expires!='0000-00-00 00:00:00' and ".TABLE_ADS.".active=0) as expired
		$str_slugs
		$q_vars 
		from ".TABLE_ADS." $str_index
		$q_join $join_pictures $join_cat $join_users
		$join_slugs
		 ".$where.$where_slugs." ".$order_by." ".$order_way." ";
		if($ads_per_page>0) $sql .= " limit ".$general_row.", ".$ads_per_page;

я добавил explain перед select, страница открылась мгновенно без данных естественно, просто шаблон, в лог ничего не записалось

[Евгений Самсонов]

viktorross, нужно выполнить EXPLAIN от этого запроса через клиент и проанализировать результат. Будет ясно, из-за чего работает медленно. Дальше уже смотреть, что можно с этим сделать

[viktorross]

Евгений Самсонов, Вы не могли бы пожалуйста объяснить как это сделать подробнее, что за клиент и т.д я никогда не анализировал базу данных, просто создавал таблицы, и подключал их к скрипту, а сейчас возникла такая проблема и я не знаю что и как проверять

[ThunderCat]

количество записей в таблицах? Индексы?

[viktorross]

ThunderCat, 140 тысяч записей в class_ads и class_slug столько же(это ссылки подставляются в нужном формате к каждой странице)

Индексы есть , все что указаны в запросе точно есть, могу скриншот всех индексов сделать , может чего-то не хватает

[viktorross]

ThunderCat, раньше не было таблицы class_slug и вообще этой функции( изменить адрес ссылки) после ее добавления началось

[ThunderCat]

viktorross, скриншот експлэйна из пхпмайадмина будет полезнее, и вы уверены что запрос тормозит? Выполните из пхпмайадмин и гляньте за сколько выполняется(хотя и в експлэйне видно обычно)

[viktorross]

ThunderCat, уверен, я как-то даже писал в службу поддержки сервера чтобы они посмотрели что именно тормозит, они прислали этот запрос и указали время около 15 секунд, можете объяснить как сделать explain , через что именно? Я просто никогда не делал этого, может есть инструкция какая-то подробная

[ThunderCat]

viktorross, phpmyadmin на любом хостинге обычно есть - вкладка sql - explain ваш_запрос

[viktorross]

ThunderCat, спасибо большое, запрос сделал, результат за секунду был получен

вот запрос

EXPLAIN SELECT DISTINCT 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 <  '2019-08-19 20:07:29'
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_slugs.`type` =  'listing'
ORDER BY  `priority` DESC , date_added DESC 
LIMIT 0 , 12

Результат прикрепил на скриншоте

[ThunderCat]

viktorross, расшифровки по времени запросов тут нет, попробуйте просто выполнить это запрос без експлейн, посмотрите скорость выполнения, по результату запроса выдаст количество строк и время. Визуально все должно работать быстро, используются индексы и в плане всего 2 шага. Если будет долго - читайте https://habr.com/ru/post/211022/

[viktorross]

ThunderCat, без explain долго, вывел 12 строк, время почти 7.5 секунд.. я посмотрел то что там написано, у меня есть индексы в базе, а если добавить explain перед select в самом скрипте то база перестает работать , т.е ничего не выводится кроме ошибки подключения

[ThunderCat]

viktorross,

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

что логично, так как эта функция делает анализ, а не выборку.

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

[viktorross]

ThunderCat, смотрите, я в этом запросе убрал distinct, теперь запрос занимает 0,003 сек
какие последствия могут быть? визуально все работает..

SELECT /*DISTINCT*/ 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 <  '2019-08-19 20:07:29'
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_slugs.`type` =  'listing'
ORDER BY  `priority` DESC , date_added DESC 
LIMIT 0 , 12

[ThunderCat]

viktorross, кстати да, там у вас дистинкт на звездочку? 0_о

[viktorross]

ThunderCat, получается что да, если смотреть на запрос
SELECT class_ads. * , class_ads.id AS adid,

[viktorross]

ThunderCat, в скрипте такой код

select ".TABLE_ADS.".*$mlang_vars, ".TABLE_ADS.".id as adid

что вот это такое
.*$mlang_vars, я даже сейчас уже и не знаю

[ThunderCat]

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

[viktorross]

ThunderCat, в любом случае работает без него и слава богу, потом может быть что-то придумаю, пока что разница видна только при загрузке страницы

Спасибо Вам большое за помощь))

Answer 1

[nozzy]

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

select t1.*, t2.*
from (select * from class_ads where class_ads.active=1) as t1
inner join (select * from class_slugs where class_slugs.`type`='listing') as t2 on t1.id = t2.object_id

Comments

[viktorross]

@nozzy
надо получается этот кусок переделывать?

$str_index = "";
		if($this->use_index) $str_index = " use index ({$this->use_index}) ";

		$sql = "select distinct ".TABLE_ADS.".*$mlang_vars, ".TABLE_ADS.".id as adid, date_format(".TABLE_ADS.".`date_added`,'$date_format') as date_nice, date_format(".TABLE_ADS.".`date_expires`,'$date_format') as date_expires_nice, UNIX_TIMESTAMP(".TABLE_ADS.".`date_added`) as `time_added`, (".TABLE_ADS.".date_expires < '$timestamp' and ".TABLE_ADS.".date_expires!='0000-00-00 00:00:00' and ".TABLE_ADS.".active=0) as expired
		$str_slugs
		$q_vars 
		from ".TABLE_ADS." $str_index
		$q_join $join_pictures $join_cat $join_users
		$join_slugs
		 ".$where.$where_slugs." ".$order_by." ".$order_way." ";
		if($ads_per_page>0) $sql .= " limit ".$general_row.", ".$ads_per_page;
//	echo $sql."<br/><br/>";
		$arr = $db->fetchAssocList($sql);