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

[piton]

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

Запрос выборки популярных "эмоций" за неделю, струкрута таблиц ниже, запрос выполняется 2-3 секунды на двухпроцессорном зеоне с 2 гигами памяти.

таблица содержит текущий вес эмоции и ее наименование (10 записей)
[sql]
CREATE TABLE `blogs_reting_vid` (
`id` mediumint(8) NOT NULL auto_increment,
`name` varchar(20) NOT NULL default '',
`znak` double(6,2) NOT NULL default '0.00',
`pub` mediumint(1) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
[/sql]

таблица содержит список проголосовавших (~12000 записей)
[sql]
CREATE TABLE `blogs_reting` (
`id` mediumint(8) NOT NULL auto_increment,
`post_id` mediumint(8) NOT NULL default '0',
`who` mediumint(8) NOT NULL default '0',
`golos` mediumint(8) NOT NULL default '0',
`ves` double(6,2) NOT NULL default '0.00',
`datetime` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `post_id` (`post_id`)
) ENGINE=MyISAM;
[/sql]

таблица постов (~10000 записец)
[sql]
CREATE TABLE `blogs` (
`id` mediumint(8) NOT NULL auto_increment,
`user_id` mediumint(8) NOT NULL default '0',
`who` mediumint(8) NOT NULL default '0',
`datetime` datetime NOT NULL default '0000-00-00 00:00:00',
... `mp3` smallint(1) NOT NULL default '0',
`video` smallint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `deleted` (`deleted`),
KEY `user_id_2` (`user_id`,`deleted`),
KEY `news` (`news`),
KEY `userpic` (`userpic`),
KEY `pub` (`pub`,`news`),
KEY `deleted_2` (`deleted`,`news`)
) ENGINE=MyISAM;
[/sql]

Сам запрос:
[sql]
SELECT t1.id, t1.name, count(t2.id) AS cnt
FROM `blogs_reting_vid` AS t1, blogs AS t3
LEFT JOIN `blogs_reting` AS t2 ON t2.golos = t1.id
WHERE t2.post_id = t3.id
AND DATE_ADD( NOW( ) , INTERVAL -7 DAY ) < t3.datetime
AND NOW( ) > t3.datetime
GROUP BY t1.id
ORDER BY cnt DESC;
[/sql]

EXPLAIN говорит

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra 
1	SIMPLE	t2	ALL	post_id	NULL	NULL	NULL	7323	Using temporary; Using filesort
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	3	blog.t2.post_id	1	Using where
1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	3	blog.t2.golos	1

 

[Alexandre]

попробуй заменить `datetime` datetime NOT NULL default '0000-00-00 00:00:00' на int, храни время в Юникстайм.
соответственно, часть запроса упростится, вместо функции работы с интервалом используй конкретное число (7 дней = 7 х 24 х 3600 ) Будет что-то типа datetime BETWEEN <нижн граница> AND <верх граница>

нижн граница и верх граница конкретные числа в юникстайм.

T2.golos - нет индекса, а по этому полю идет объединение таблиц

 

[piton]

1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	14	Using temporary; Using filesort
1	SIMPLE	t2	ref	post_id,golos	golos	3	blog.t1.id	523	Using where
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	3	blog.t2.post_id	1	Using where

Хм, вроде стало получше + в memcache запихал массив до кучи

Вопрос по datetime, везде обычно стараюсь использовать его, т.е. получается зря? Лучше использовать unixtime и не заморачиваться?

-~{}~ 02.08.07 16:21:

Опять-же в WHERE имеет значение порядок условий?
Т.е.
WHERE a=1 AND b=3 AND c!=2 аналогично
WHERE b=2 AND c!=2 AND a=1 ?

 

[MajestiC]

Лучше конечно datetime, но если не страшны ограничения unix timestamp (1970-2038 помоему), то все на ваш выбор -)

AND DATE_ADD( NOW( ) , INTERVAL - 7
DAY ) < t3.datetime AND NOW( ) > t3.datetime

Это можно заменить конкретными значениями, которые предварительно посчитать в php.

 

[piton]

MajestiC
Т.е. INTERVAL медленнее чем цифры полученые с php?

и в моем случае полезно избавиться от NTERVAL? я думал что он такой-же быстрый как phpшные функции.

p.s. забыл указать версию mysql& php, mysql 4.1.11a, php 4.3.10

 

