Оптимизация простого запроса

[LONGMAN]

Оптимизация простого запроса

Как можно оптимизировать такой запрос:
[sql]
SELECT `a` . *
FROM `content` AS `a`
INNER JOIN `categories` AS `c` ON `a`.`catid` = `c`.`id`
INNER JOIN `sections` AS `s` ON `a`.`sectionid` = `s`.`id`
WHERE `a`.`state` = 1 AND (
`a`.`publish_up` = '0000-00-00 00:00:00' OR `a`.`publish_up` <= '2010-10-04 16:07:53'
) AND (
`a`.`publish_down` = '0000-00-00 00:00:00' OR `a`.`publish_down` >= '2010-10-04 16:07:53'
)
ORDER BY `a`.`publish_up` DESC
LIMIT 6
[/sql]
Explain:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	a 	ref 	idx_section,idx_state,idx_catid,state 	idx_state 	1 	const 	10034 	Using where; Using temporary; Using filesort
1 	SIMPLE 	s 	index 	PRIMARY 	PRIMARY 	4 	NULL 	2 	Using where; Using index
1 	SIMPLE 	c 	eq_ref 	PRIMARY 	PRIMARY 	4 	web1db1.a.catid 	1 	Using where; Using index

Запрос выполняется 0.5 sec. Записи в таблице content 55000. Моя цель вывести 6 последних статьи. Есть индексы на catid, sectionid, state и один общий на state, publish_up, publish_down

 

[zerkms]

схема базы где?

 

[LONGMAN]

