Xpoint
   [напомнить пароль]

Оптимизация длинных SQL-запросов

Метки: [без меток]
[удл]
2005-03-10 19:27:44 [обр] RussianSpy[досье]

Доброго времени суток!
Имеется БД PostgreSQL 7.4.6.
Имеется таблица pays, в которой хранится информация об изменении количества очков пользователя во время игры. Поле paytype показывает тип изменения баланса(0 - ставка, 1 - выигрыш и т.д.).
Нижеприведенный запрос считает сумму и количество ставок за сегодня, вчера, неделю, месяц и вообще весь период.
Всего записей в таблице pays около 100 000.
БД используется из PHP 4.3.10
Вот два варианта запроса:
select count( * ) as i,-sum(balancechange) as is from pays where gameid<>0 and paytype=0 and paytime>CURRENT_DATE
union
select count( * ),-sum(balancechange) from pays where gameid<>0 and paytype=0 and paytime>(CURRENT_DATE - interval '1 day') AND paytime<CURRENT_DATE
union
select count( * ),-sum(balancechange) from pays where gameid<>0 and paytype=0 and paytime>(now() - interval '1 week')
union
select count( * ),-sum(balancechange) from pays where gameid<>0 and paytype=0 and paytime>(now() - interval '1 month')
union
select count( * ),-sum(balancechange) from pays where gameid<>0 and paytype=0

и второй:

select * from (select count( * ) as i1,-sum(balancechange+bonuschange) as i1s from pays where gameid<>0 and paytype=0 and paytime>CURRENT_DATE) as k1,(select count( * ) as i7,-sum(balancechange+bonuschange) as i7s from pays where gameid<>0 and paytype=0 and paytime>(now() - interval '1 week')) as k2, (select count( * ) as i30,-sum(balancechange+bonuschange) as i30s from pays where gameid<>0 and paytype=0 and paytime>(now() - interval '1 month')) as k3,(select count( * ) as iall,-sum(balancechange+bonuschange) as ialls from pays where gameid<>0 and paytype=0) as k4,(select count( * ) as iy,-sum(balancechange+bonuschange) as iys from pays where gameid<>0 and paytype=0 and paytime>(CURRENT_DATE - interval '1 day') AND paytime<CURRENT_DATE) as k5

Вопрос состоит в следующем. Время выполнения данного запроса превышает 1 секунду. Таких запросов (имеется ввиду такой сложности и такого объема) на странице общей статистики более 10. Можно ли каким-то образом оптимизировать данный запрос и с чем может быть связана столь большая продолжительность выполнения запросов?

спустя 13 минут [обр] Алексей В. Иванов(2/2861)[досье]
м-да. ну и бред. в первом вообще union не нужен. достаточно условий и сортировки.
во втором даже разбираться не хочется. ужас.
спустя 5 минут [обр] RussianSpy[досье]
Хороший ответ. Информативный. Что еще предложите?! Хочется конкретных предложений, а не "ну и бред". Никакого бреда там нет. А можно взглянуть на этот запрос без использования UNION?
спустя 13 часов [обр] Денис Гетман(5/228)[досье]
 Ну, в первом запросе можно проиндексировать все поля кроме сумм, конечно.
И вы в курсе, что он может выдать не 5 строк, а меньше? Так что может лучше сделать 5 отдельных запросов. Explain в руки и вперед - считать секунды.
 И вообще, такие запросы делают раз в десять минут, а результаты кешируют.
 А второй запрос - это что-то страшное. Нельзя же всю логику на SQL перекладывать. Те же 5 запросов, только еще одна сумма добавилась и в программе выдать.
спустя 1 час 16 минут [обр] Алексей В. Иванов(2/2861)[досье]
RussianSpy[досье]
В первом запросе UNION не лишний. Прошу прощения. Но его преминение, конечно, абсурдно. Хотя бы потому, как сказал Денис Гетман[досье] - неделю был выходной, сайт "отдыхал" и вся статистика сдвинулась на ряд-два.
100000 - это очень мало, так что смотрите индексы. У Вас обязетельно должна быть проиндексированна дата. Остальное не надо, т.к. проверки <>0 и =0 не требуют особого индекса и могут даже помешать.
Ну и конечно же, если вы рассчитываете расширять мощность системы - то скделайте кэш таблицу, куда будут сваливаться дневные/недельные отчеты. Это будет меленькая таблица из которой выборка будет проходить мгновенно.
спустя 5 часов [обр] RussianSpy[досье]
Спасибо огромное! Действительно добавление индекса для даты ускорило генерирование всей страницы отчета на 25%. Но тем не менее время генерирования все равно составляет более 3 секунд (там запросов подобного размера около 5 плюс штук 10 мелких). Думаю вот сделать представление (CREATE VIEW), но я так и не смог выяснить насколько оно загружает базу. Данные поступают ежесекундно в таблицы и стастика нужна в реальном времени.
Powered by POEM™ Engine Copyright © 2002-2005