Нужна помощь в оптимизации простенького запроса

[Toxic_Cat]

Нужна помощь в оптимизации простенького запроса

Сам запрос делает следующее: есть категория, он выводит последние новости с одновременным подсчетом комментариев к новостям.

SELECT *, COUNT(ment_id) as ment_count 
FROM pages 
LEFT JOIN mentions ON page_id = ment_pageid 
WHERE page_secid = 70 
GROUP BY page_id 
ORDER BY page_date DESC 
LIMIT 0, 15

MENTIONS
ment_id = id комментария
ment_pageid = id страницы к которой привязан комментарий

PAGES
page_id = id страницы
page_secid = id раздела страницы
page_date = дата изменения страницы

Но вот не задача. Запрос выполняется аж 0.2 секунды (на локалхосте). На сервере и того больше, аж 0.4 секунды.

С виду проблема в одном: GROUP BY page_id ORDER BY page_date DESC, если заменить на GROUP BY page_date ORDER BY page_date DESC то генерация сокращается аж до 0.01 секунды! Только это решение не красивое, так как у страниц могут совпадать даты и тогда при выводе страницы с одинаковыми датами сгруппируются. Вариант с GROUP BY page_id ORDER BY page_id DESC то же не подходит в виду того, что идентификатор не отвечает за "свежесть" новости.

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

 

[Wicked]

сначала выбирай страницы без мнений, а мнения потом отдельным запросом:
select ment_pageid, count(*) from mentions where ment_pageid in (598, 4844, 843, ...) group by ment_pageid;

 

[prolis]

Покажи план запроса:
[sql]
select pages.*, t.cnt from pages,
(select ment_pageid,count(*) as cnt from mentions
group by ment_pageid
) t
WHERE page_secid = 70
and t.ment_pageid=pages.page_id
ORDER BY page_date DESC
LIMIT 0, 15
[/sql]

 

[Toxic_Cat]

Wicked

Сильно помогает с ускорением запросов (до 0.01-0.03 секунд). Но вот движок переписывать... тоже не выход. Там слишком все завязано именно на таких вот спаренных запросах, с кэшированием.

prolis

Твой запрос выполняется быстрее в два раза, но все-равно уходит аж 0.1 секунда.

EXPLAIN выдает вот такое:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	<derived2> 	ALL  	NULL  	NULL  	NULL  	NULL 	767 	Using temporary; Using filesort
1 	PRIMARY 	white_pages 	eq_ref 	PRIMARY,page_id_3,i_page_secid,i_page_id,page_id,p... 	PRIMARY 	4 	t.ment_pageid 	1 	Using where
2 	DERIVED 	white_mentions 	index 	NULL 	i_ment_pageid 	4 	NULL 	19511 	Using index

P.S. сорри за отсутствие форматирования таблицы, не знаю как её правильно оформить в BB-code.

 

[Wicked]

SELECT p.*, COUNT(ment_id) as ment_count
FROM (select * from pages WHERE page_secid = 70 ORDER BY page_date DESC LIMIT 0, 15) as p
LEFT JOIN mentions ON p.page_id = ment_pageid
GROUP BY p.page_id

как-то так

explain не забудь показать

 

[Mr_Max]

show create так-же не помешает.

 

[Wicked]

в моем случае нужен составной индекс по (page_secid, page_date)

 

[Toxic_Cat]

Wicked

Убрал некоторые индексы, оставил только два.

В общем наткнулся на странное совпадение, если удалить все TEXT поля то запрос

SELECT p.*, COUNT(ment_id) as ment_count
FROM (select * from pages WHERE page_secid = 70 ORDER BY page_date DESC LIMIT 0, 15) as p
LEFT JOIN mentions ON p.page_id = ment_pageid
GROUP BY p.page_id

выполняется 0.01 - 0.03 вместо стандартных 0.13 - 0.15

EXPLAIN стандартной таблицы (as is):

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	<derived2> 	ALL 	NULL 	NULL 	NULL 	NULL 	15 	Using temporary; Using filesort
1 	PRIMARY 	mentions 	ref 	ment_pageid 	ment_pageid 	4 	p.page_id 	238 	 
2 	DERIVED 	pages 	ref 	page_secid_page_date 	page_secid_page_date 	4 	  	784 	Using where