Вот
[sql]
CREATE TABLE `content` (
`id` int(11) unsigned NOT NULL auto_increment,
`title` varchar(255) NOT NULL default '',
`alias` varchar(255) NOT NULL default '',
`title_alias` varchar(255) NOT NULL default '',
`introtext` mediumtext NOT NULL,
`fulltext` mediumtext NOT NULL,
`state` tinyint(3) NOT NULL default '0',
`sectionid` int(11) unsigned NOT NULL default '0',
`mask` int(11) unsigned NOT NULL default '0',
`catid` int(11) unsigned NOT NULL default '0',
`created` datetime NOT NULL default '0000-00-00 00:00:00',
`created_by` int(11) unsigned NOT NULL default '0',
`created_by_alias` varchar(255) NOT NULL default '',
`modified` datetime NOT NULL default '0000-00-00 00:00:00',
`modified_by` int(11) unsigned NOT NULL default '0',
`checked_out` int(11) unsigned NOT NULL default '0',
`checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00',
`publish_up` datetime NOT NULL default '0000-00-00 00:00:00',
`publish_down` datetime NOT NULL default '0000-00-00 00:00:00',
`images` text NOT NULL,
`urls` text NOT NULL,
`attribs` text NOT NULL,
`version` int(11) unsigned NOT NULL default '1',
`parentid` int(11) unsigned NOT NULL default '0',
`ordering` int(11) NOT NULL default '0',
`metakey` text NOT NULL,
`metadesc` text NOT NULL,
`access` int(11) unsigned NOT NULL default '0',
`hits` int(11) unsigned NOT NULL default '0',
`metadata` text NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_section` (`sectionid`),
KEY `idx_access` (`access`),
KEY `idx_checkout` (`checked_out`),
KEY `idx_state` (`state`),
KEY `idx_catid` (`catid`),
KEY `idx_createdby` (`created_by`),
KEY `state` (`state`,`publish_up`,`publish_down`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=146555 ;
[/sql]

 

[Активист]

LONGMAN
Храни в отдельной таблице (кеше, файле и т.п.) 6 последних статей сбрасывай кеш при появлении новой статьи, какой смысл каждый раз дергать, сортировать, лимитировать.

-~{}~ 05.10.10 14:58:

[не заметил индекс сразу]

 

[LONGMAN]

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

 

[Активист]

Кстати,

`a`.`publish_up` = '0000-00-00 00:00:00' OR `a`.`publish_up` <= '2010-10-04 16:07:53'

Как-то странновато, что за нули. Убрал бы эти нули, а то индекс state не используется

WHERE `a`.`state` = 1 && `a`.`publish_up` <= '2010-10-04 16:07:53'
&&`a`.`publish_down` >= '2010-10-04 16:07:53'

И касаемо структуры БД, что значит publish_up, publish_down, тут можно по одному полю сравнивать, зачем по два не понятно.

И на `publish_up` нужен индекс, потому как на него идет order.

-~{}~ 05.10.10 15:07:

LONGMAN
И что, что 5 минут, за 5 минут может быть овер 9000 реквистов на хост.

-~{}~ 05.10.10 15:14:

Кстати, последние по дате или последние по добавлению? Если последнее - то можно и primary key использвать, т.е. ID, а не сравнивать даты.

-~{}~ 05.10.10 15:15:

И еще, либо ты не весь SQL показал, либо INNER JOIN не нежен.

 

[LONGMAN]

Активист
publish_up и publish_down это начало и конец опубликования статьи. если publish_down '0000-00-00 00:00:00' то значит время публикации не истекает.

И на `publish_up` нужен индекс, потому как на него идет order.

попробую

И еще, либо ты не весь SQL показал, либо INNER JOIN не нежен.

сократил SELECT

 

[Активист]

1. Избавься от 0000-00-00 в запросе, используй скажем `state`.

 

[zerkms]

1. Избавься от 0000-00-00 в запросе, используй скажем `state`.

я бы тогда предложил избавиться от 0000 в пользу 2999, например. тогда условие будет банальным BETWEEN

 

[LONGMAN]

Переписал запрос так
[sql]
SELECT `a`. *
FROM `content` AS `a`
INNER JOIN `categories` AS `c` ON `a`.`catid` = `c`.`id`
INNER JOIN `sections` AS `s` ON `a`.`sectionid` = `s`.`id`
WHERE `a`.`state` = 1 AND `a`.`publish_up` <= '2010-10-04 16:07:53'
AND `a`.`publish_down` >= '2010-10-04 16:07:53'
ORDER BY `a`.`id` DESC
LIMIT 6
[/sql]
но вроде ничего не изменилось

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	a 	ref 	idx_section,idx_state,idx_catid,state,publish_up 	idx_state 	1 	const 	9970 	Using where; Using temporary; Using filesort
1 	SIMPLE 	c 	eq_ref 	PRIMARY 	PRIMARY 	4 	web1db1.a.catid 	1 	Using where; Using index
1 	SIMPLE 	s 	index 	PRIMARY 	PRIMARY 	4 	NULL 	2 	Using where; Using index

 

[zerkms]

если попробовать индекс state+id и после этого, на всякий случай, сделать ANALYZE + OPTIMIZE лучше не станет ли?

 

[LONGMAN]

state+id или state+publish_up+publish_down+id?

 

[Вурдалак]

От state+publish_up+publish_down+id лучше точно не станет.

 

[LONGMAN]

Добавил на state+id

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	a 	ref 	idx_section,idx_state,idx_catid,state,publish_up,idx_state_id 	idx_state_id 	1 	const 	1838 	Using where; Using temporary; Using filesort
1 	SIMPLE 	c 	eq_ref 	PRIMARY 	PRIMARY 	4 	web1db1.a.catid 	1 	Using where; Using index
1 	SIMPLE 	s 	index 	PRIMARY 	PRIMARY 	4 	NULL 	2 	Using where; Using index

 

[zerkms]

LONGMAN
Ну и? Быстро выполняться не стало?

 

[LONGMAN]

zerkms
да, время снизился до 0.1398
но видимо все равно придется менять что то в архитектуре

 

[Вурдалак]

Да просто в кеш скидывай, как уже сказали.

 

[iceman]

LONGMAN

SELECT x.* FROM (
SELECT `a`. *
FROM `content` AS `a`
WHERE `a`.`state` = 1 AND `a`.`publish_up` <= '2010-10-04 16:07:53' AND `a`.`publish_down` >= '2010-10-04 16:07:53'
ORDER BY `a`.`id` DESC
LIMIT 6 ) as x
INNER JOIN `categories` AS `c` ON `x`.`catid` = `c`.`id`
INNER JOIN `sections` AS `s` ON `x`.`sectionid` = `s`.`id`

а вот так? %)

 

[LONGMAN]

iceman
Не поверишь, 2.1 sec.
И explain очень странный

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	NULL 	NULL 	NULL 	NULL 	NULL 	NULL 	NULL 	Impossible WHERE noticed after reading const tables
2 	DERIVED 	a 	ref 	idx_state,state,publish_up,idx_state_id 	idx_state_id 	1 	  	1840 	Using where

 

[iceman]

ясно =)