Оптимизация запросов, выборка новых клиентов.

[p0is0n]

Оптимизация запросов, выборка новых клиентов.

Доброе время суток, столкнулся с такой проблемой.

Есть таблица:

CREATE TABLE  `stat` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `bot` varchar(50) NOT NULL,
  `whos` varchar(50) NOT NULL,
  `type` enum('icq','jabber','mrim') NOT NULL,
  `cmd` varchar(20) default NULL,
  `message` text NOT NULL,
  `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `cmd_idx` (`cmd`),
  KEY `whs_idx` (`whos`),
  KEY `tim_idx` (`time`),
  KEY `wts_idx` (`time`,`whos`)
) ENGINE=MyISAM;

Записей примерно 10 000 000 обновляется каждую секунду (просто инсерты)

Мне нужно с этой таблицы выбрать `whos` за последнюю минуту, но которых небыло раньше) тоесть новых за последнюю минуту.

Есть два варианта запросов:

SELECT COUNT(*) FROM (SELECT
DISTINCT `whos`
FROM `stat` WHERE `time` >= NOW() - INTERVAL 1 MINUTE) `a`
LEFT JOIN `stat` `b` ON `b`.`whos` = `a`.`whos` AND `b`.`time` <= NOW() - INTERVAL 1 MINUTE WHERE `b`.`whos` IS NULL

план:

+----+-------------+------------+-------+-------------------------+---------+---------+--------+-------+-------------------------------------------+
| id | select_type | table      | type  | possible_keys           | key     | key_len | ref    | rows  | Extra                                     |
+----+-------------+------------+-------+-------------------------+---------+---------+--------+-------+-------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL                    | NULL    | NULL    | NULL   | 56974 |                                           |
|  1 | PRIMARY     | b          | ref   | whs_idx,tim_idx,wts_idx | whs_idx | 152     | a.whos |     1 | Using where; Not exists                   |
|  2 | DERIVED     | stat       | range | tim_idx,wts_idx         | wts_idx | 4       | NULL   | 74338 | Using where; Using index; Using temporary |
+----+-------------+------------+-------+-------------------------+---------+---------+--------+-------+-------------------------------------------+

SELECT
	COUNT(DISTINCT `a`.`whos`)
FROM `stat` `a` WHERE `a`.`time` >= NOW() - INTERVAL 1 MINUTE AND (
	SELECT
		`id`
	FROM `stat` WHERE `time` < NOW() - INTERVAL 1 MINUTE AND `whos` = `a`.`whos` LIMIT 1
) IS NULL

план:

+----+--------------------+-------+-------+-------------------------+---------+---------+--------------+-------+--------------------------+
| id | select_type        | table | type  | possible_keys           | key     | key_len | ref          | rows  | Extra                    |
+----+--------------------+-------+-------+-------------------------+---------+---------+--------------+-------+--------------------------+
|  1 | PRIMARY            | a     | range | tim_idx,wts_idx         | wts_idx | 4       | NULL         | 79403 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | stat  | ref   | whs_idx,tim_idx,wts_idx | whs_idx | 152     | arx_1.a.whos |     1 | Using where              |
+----+--------------------+-------+-------+-------------------------+---------+---------+--------------+-------+--------------------------+

в среднем оба запроса выполняются ~3 секунд. Возможно ли что то сделать? 3 секунды это критично...

 

[prolis]

можно и так:
[sql]
SELECT count(DISTINCT a.whos) FROM stat a
WHERE
a.time >= NOW() - INTERVAL 1 MINUTE
and NOT EXISTS
(select * from stat b where b.whos=a.whos and b.time< NOW() - INTERVAL 1 MINUTE )
[/sql]
но лучше выделять уников в отдельные сущности

 

[Alexandre]

Возможно ли что то сделать? 3 секунды это критично...

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

 

[p0is0n]

Alexandre, это сбор статистики, какие тут предварительные расчеты?

prolis, этот запрос почти так же. Насчет выделить думаю было бы хорошо, но не хотелось бы делать еще какие то операции при добавлении записей, есть только инсерты. Как можно проше выделить? может есть что нибудь полезное

 

[zerkms]

1. запрос примерно такой:

[sql]
SELECT COUNT(DISTINCT `whos`)
FROM `stat` `a`
LEFT JOIN `stat` `b` ON `a`.`whos` = `b`.`whos` AND `b`.`time` < NOW() - INTERVAL 1 MINUTE
WHERE `a`.`time` >= NOW() - INTERVAL 1 MINUTE AND `b`.`whos` IS NULL
[/sql]

2. индексы:
KEY `whs_idx` (`whos`), заменить на whos+time
KEY `tim_idx` (`time`) удалить

 

[Alexandre]

это сбор статистики, какие тут предварительные расчеты?

а при показе статистики разве не могут быть подготовленных вычислений?
или ты этот запрос не в реал-тайм отображаешь?

 

[prolis]

Автор оригинала: p0is0n
Как можно проше выделить? может есть что нибудь полезное

шаг 1: Создаем таблицу сущности
[sql]
create table stat_whos as
select max(id) as id, whos from stat group by whos
[/sql]
строим составной праймари
шаг 2: запрос на обновление stat_whos на основе stat(ид больше максимального, время старше минуты и whos ещё нет в whose stat)
шаг 3: непосредственный запрос
по замыслу шаги 2 и 3 будут выполняться приемлимое время (менее полсекунды)

 

[SiZE]

А как такой вариант - записать ID последнего запрошенного в файл?

 

[prolis]

Автор оригинала: SiZE
А как такой вариант - записать ID последнего запрошенного в файл?

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

 

[С.]

[sql]
SELECT `whos`, COUNT(*) FROM `stat`
WHERE `time` >= NOW( ) - INTERVAL 1 MINUTE
GROUP by `whos`
[/sql]
...и отфильтровать выдачу.

 

[p0is0n]

Автор оригинала: zerkms
1. запрос примерно такой:

[sql]
SELECT COUNT(DISTINCT `whos`)
FROM `stat` `a`
LEFT JOIN `stat` `b` ON `a`.`whos` = `b`.`whos` AND `b`.`time` < NOW() - INTERVAL 1 MINUTE
WHERE `a`.`time` >= NOW() - INTERVAL 1 MINUTE AND `b`.`whos` IS NULL
[/sql]

2. индексы:
KEY `whs_idx` (`whos`), заменить на whos+time
KEY `tim_idx` (`time`) удалить

Остановился на этом варианте, в среднем выполнение запроса 1.5 секунд. Думаю в реальных условиях будет еще быстрей.

Всем спасибо