Оптимизация Group by

[punchos]

Оптимизация Group by

Здравствуйте!
Есть таблица обращений пользователей....
со структурой
CREATE TABLE connects (
id int(11) NOT NULL auto_increment,
uid varchar(50) default NULL,
day int,
month int,
year int,
PRIMARY KEY (id),
KEY year (year),
KEY month (month),
KEY uid (uid),
) ENGINE=MyISAM;

в таблицу попадает запись при каждом обращении пользователя к сервису...в поле uid уникальный идентификатор пользователя...
такая задача....вывести количество уникальных обращений(новых пользователей) за ноябрь 2009(например), по дням...
то есть...1 ноября 2009 к системе обращались 120 тыс. новых пользователей...2 - 134...и т.д.

запрос как я себе его вижу выглядит так
select ss.day, COUNT(distinct ss.uid) AS count_conects
from connects as ss inner join (SELECT uid,min(id) as id_min FROM connects group by uid) as dd on ss.id=dd.id_min WHERE ss.month=11 AND ss.year=2009 GROUP BY ss.day;

в таблице сейчас 20 млн. записей...отрабатывает он 40-50 минут....
я так понимаю основной тормоз подзапрос SELECT uid,min(id) as id_min FROM connects group by uid...хотя могу ошибаться


у кого какие мысли по оптимизации?

 

[С.]

KEY year (year),
KEY month (month),

Что за чушь?

 

[punchos]

индексы...
а что не так?

-~{}~ 09.12.09 13:58:

люди...но хоть какую нибудь идейку....пжлст...
я уже начинаю подумывать чтоб завести еще одну таблицу....типа
connects_unique...и туда только уникальные заносить....
поскольку такой запросик
select day, COUNT(distinct uid) AS count_conects
from connects
WHERE month=11 AND year=2009 GROUP BY day;
работает шустро...1 минуту...меня вполне устраивает, к тому же уверен если индекс сделать такой KEY (month,year,day)..будет еще шустрее...

но встает вопрос если иметь таблицу connects_unique...допустим с первичным ключем uid...
то как тогда быть...проверять каждый раз на имеющийся uid перед вставкой...или вставлять без проверки и игрорировать мускульные ошибки о повторяющемся ключе?

 

[Gas]

Как минимум покажи explain, вполне вероятно что тормозит join, так как объединение идёт с temporary table, которая может идти второй, а в ней естественно нет индексов. В этом случае можно создавать отдельным запросом temporary table, заливать в неё уников и добавлять индекс, а потом уже джойнить.

По мелочам, хотя бы поставь полям day,month тип not null unsigned tinyint, а year - smallint. Индекс сделай составной year,month,day.

 

[prolis]

1.Как-то так:
[sql]
create table first_connects as
select uid, min(STR_TO_DATE(day+'-'+month+'-'+year,'%d-%m-%Y')) as date_min from connects
group by uid
[/sql]
2.[sql]
ALTER TABLE first_connects ADD PRIMARY KEY (uid,date_min)
[/sql]
3.По крону еженочно:[sql]
insert into first_connects
(
select uid, min(STR_TO_DATE(day+'-'+month+'-'+year,'%d-%m-%Y')) from connects
where uid>
(select max(uid) from first_connects)
)
[/sql]

 

[punchos]

Gas
вот explain...
+----+-------------+------------+--------+--------------------+---------+---------+-----------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len| ref | rows | Extra |
+----+-------------+------------+--------+--------------------+---------+---------+-----------+----------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1874673 | Using temporary; Using filesort |
| 1 | PRIMARY | ss | eq_ref | PRIMARY,month,year | PRIMARY | 4 | dd.id_min | 1 | Using where |
| 2 | DERIVED | conects | ALL | NULL | NULL | NULL | NULL | 2234822 | Using temporary; Using filesort |
+----+-------------+------------+--------+--------------------+---------+---------+-----------+----------+---------------------------------+
3 rows in set (1 hour 16 min 5.41 sec)

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



prolis
а смысл?....в поле uid хранится буквенно-числовой идентификатор пользователя...на подобии сешен айди...
мы и на первом шаге добавим в таблицу first_connects uid начинающийся скажем с буквы Z...
и на третем шаге не чего добавляться не будет...хотя за это время там появятся уники начинающийся скажем с A или B...
вообщем не схляет это

 

[prolis]

Автор оригинала: punchos
а смысл?....в поле uid хранится буквенно-числовой идентификатор пользователя...на подобии сешен айди...
мы и на первом шаге добавим в таблицу first_connects uid начинающийся скажем с буквы Z...
и на третем шаге не чего добавляться не будет...хотя за это время там появятся уники начинающийся скажем с A или B...
вообщем не схляет это

схаляет:
3.По крону еженочно:SQL:
[sql]
INSERT INTO first_connects(
SELECT uid, min( STR_TO_DATE(
DAY + '-' + MONTH + '-' + YEAR, '%d-%m-%Y'
) )
FROM connects
WHERE STR_TO_DATE(DAY + '-' + MONTH + '-' + YEAR, '%d-%m-%Y') > (
SELECT max(date_min)
FROM first_connects
)
)
[/sql]

 

[punchos]

prolis
дубликаты будут попадать с одинаковыми uid...необходимо еще и их исключать во WHERE...что нибудь типа not in...
а это уже будет не так шустро...хотя скорее всего быстрее чем час....надо будет попробовать....

 

[Gas]

prolis
только может лучше where заменить на что-то типа YEAR >= (SELECT YEAR(MAX(date_min) FROM first_connects )) AND MONTH >= ....

Так не должно быть полного перебора.

-~{}~ 09.12.09 16:25:

punchos

дубликаты будут попадать с одинаковыми uid

сделай ключ (uid, date_min) уникальным и вставляй insert ignore (это относительно предложения prolis'а)

 

[punchos]

Gas
понял....спасибо...так и буду делать