оптимизация запроса для выборки id в игровом процессе

[BoBka-altRUist]

оптимизация запроса для выборки id в игровом процессе

добрый день,

есть таблица с id, id такого вида

id pass_id | date
1 | 123000001 | 2010-07-01 12:34:32
2 | 123000413 | 2010-07-01 12:35:33
3 | 123000503 | 2010-07-02 12:38:23
4 | 123000614 | 2010-07-03 12:45:12
5 | 123000614 | 2010-07-03 12:46:33
6 | 123000629 | 2010-07-03 12:50:02
7 | 123000677 | 2010-07-03 12:50:11
8 | 123004708 | 2010-07-03 12:51:12
9 | 123004718 | 2010-07-04 12:51:21
10 | 123004719 | 2010-07-04 12:55:56
11 | 123004729 | 2010-07-04 12:55:59

...
и так далее (это генерация для игры)

проблема в том что выданные коды (pass_id) они неуникальные (могут повторяться)

- уникальные можно отобрать через group by pass_id

а кроме того они встречаются довольно близкие,
нужно, за период (за 7-й месяц, за 3-е число), из уникальных pass_id взять близкие ("близкие" это значит все разряды совпадают кроме последних двух) но в случае если таких "близкие" больше 2-х

все остальные поля неважны

кроме того скажу что в таблице есть доп. поле pass_add (где записан id без последних двух разрядов - так сказать в помощь)

из примера выше должно получиться:

pass_id
123000614
123000629
123000677


я делал так:
сначала запрос: выбрать "близкие" у которых повторений больше 2


SELECT `pass_add`
FROM `table`
WHERE (
`date`
BETWEEN '2010-07-03 00:00:00'
AND '2010-07-03 23:59:59'
)
GROUP BY `pass_add`
HAVING COUNT( `pass_add` ) >2
ORDER BY `pass_add`


а потом поместить его внутрь другого

SELECT `pass_id`
FROM `table` where (
`date`
BETWEEN '2010-07-03 00:00:00'
AND '2010-07-03 23:59:59'
) AND `pass_add` IN (

SELECT `pass_add`
FROM `table`
WHERE (
`date`
BETWEEN '2010-07-03 00:00:00'
AND '2010-07-03 23:59:59'
)
GROUP BY `pass_add`
HAVING COUNT( `pass_add` ) >2
ORDER BY `pass_add`

)


скажите это правильно или можно проще и легче?

 

[zerkms]

[...]

 

[Вурдалак]

Через GROUP_CONCAT() можно упростить, но может в буфер не влезть.

 

[Wicked]

1) добавить поле `date2` типа date, чтобы у нас было const-условие на дату, а не range - это полезно для индексов
2) сделать индекс на (`date2`, `pass_add`, `pass_id`)

тогда можно сделать как-то так:

SELECT distinct(pass_id) FROM `table`
INNER JOIN
(
  SELECT `pass_add`
  FROM `table`
  WHERE `date2` = '2010-07-03'
  GROUP BY `date2`, `pass_add`
  HAVING COUNT(DISTINCT `pass_id`) > 2
) as filter
ON filter.pass_add = table.pass_add
WHERE `date2` = '2010-07-03'
ORDER BY date2, pass_add, pass_id

будет круто, если ты покажешь explain этого запроса

-~{}~ 13.08.10 09:19:

ну и да, group_concat в моем случае тоже может пригодиться - заменить запрос с подзапросом на один простой запрос

 

[zerkms]

WHERE `date2` = '2010-07-03' GROUP BY `date2`, `pass_add`

зачем в группировке дата?

-~{}~ 13.08.10 13:26:

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

 

[Wicked]

зачем в группировке дата?

я не помню, муся самостоятельно сможет определить по where и group by, что группировать нужно по этому самому индексу или нет.

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

потому что внутренним запросом мы выбираем pass_add, а не pass_id, которые дохнут от группировки. Чтобы выбирать там pass_id, можно использовать тот самый group_concat или наружний запрос.

 

