Не могу понять,почему запрос mysql долгий?

Question

[viktorross]

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

# Time: 190903 23:07:28
# User@Host: i_one[i_one] @ localhost []  Id: 163664
# Schema: i_db  Last_errno: 0  Killed: 0
# Query_time: 1.488142  Lock_time: 0.000337  Rows_sent: 12  Rows_examined: 237365  Rows_affected: 0
# Bytes_sent: 33159
SET timestamp=1567541248;
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 < '2019-09-03 23:07: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 like 1  and class_ads.category_id='116' and class_ads.id !='124398' and class_slugs.`type`='listing'    limit 73715, 12;
# Time: 190903 23:07:31
# User@Host: i_one[i_one] @ localhost []  Id: 163672
# Schema: i_db  Last_errno: 0  Killed: 0
# Query_time: 1.224256  Lock_time: 0.000172  Rows_sent: 0  Rows_examined: 133667  Rows_affected: 0
# Bytes_sent: 8964
SET timestamp=1567541251;
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 < '2019-09-03 23:07:30' and class_ads.date_expires!='0000-00-00 00:00:00' and class_ads.active=0) as expired
		, class_slugs.slug 
		 
		from class_ads  use index (active_2) 
		   
		inner join class_slugs on class_ads.id=class_slugs.object_id
		 where class_ads.featured>0 and class_ads.active = 1 and `category_id` in (104) and class_slugs.`type`='listing';

php

function getFeatured($no_featured, $where = '') {

		
		global $db, $settings;

		$locations_str="";
		if($settings['enable_locations'])
			$locations_str = locations::makeQueryStr();

		$where="where ".TABLE_ADS.".featured>0 and ".TABLE_ADS.".active = 1".$where.$locations_str;

		// solution to avoid order by rand() 

		$total_featured = $db->fetchRow("select count(*) from ".TABLE_ADS." ".$where);

		if($total_featured<=$no_featured) {

			$result=$this->getShortListings($where,"","",0,0);
			shuffle ( $result );
			return $result;

		}

		// more than $no_featured results
		$t = $total_featured - $no_featured;
		$start = rand(0,$t);

		$result=$this->getShortListings($where,"","",$start,$no_featured);
		return $result;

	}

еще один запрос

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

		$sql = "select ".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;

Что можно сделать?

Comments

[FanatPHP]

выполнить запрос добавтв к нему слева слово EXPLAIN, прочесть вывод и расставить индексы чтобы убрать перебор/временные файлы

[viktorross]

FanatPHP, выполнил запрос так

EXPLAIN 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 <  '2019-09-03 23:30:04'
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 LIKE 1 
AND class_ads.category_id =  '116'
AND class_ads.id !=  '99178'
AND class_slugs.`type` =  'listing'
LIMIT 52054 , 12

получил 2 строки под +Параметры

(select_type)SIMPLE
(table)class_slugs
(type)ref
(possible_keys)idx_type,idx_object_id
(key)idx_type
(key_len)63
(ref)const
(rows)67741
(Extra)Using index condition; Using where

и

SIMPLE
class_ads
eq_ref
PRIMARY,idx_category,idx_active,active_2,active_3,...
PRIMARY
4
tix_db.class_slugs.object_id
1
Using where

что тут понять нужно?

Answer 1

[green_goo]

limit 73715, 12

С таким-то смещением не удивительно, что долго получается

Comments

[viktorross]

green_goo так а что можно сделать?

[PrAw]

viktorross, https://habr.com/ru/post/217521/ как вариант

[FanatPHP]

ой! Слона-то я и не приметил!

Answer 2

[Nick-M]

Explain select... пробовали? С индексами что там?

Comments

[viktorross]

Nick-M, такой запрос делал

EXPLAIN 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 <  '2019-09-03 23:30:04'
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 LIKE 1 
AND class_ads.category_id =  '116'
AND class_ads.id !=  '99178'
AND class_slugs.`type` =  'listing'
LIMIT 52054 , 12

результат

(select_type)SIMPLE
(table)class_slugs
(type)ref
(possible_keys)idx_type,idx_object_id
(key)idx_type
(key_len)63
(ref)const
(rows)67741
(Extra)Using index condition; Using where

и

SIMPLE
class_ads
eq_ref
PRIMARY,idx_category,idx_active,active_2,active_3,...
PRIMARY
4
tix_db.class_slugs.object_id
1
Using where

Answer 3

[Evgeniy Ivanov]

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