оптимизация запроса (не пугайтесь =)

[Николя223]

Есть сложный запрос, писал не я

CMS - UMICMS 

SELECT 					  MONTH(FROM_UNIXTIME(int_val)) AS 'm', YEAR(FROM_UNIXTIME(int_val)) AS 'y',					  COUNT(MONTH(FROM_UNIXTIME(int_val))) AS cnt 					FROM					  (SELECT DISTINCT 						h.id,						c2.int_val 					  FROM						 _hierarchy_relations hr,						 _objects o,						 _hierarchy h,						 _object_content c1,						 _object_content c2 					  WHERE h.obj_id = o.id 						AND h.is_deleted = '0' 						AND (						  c2.obj_id = o.id 						  AND c2.field_id = '{$i_field_id}'						{$sf}						) 						AND (						  (							hr.child_id = h.id 							AND (							  hr.rel_id = '$category_id' 							  AND hr.level <= '{$i_need_deep}'							)						  )						) 						AND h.is_active = '1' 						AND h.type_id IN ('{$hierarchy_type_id}') 						AND c1.obj_id = c2.obj_id 					  ORDER BY h.ord DESC) AS s 					GROUP BY MONTH(FROM_UNIXTIME(int_val)), YEAR(FROM_UNIXTIME(int_val))					ORDER BY YEAR(FROM_UNIXTIME(int_val)) DESC, MONTH(FROM_UNIXTIME(int_val)) DESC

проблема в том что юми - дура.

таблица _object_content  разраслась до феерических пределов 

И запрос работает через раз, через раз MYSQL go away 

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

Можно сделать временный костыль , но костыль на то и костыль что костыль)))

Edited June 16, 2014 by Николя223

[Veseloff]

А что explain говорит?

P. S. Как минимум нужно видеть реальный запрос, а не с переменными. Что с ходу видно. Непонятно для чего два раза подключается _object_content, далее непонятно зачем использутеся вложенный запрос. Вообще запрос странно построен — как минимум скобки как-то нездорово расставлены — один AND, ни одного OR, но тем не менее всё в скобках. Странный запрос.

[Николя223]

в личку отправил.

но что то не то. выдал что строк 65108 , как я понял это то в чём будет поиск проходить.

А в phpmyadmin - у этой таблицы ~7,253,047 рядов

Первый раз  использую explain - пока не до конца вкурил что к чему

 

Может быть завязка с общей нагрузкой на сервер? юми - тяжелая cms а пользователей много

 

 

Вообще запрос странно построен — как минимум скобки как-то нездорово расставлены

=)

Как мне объяснили - проэктировал модуль Юмипартнер....   ))) 

Не могу с точными значениями пример дать юми - самая сложная CMS которую встречал. только всю функцию в личку

Edited June 16, 2014 by Николя223

[Veseloff]

7 миллионов записей — это довольно много. Не то, чтобы прямо совсем капец как много, но не так уж и мало. А на чём всё это крутится: шаред, вдс, или сервер?

[Николя223]

7 миллионов записей — это довольно много. Не то, чтобы прямо совсем капец как много, но не так уж и мало. А на чём всё это крутится: шаред, вдс, или сервер?

это много - 1, 6 Гигабайт

Крутится как я понял на чем то подобии VDS - UMI host - платят 800 рублей за это фуфло. Оперативы 512 метров

[SelenIT]

MONTH(FROM_UNIXTIME(int_val))

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

 

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

SELECT (CASE		WHEN int_val BETWEEN 1388952001 AND 1389038400			THEN '2014-06'		WHEN int_val BETWEEN 1388865601 AND 1388952000			THEN '2014-05'		WHEN int_val BETWEEN 1388779201 AND 1388779200			THEN '2014-04'		WHEN int_val BETWEEN 1388692801 AND 1388692800			THEN '2014-03'		....		END CASE) as 'y-m',    					  COUNT(int_val) AS cnt 					FROM.... весь этот жуткий запрос ...					GROUP BY CASE.... весь жуткий CASE из начала ...		END CASE					ORDER BY CASE.... весь жуткий CASE из начала ...		END CASE DESC