[zerkms]

Wicked

муся самостоятельно сможет определить по where и group by

по логике должна. если нет то совсем ппц

потому что внутренним запросом мы выбираем

угу, пропустил. почему-то смотрел несколько минут и не мог заметить что поля таки разные ))

 

[Wicked]

по логике должна. если нет то совсем ппц

да, вроде все ок
но я бы все равно оставил группировку по дате тоже

 

[BoBka-altRUist]

1) добавить поле `date2` типа date, чтобы у нас было const-условие на дату, а не range - это полезно для индексов

да, но выборка чаще берется за неделю, или все равно лучше сделать поле date?
тот что я приводил запрос - просто вешает mysql сервер,
сейчас буду пробовать этот.

p.s.:а то у меня уже мысли: с помощью mysql просто выбрать уникальные за период и сохранить в файл а все остальное сделать на перле изврат конечно...

 

[antson]

BoBka-altRUist
зачем ? почему возникла такая задача ?

интуиция шепчет, что не все ладно в датском королевстве.

 

[BoBka-altRUist]

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

SELECT distinct(pass_id) FROM `table`
INNER JOIN
(
  SELECT `pass_add`
  FROM `table`
  WHERE `date2` = '2010-07-03'
  GROUP BY `date2`, `pass_add`
  HAVING COUNT(DISTINCT `pass_id`) > 2
) as filter
ON filter.pass_add = table.pass_add
WHERE `date2` = '2010-07-03'
ORDER BY pass_id

будет круто, если ты покажешь explain этого запроса

сори но я все же заменил на интервалы, т.к. выборка за день никогда не используется (а обычно берется за неделю)
но доп. поле date2 (date_add) добавил, т.к. ключ короче и для него уникальных значений меньше

SQL-запрос: 
... WHERE ( `date_add` BETWEEN '2010-08-03' AND '2010-08-03' ) 
... WHERE ( `date_add` BETWEEN '2010-08-03' AND '2010-08-03' ) ...  

Строки: 3 
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra 
1	PRIMARY	table	ref	date_add	date_add	3	const	7299	Using where; Using temporary; Using filesort
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	Using where; Distinct; Using join buffer
2	DERIVED	table	ALL	date_add	date_add	3	 	712259	Using filesort

тут судя по описанию используется буфер а Вурдалак писал что туда может не влезть выборка, можно об этом буфере где-то прочитать и как его использовать?

p.s.: в том варианте что я писал выше работает неправильно т.к. внешний запрос выбирает повторные pass_add т.е. в результате получаются дубли

 

[zerkms]

почему-то тут нельзя использовать сортировку по нескольким полям,

нет, он сказал тебе, что он не может понять, по какому из полей с именем pass_add нужно производить сортировку (их там два), а ты поленился просто перевести фразу

 

[Вурдалак]

BoBka-altRUist, не, я не про это. Можно сделать такой запрос:
[sql]SELECT GROUP_CONCAT(DISTINCT pass_id) AS pass_list FROM `table` WHERE `date` BETWEEN '2010-07-03 00:00:00' AND '2010-07-03 23:59:59' GROUP BY pass_add HAVING COUNT(pass_add) > 2;[/sql]
, но длина строки pass_list будет ограничена этим значением: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_group_concat_max_len

 

[BoBka-altRUist]

....

 

[Wicked]

Вурдалак

но длина строки pass_list будет ограничена этим значением

будет, но как следует из постановки задачи, таких pass_id будет не больши сотни на каждый pass_add, по 9 символов на id, разделенные запятыми - как раз будет 999 но есть вероятность, что когда-нибудь эти ID перевалят через 10**9, и тогда вероятность переполнения станет отличной от нуля

но всегда можно сделать такую выборку select, pass_add, GROUP_CONCAT(DISTINCT SUBSTRING(pass_id, -2))

 

[Вурдалак]

pass_id MOD 100