EXPLAIN после удаления полей типа TEXT:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	<derived2> 	ALL 	NULL 	NULL 	NULL 	NULL 	15 	 
1 	PRIMARY 	mentions 	ref 	ment_pageid 	ment_pageid 	4 	p.page_id 	238 	 
2 	DERIVED 	pages 	ref 	page_secid_page_date 	page_secid_page_date 	4 	  	795 	Using where

удалены были: page_img, page_string1, page_string2, page_smalltext, page_text, page_set, page_advanced6, page_advanced7

Mr_Max

CREATE TABLE `pages` (
 `page_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `page_siteid` int(11) NOT NULL DEFAULT '0',
 `page_secid` int(11) NOT NULL DEFAULT '0',
 `page_catid` int(11) NOT NULL DEFAULT '0',
 `page_userid` int(11) NOT NULL DEFAULT '0',
 `page_alias` varchar(100) NOT NULL DEFAULT '',
 `page_head` varchar(255) NOT NULL DEFAULT '',
 `page_title` varchar(255) NOT NULL DEFAULT '',
 `page_desc` varchar(255) NOT NULL DEFAULT '',
 `page_h1` varchar(255) NOT NULL DEFAULT '',
 `page_date` int(11) NOT NULL DEFAULT '0',
 `page_mdate` int(11) NOT NULL DEFAULT '0',
 `page_img` text NOT NULL,
 `page_string1` text NOT NULL,
 `page_string2` text NOT NULL,
 `page_advanced1` varchar(255) NOT NULL DEFAULT '',
 `page_advanced2` varchar(255) NOT NULL DEFAULT '',
 `page_advanced3` varchar(255) NOT NULL DEFAULT '',
 `page_advanced4` varchar(255) NOT NULL DEFAULT '',
 `page_advanced5` varchar(255) NOT NULL DEFAULT '',
 `page_alpha` varchar(255) NOT NULL DEFAULT '',
 `page_redirect` varchar(255) NOT NULL DEFAULT '',
 `page_visits` int(11) NOT NULL DEFAULT '0',
 `page_status` int(2) NOT NULL DEFAULT '0',
 `page_score` int(11) NOT NULL DEFAULT '0',
 `page_votes` int(11) NOT NULL DEFAULT '0',
 `page_total` int(11) NOT NULL DEFAULT '0',
 `page_menttype` int(2) NOT NULL DEFAULT '0',
 `page_permission` int(2) NOT NULL DEFAULT '0',
 `page_smalltext` text NOT NULL,
 `page_text` text NOT NULL,
 `page_set` text NOT NULL,
 `page_advanced6` text NOT NULL,
 `page_advanced7` text NOT NULL,
 PRIMARY KEY (`page_id`),
 KEY `page_secid_page_date` (`page_secid`,`page_date`)
) ENGINE=MyISAM AUTO_INCREMENT=2379 DEFAULT CHARSET=utf8

CREATE TABLE `mentions` (
 `ment_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `ment_siteid` int(11) NOT NULL DEFAULT '0',
 `ment_secid` int(11) NOT NULL DEFAULT '0',
 `ment_catid` int(11) NOT NULL DEFAULT '0',
 `ment_pageid` int(11) NOT NULL DEFAULT '0',
 `ment_userid` int(11) NOT NULL DEFAULT '0',
 `ment_date` int(11) NOT NULL DEFAULT '0',
 `ment_ip` int(11) NOT NULL DEFAULT '0',
 `ment_score` int(11) NOT NULL DEFAULT '0',
 `ment_votes` int(11) NOT NULL DEFAULT '0',
 `ment_name` varchar(64) NOT NULL DEFAULT '',
 `ment_email` varchar(64) NOT NULL DEFAULT '',
 `ment_site` varchar(200) NOT NULL DEFAULT '',
 `ment_text` text NOT NULL,
 `ment_status1` int(1) NOT NULL DEFAULT '0',
 `ment_status2` int(11) NOT NULL DEFAULT '0',
 `ment_subscribe` tinyint(1) NOT NULL DEFAULT '0',
 `ment_set` text NOT NULL,
 PRIMARY KEY (`ment_id`),
 KEY `ment_pageid` (`ment_pageid`)
) ENGINE=MyISAM AUTO_INCREMENT=21192 DEFAULT CHARSET=utf8

Не знаю даже что и подумать. На локал хостинге и на виртуале все аналогично.

 

[Alexandre]

P.S. сорри за отсутствие форматирования таблицы, не знаю как её правильно оформить в BB-code.