но не проверял, так что не гарантирую.

[Николя223]

но не проверял, так что не гарантирую.

 

Здается мне я весь это бредовый запрос переписывать буду.... все это сделано ради...... календаря новостей.... 

я под столом....

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

юмипартнер.....

[Veseloff]

Ну на самом деле проблема, судя по всему, не только в запросе, а и ещё в изначальной архитектуре. Хранить даты в виде int — это уже клиника.

[Николя223]

сорри за трололо, но это не клиника.... клиника вот это      

 

		$hierarchy = umiHierarchy::getInstance();		//$hierarchy_type_id = umiHierarchyTypesCollection::getInstance()->getTypeByName("news", "item")->getId();		$hierarchy_type_id = 21231243; // тип новости				$months_ru = array('Январь', 'Февраль', 'Март ', 'Апрель', 'Май', 'Июнь', 'Июль', 'Август', 'Сентябрь', 'Октябрь ', 'Ноябрь', 'Декабрь');		$fields_filter = getRequest('fields_filter');		if (!isset($fields_filter['year'])) $fields_filter['year'] = getRequest('year');		list($template_block, $template_block_line, $template_block_empty) = def_module::loadTemplates("./tpls/filtered_pages/{$template}.tpl", 			"pages_block", "pages_block_line", "pages_block_empty");					$type_id = $hierarchy->getDominantTypeId($category_id, $i_need_deep, $hierarchy_type_id);		$o_object_type = umiObjectTypesCollection::getInstance()->getType($type_id);		if( $i_field_id = $o_object_type->getFieldId($fieldname)) {			$field = umiFieldsCollection::getInstance()->getField($i_field_id);			$sql =	"					SELECT 					  DISTINCT YEAR(FROM_UNIXTIME(int_val)) AS 'y'					FROM					  (SELECT DISTINCT 						h.id,						c2.int_val 					  FROM						 _hierarchy_relations hr,						 _objects o,						 _hierarchy h,						 _object_content c1,						 _object_content c2 					  WHERE h.obj_id = o.id 						AND h.is_deleted = '0' 						AND (						  c2.obj_id = o.id 						  AND c2.field_id = '{$i_field_id}'						) 						AND (						  (							hr.child_id = h.id 							AND (							  hr.rel_id = '48' 							  AND hr.level <= '{$i_need_deep}'							)						  )						) 						AND h.is_active = '1' 						AND h.type_id IN ('{$hierarchy_type_id}') 						AND c1.obj_id = c2.obj_id 					  ORDER BY h.ord DESC) AS s 					ORDER BY YEAR(FROM_UNIXTIME(int_val)) DESC					";			$res = mysql_query($sql);                                     			$years = array();			while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {				$years[] = def_module::parseTemplate($template_block_line, array('node:year' => $row['y']));			}						$block_arr = array();			$items = array();			$y_tec = 0;						if (isset($fields_filter['year']) and $fields_filter['year']) {				$d1 = strtotime('01-01-'.$fields_filter['year']);				$d2 = strtotime('31-12-'.$fields_filter['year'])+86400-10;				$sf = " AND c2.int_val> {$d1} AND c2.int_val < {$d2} ";							$sql =	"					SELECT 					  MONTH(FROM_UNIXTIME(int_val)) AS 'm', YEAR(FROM_UNIXTIME(int_val)) AS 'y',					  COUNT(MONTH(FROM_UNIXTIME(int_val))) AS cnt 					FROM					  (SELECT DISTINCT 						h.id,						c2.int_val 					  FROM						 _hierarchy_relations hr,						 _objects o,						 _hierarchy h,						 _object_content c1,						 _object_content c2 					  WHERE h.obj_id = o.id 						AND h.is_deleted = '0' 						AND (						  c2.obj_id = o.id 						  AND c2.field_id = '{$i_field_id}'						{$sf}						) 						AND (						  (							hr.child_id = h.id 							AND (							  hr.rel_id = '$category_id' 							  AND hr.level <= '{$i_need_deep}'							)						  )						) 						AND h.is_active = '1' 						AND h.type_id IN ('{$hierarchy_type_id}') 						AND c1.obj_id = c2.obj_id 					  ORDER BY h.ord DESC) AS s 					GROUP BY MONTH(FROM_UNIXTIME(int_val)), YEAR(FROM_UNIXTIME(int_val))					ORDER BY YEAR(FROM_UNIXTIME(int_val)) DESC, MONTH(FROM_UNIXTIME(int_val)) DESC					";				$res = mysql_query($sql);			 				while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {					$y = $row['y'];					$m = $row['m'];					$cnt = $row['cnt'];					//$item_id = $row['rel_val'];					$item_id = 0;					//$object=umiObjectsCollection::getInstance()->getObject($item_id);					/*if($object instanceof umiObject) 						$item_name = $object->getName();					else*/						$item_name = $y.' '.$m;						if ($y_tec != $y) {						$y_tec = $y;						$m_tec = 12;					}					while ($m> 0 and $m_tec > $m) {						$item_name2 = $y_tec.' '.$m_tec;						$cnt2 = 0;						$sel='';						if ($y_tec == date('Y') and $m_tec == date('m')) 							$sel='sel';							$d1 = strtotime('01.'.sprintf("%02d", $m_tec).'.'.sprintf("%04d", $y_tec));						($m_tec+1 > 12)? $d2 = strtotime('01.01.'.sprintf("%04d", ($y_tec+1))):$d2 = strtotime('01.'.sprintf("%02d", ($m_tec+1)).'.'.sprintf("%04d", $y_tec))-10;						$item = array(							'attribute:id'		=> $item_id,							'attribute:object_id'		=> $item_id,							'attribute:link'	=> "?fields_filter[publish_time][0]={$d1}&fields_filter[publish_time][1]={$d2}",							'attribute:d1'			=> $d1,							'attribute:d2'			=> $d2,							'attribute:fieldname'			=> $fieldname,							'attribute:y'			=> $y_tec,							'attribute:m'			=> $m_tec,							'attribute:mname'			=> $months_ru[$m_tec - 1],							'attribute:cnt'			=> $cnt2,							'node:name'			=> $item_name2,							'attribute:sel'		=> $sel						);						$items[] = def_module::parseTemplate($template_block_line, $item, $item_id);						$m_tec--;					}							$d1 = strtotime('01.'.sprintf("%02d", $m).'.'.sprintf("%04d", $y));					($m+1 > 12)? $d2 = strtotime('01.01.'.sprintf("%04d", ($y+1))):$d2 = strtotime('01.'.sprintf("%02d", ($m+1)).'.'.sprintf("%04d", $y))-10;						$sel='';					if (isset($fields_filter['publish_time']) and $d1 == $fields_filter['publish_time'][0] and $d2 == $fields_filter['publish_time'][1]) 						$sel='sel';						$item = array(						'attribute:id'		=> $item_id,						'attribute:object_id'		=> $item_id,						'attribute:link'	=> "?fields_filter[publish_time][0]={$d1}&fields_filter[publish_time][1]={$d2}",						'attribute:d1'			=> $d1,						'attribute:d2'			=> $d2,						'attribute:fieldname'			=> $fieldname,						'attribute:y'			=> $y,						'attribute:m'			=> $m,						'attribute:mname'			=> $months_ru[$m - 1],						'attribute:cnt'			=> $cnt,						'node:name'			=> $item_name,						'attribute:sel'		=> $sel					);					$items[] = def_module::parseTemplate($template_block_line, $item, $item_id);					$m_tec--;				}			}						if(count($items) > 0) {				$block_arr['subnodes:items'] = $items;				$block_arr['subnodes:years'] = $years;				$template = $template_block;			}else{				$block_arr['subnodes:years'] = $years;				$template = $template_block_empty;			}			return def_module::parseTemplate($template, $block_arr);		}

Edited June 16, 2014 by Николя223