[MajestiC]

DATE_ADD, NOW - функции, а если ты напрямую значения подсчитываешь то MySQL делает обычный поиск по индексам.
В твоем - полезно. И NOW заменить тоже.

 

[piton]

делает обычный поиск по индексам.

т.е. t3.datetime сделать int полем, запихать данные в unixtime и сделать поле индексом?

 

[MajestiC]

Не обязательно, кто тебе мешает сделать:

AND '2007-01-03 01:01:01' < t3.datetime AND '2007-03-03 01:03:01' > t3.datetime

 

[piton]

тип DATETIME дефолтово является индексом или его нужно делать?

 

[MajestiC]

У типов нет индексов. Индексы создаешь ты сам.

 

[piton]

MajestiC

Ок, перефразирую, для полей datetime если я использую констукции WHERE с ними имеет смысл делать поле индексом?

 

[Alexandre]

T2.golos - нет индекса, а по этому полю идет объединение таблиц

это сделал??

-~{}~ 02.08.07 18:26:

AND '2007-01-03 01:01:01' < t3.datetime AND '2007-03-03 01:03:01' > t3.datetime

быстрее будет
t3.datetime BETWEEN '2007-01-03 01:01:01' AND '2007-03-03 01:03:01'
а еще быстрее тоже самое в интегер.

 

[alpine]

piton
Попробуй еще добавить индекс для поля `datetime` в таблице `blogs`.

 

[MajestiC]

Alexandre
(про BETWEEN) Никак не оспариваю, но интересно, откуда такая информация?

а еще быстрее тоже самое в интегер.

Тоже интересно, я просто не знаю как datetime лежат в mysql (насколько я помню 8 байт), думал всегда что какой-нить bigint и сравнивает тоже через числа, что впринципе односильно при использовании 64 битного проца.

-~{}~ 02.08.07 19:20:

piton
Да, имеет. То что находится в where или order by, в большинстве случаев индес помогает. explain в помощь.

 

[alpine]

Alexandre

а еще быстрее тоже самое в интегер.

Мне тоже очень бы хотелось увидеть тесты показывающие эту разницу.

-~{}~ 02.08.07 18:32:

Тоже интересно, я просто не знаю как datetime лежат в mysql (насколько я помню 8 байт), думал всегда что какой-нить bigint и сравнивает тоже через числа, что впринципе односильно при использовании 64 битного проца.

Можно кстати хранить данные не в формате DATETIME(8байт), а в формате TIMESTAMP(4байта). INT - 4 байта

-~{}~ 02.08.07 18:37:

По поводу BETWEEN Alexandre привел не совсем эквивалентные конструкции.

This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. (C) manual

 

[MajestiC]

Можно кстати хранить данные не в формате DATETIME(8байт), а в формате TIMESTAMP(4байта). INT - 4 байта

Об этом я писал:

Лучше конечно datetime, но если не страшны ограничения unix timestamp (1970-2038 помоему), то все на ваш выбор -)

о поводу BETWEEN Alexandre привел не совсем эквивалентные конструкции.

Вот я про то же.

 

[Wicked]

тру пацаны хранят даты - как даты.

Тоже интересно, я просто не знаю как datetime лежат в mysql (насколько я помню 8 байт), думал всегда что какой-нить bigint и сравнивает тоже через числа,

DATETIME 8 bytes (с) manual

 

[MajestiC]

Wicked
Ну дык я по памяти помню, лень было в мануал лезть, конец рабочего дня, расслабляемся =)

 

[fixxxer]

охренеть тут советчики собрались

 

[Gas]

1) DATE_ADD( NOW( ) , INTERVAL - 7 DAY ) и NOW() вычисляется 1 раз, не имеет смысла в php считать;
2) про индексы на golos и datetime тебе сказали;
3) datetime vs int, обычно пох., не думаю что тут прям такая ситуация, когда индексный файл из-за лишних 4-байт раздувается и существенно отжирает key_buffer;
4) Using filesort - не уберёшь, так-как сортировка по вычисляемому значению. Но по идее сортируется результат из 10 записей и это натуплять не должно (не должно ли?)
5) c Using temporary в данном запросе тоже не факт что получится справится. Влазит выбока в tmp_table_size или счётчик сreated_tmp_disk_tables увеливается?