оптимизация выборки из таблиц

[imp]

оптимизация выборки из таблиц

Доброе время суток!
Есть две таблицы:
`message`
_________________________
| id | text | load_date | id_reg |

`region`
______________
| id_reg | name |
В таблице `message` порядка 2 млн. записей, в `region` 13 записей.

Запрос:
SELECT r.name, COUNT(m.*) FROM region r, message m WHERE (m.id_reg=r.id_reg) AND m.load_date BETWEEN 'xxxx-xx-xx' AND 'xxxx-xx-xx' GROUP BY r.name;
Результат запроса : `название региона` - `количество записей`; и таких соответственно 13 записей.

Записи в `message` c 2002 года по 2010. Обратиться пользователь может в любой участок диапазона одинаково часто.
Результат выдается быстро если записи в начале таблицы. Чем дальше, тем хуже, в конце совсем не дождаться результата.
первичные ключи стоят на первых полях таблиц, индекс на `message`.id_reg.

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

 

[c0dex]

Индекс на load_date, потом делать выборку через усечение диапазона дат, после приджоинить регионы. Имхо так.

 

[LONGMAN]

И лучше как нибудь обойтись без группировки на name

 

[imp]

индекс поставил, группировку с name снял, спасибо.
усекать и приджоинить - это все в одном запросе?

вот такой запрос выполняется за 2 секунды, что меня вполне удовлетворяет:
SELECT r.id_reg, COUNT(*) FROM message m
LEFT JOIN region r ON m.id_reg=r.id_reg
GROUP BY r.id_reg;
получается подсчет по всему дипазону дат.

не пойму как предварительно усечь его до необходимого для пользователя .

 

[zerkms]

не пойму как предварительно усечь его до необходимого для пользователя .

внезапно: WHERE

 

[imp]

вот так:
SELECT r.id_reg, COUNT(*) FROM message m
LEFT JOIN region r ON m.id_reg=r.id_reg WHERE m.load_date BETWEEN 'xxxx-xx-xx' AND 'xxxx-xx-xx'
GROUP BY r.id_reg;

выполняется, но все равно вроде бы долговато, от 1 минуты до 5 минут. если диапазон небольшой, например, год, то где-то минуту, если взяться за весь диапазон, то больше 5 минут. Что то еще можно сделать? Хотя просто до минуты свести.

 

[zerkms]

EXPLAIN в студию

 

[c0dex]

LEFT JOIN тут нафига?

Сколько выполняется простой запрос с COUNT() для нужного диапазона? Без группировки. Сколько с группировкой?

 

[zerkms]

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

 

[imp]

id | select_type | table |type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | m |ALL | load_date | NULL |NULL | NULL |1895504 | Using where; Using temporary; Using filesort
1 | SIMPLE | r | eq_ref | PRIMARY |PRIMARY |1 | smistats.m.id_reg |1 | Using index

-~{}~ 14.10.10 13:56:

нет нужного диапазона, он каждый раз может быть разным. и как сделать COUNT без группировки, если я подсчитываю для каждого региона количество записей? так то он выдает результат только за один регион

 

[zerkms]

Сделай для начала запрос без LEFT JOIN.

Насколько большой получается диапазон, в процентах от всей таблы?

 

[imp]

без LEFT JOIN по сути сводится к тому что написано в самом начале. А диапазон, варьируется таким образом (он впринципе задается пользователем) все записи начинаются с 2002 года и заканчиваются 2010, так вот, нужно свести к максимально быстрому исполнению запрос диапазона 2002-2010, а по сути это 100% данных. если он будет быстр, то остальные что с меньшими диапазонами будут еще быстрее.

 

[zerkms]

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

 

[prolis]

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