Привет, помогите оптимизировать запрос на сайте, написанном на php.

В общем сайт представляет собой собрание фотографий, за которые можно голосовать. Вся информация о фотографиях хранится в таблице `photos`:

id(int) | country (int) | city(int)

т.е. каждая фотография сделана в определенной местности. Тип данных - int, т.к. Значение берется из других таблиц (`country` и `city`), названия (`country.name` и `city.name`) соответствуют идентификаторам из таблицы фото. Т.е. чтобы узнать, в каком городе сделан снимок - создает запрос:

Select * from `city` where `city.id`='значение photos.city из таблицы photos'

Аналогично и со странами.

Также есть таблица, в которую заносятся все данные о голосованиях `rate`:

win(int) | value(int) | date(date)

Win - это идентификатор фотографии
Value - значение голосования. Если голосовали "за", то в таблицу попадает значение +2, если против, то -1.
Date - дата голосования.

Таким образом можно составить рейтинг фотографий:

Select *,sum(`value`) as `summa` from `rate` group by `win` where `date` = 'подставляем сюда период за какой надо выбрать значения' order by `summa` desc;

Довольно простой и понятный запрос, а теперь - ВОПРОС!!!


На странице с фотографией необходимо создать 3 графика "местонахождения" в рейтинге фотографии (по городу, по стране и вцелом по сайту).

Другими словами нужно 3 массива, в котором содержалась следующая информация:

$array = ('date20140101' => '1-e место', 'date20140102' => '30-e место, 'date20140103' => '283-е место'... и т.д.);

Это для общего графика. Аналогичные массивы нужны также для города и страны. Т.е. Элемент массива $array['date20140101'] показывает рейтинг фотографии среди всех фотографий, за которые голосовали 1-го января. Соответственно для массивов с городом и страной надо показать на каком месте находилась эта фотография такого-то числа среди фотографий из этого города (страны) за которые голосовали в определенный день. Сложность даже не стом, чтобы подсчитать сумму баллов по необходимым условиям, а сколько узнать рейтинг. Если сначала запросить количество всех возможных результатов по необходимым условиям, создать рейтинг и найти в нем место этой фотграфии - необходимо сделать несколько запросов по таблице `rate` и связать ее с остальными таблицами. А в таблице `rate` Может быть несколько сотен тысяч записей. Что просто "накрывает" сервер. Может можно как-нибудь упростить запрос. Чтобы из него сразу возникал нужный массив. 3 запроса = 3 массива.

Если мои рассуждения неверны, буду рад выслушать критику. Заранее благодарен, очень нужна ваша помощь.

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

Это сообщение отредактировал(а) password - 26.6.2014, 00:51

Крайне неудачно. Код области должен быть не в таблице фотографий, а в таблице городов.


Для оптимизации надо чем-то жертвовать. В данном случае наиболее разумно имхо пожертвовать дисковым пространством, чтобы выиграть в скорости.
Я бы предложил держать предрасчётную таблицу фотография-дата-рейтинг. Суммарный рейтинг конкретной фотографии за конкретную дату. Для дополнительной оптимизации эту таблицу можно партиционировать - например, помесячно.
Соответственно для получения статистики не потребуется лопатить всю таблицу рейтингов и считать сумму, это значительно ускорит получение данных. Суммирование для города и области станет более компактным (впрочем, можно также держать таблицы предрасчётной статистики для города и для области, тоже партиционированные).
Обновление данных в таблицах предрасчёта нужно вполне очевидно повесить на триггеры. И, само собой, написать процедуры пересчёта этой предрасчётной статистики.