попробуй сипользовать BB:[ code ]

попробуй сделать два запроса, и склеить их на клиенте. Может быть будет быстрее.

ну а если нужна действительно скорость, то данные надо подготавливать по крону в отдельную таблицу и выбирать простым селектом, тогда будет 0,1 -0,4 мс. Не понимаю, чего место жалеть, оно сравнительно дешево.

 

[Wicked]

если удалить все TEXT поля то запрос выполняется 0.01 - 0.03 вместо стандартных 0.13 - 0.15

наличие полей типа TEXT влечет за собой filesort, который гарантированно делается на диске. Без таких полей при небольших объемах данных filesort чаще делается в памяти.
Ну и имеет место некоторый оверхед есть на возврат большего объема данных.

 

[Toxic_Cat]

Alexandre
Места мне не жалко. Надо будет на досуге подумать, какие данные стоит кэшировать. База то всего 24 метра, а хостер уже пишет, что я нагрузку большую даю.

Wicked
Все таки чтобы быстро исправить положение я пойду обходным путем и порежу запросы на более мелкие. Т.е. комментарии буду выбирать во втором, полноценном запросе. Потом все это сводиться с помощью PHP в циклах.

Очередные костыли

 

[prolis]

В данном подзапросе не используются индексы:
[sql]
select * from pages WHERE page_secid = 70 ORDER BY page_date DESC LIMIT 0, 15
[/sql]

PRIMARY     <derived2>     ALL     NULL     NULL     NULL     NULL     15     Using temporary; Using filesort

- попробуй "пнуть" оптимизатор, дабы он начал использовать индекс "KEY `page_secid_page_date` (`page_secid`,`page_date`)"
например:
[sql]
select * from pages WHERE page_secid = 70 and page_date<now() ORDER BY page_date DESC LIMIT 0, 15
[/sql]

 

[Toxic_Cat]

В общем сегодня пару часов я посвятил оптимизации движка. И понял еще пару интересных вещей...

Например есть страница с 9000 комментариев (ID 2276).
Делаем запрос на их количество, плюс данные одного коммента (последнего, например).

SELECT *, COUNT(*) as ment_count 
FROM mentions 
WHERE ment_pageid = 2276

Выполняется аж 0.07 сек.

Пришлось пилить на:

SELECT * 
FROM mentions 
WHERE ment_pageid = 2276

0.0007 сек.
и

SELECT COUNT(*) as ment_count 
FROM mentions 
WHERE ment_pageid = 2276

0.003 сек.

Итого - огромный выигрыш в скорости генерации: 0.06 сек.


Второе, как и посоветовали выше, я разобрал сложные запросы:

SELECT *, COUNT(ment_id) as ment_count 
FROM pages 
LEFT JOIN mentions ON page_id = ment_pageid 
WHERE page_secid = 70 
GROUP BY page_id 
ORDER BY page_date DESC 
LIMIT 0, 15

0.2 сек.

на три:

SELECT *
FROM pages
WHERE page_secid = 70
ORDER BY page_date DESC 
LIMIT 0, 15

0.01 сек.

SELECT *
FROM mentions 
WHERE ment_pageid IN (70, 90, 30, ...)

0.0058

SELECT COUNT(*) as ment_count
FROM mentions 
WHERE page_secid IN (70, 90, 30, ...)

0.0011 сек.

Итого - огромный выигрыш в скорости генерации: 0.18 сек.

Вывод
Не юзать разные поля в GROUP BY и ORDER BY (так как индексы перестают использоваться, это написано в мануале mysql)
Комбинация *, COUNT(*) - плоха, лучше разбивать на два запроса.

Возможно у вас будет по другому.

Хотя у меня не такая уж и большая база, на самом деле:
комментариев: 20,992
страниц: 1,301

И то так сильно тормозила выборка. Что было бы при большей базе - вообще думать страшно.

prolis
Выигрышь в производительности есть, но все-равно запрос выполняется десятые доли секунды.

 

[Wicked]

SELECT *
FROM mentions
WHERE ment_pageid IN (70, 90, 30, ...)

зачем?

 

[Toxic_Cat]

Wicked
Там еще GROUP BY ORDER BY есть. Просто я вывожу для наглядности последний комментарий новости. Ведь интересно, что там люди понаписали
Правда это еще не включено, но в планах.

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

В общем работы много.