miksoft,

Спасибо вам, воспользовался второй реализацией с некоторыми дополнениями.

Сделал так:
1) создал новую таблицу game_views_popular_month содержащую два поля (game_id, gv_cnt)
2) написал хранимую процедуру со следующим содержимым для поддержания таблицы game_views_popular_month.

CALL number_games_page (28)

Содержимое хранимой процедуры (понятно, что содержимое не идеально, доведу до идеала позже, главное понятен смысл):

DELETE FROM game_views_popular_month;
INSERT INTO game_views_popular_month (game_id, gv_cnt)
SELECT game_views.game_id, count(*) AS gv_cnt
FROM `game_views` `game_views`
WHERE (`game_views`.`date` = DATE(NOW() - INTERVAL 1 MONTH))
GROUP BY game_views.game_id
ORDER BY gv_cnt DESC
LIMIT number;

Вызов на выполнение хранимой процедуры осуществляется по крону один раз ночью (когда нагрузка на сервер минимальна), происходит обновление таблицы game_views_popular_month новыми данными (записываются 28 новых записей - это популярные игры за месяц на текущую дату) из таблицы game_views. Таким образом удалось исключить из запроса большую проблемную таблицу содержащую более 1 000 000 записей и ежедневно наполняемую в районе 10 000 новых записей. В результате всего удалось ещё в 3 раза ускорить выполнение проблемного запроса.

Спасибо за помощь!

Забыл показать запрос, который получился в результате нашей оптимизации:





Теперь запрос выполняется в районе 0.150-0.250 сек

tanglirokuznetsov,

а если напрямую сджойнить, без промежуточной материализации - не быстрее будет?

Я вас не понимаю? По подробней напишите? Разве первоначальный запрос который требовалось оптимизировать не напрямую был сджойнин без материализации?

Akina,

автор1) ORDER BY в подзапросе не имеет смысла - он всё равно будет проигнорирован. А если его убрать - то подзапрос теряет смысл.


Использование ORDER BY придаёт запросу реальную отсортированную упорядоченную выдачу, при этом он НЕ ИГНОРИРУЕТСЯ, а реально работает, ваши утверждения НЕ ВЕРНЫ!

автор2) Замена звёздочек на список реально необходимых полей способен ускорить запрос, особенно при наличии индексов, совсем особенно - если они покрывающие.

Я в курсе про звёздочки+поля+индексы (я думаю уже вообще мало людей осталось который не в курсе), но реального ускорения выполнения запроса от использования полей в место звёздочек в данном случае я не увидел, как выполнялся за своё время так и выполняется в этих временных интервалах, при этом уменьшилось число символов в запросе и повысилась читабельность

tanglirokuznetsov,

в первоначальном запросе было задействовано больше таблиц и присутствовал лимит.

Во-первых: таблиц задействовано столько же (одну таблицу с более чем 1 000 000 записей заменили на другую в которой всегда 28 записей, которая подзапросом набивается один раз ночью по крону. На этом в скорости и выйграли!)

Во-вторых: если что-то хотите посоветовать/подсказать, то делайте это, не нужно отвлекать понапрасну, ФЛУДИТЬ и посты себе набивать какой-то х.... Я тоже умею ПЕРЕСКАЗЫВАТЬ очевидные вещи - это я про "присутствие лимита"

okuznetsov,

Это правильный запрос (в принципе можно и без ORDER BY обойтись, но тогда выводимые игры на странице будут не отсортированы по популярности, если кому нужна такая выдача, то пожалуйста я не против. В данном случае по проведённым мной тестам без ORDER BY - ни как не повлияло на скорость выполнения запроса)

Akina,

Не забывайте как моя тема называется: "требуется помощь в оптимизации запроса", подразумевается, что не кому-то, а именно мне нужно помочь, и именно в моих условиях и на моих объёмах данных - запрос ускорить.

okuznetsovИспользование ORDER BY придаёт запросу реальную отсортированную упорядоченную выдачу, при этом он НЕ ИГНОРИРУЕТСЯ, а реально работает, ваши утверждения НЕ ВЕРНЫ!Есть определённая категория граждан, которым везёт... но даже им везёт не всегда. Так что когда (не "если", а именно "когда") разобьёшь на этом своём ложном убеждении физиономию - не плачься.[/quot]

Уже ни один раз видел (не помню на каких форумах) похожее высказывание, разумеется не мне адресованное, читал у других. Теперь вот и мне посоветовали. Постараюсь не плакать)

Давайте не будем ругаться, в данном случае вы не правы, я вам об этом сказал. Везения здесь нет - на ORDER BY повторюсь в данном случае ОСНОВАНА ЛОГИКА ЗАПРОСА, кстати и вы об этом выше сказали.

авторчто не выполнены все указанные мной условия
Читайте выше, все ваши предложения/условия я соблюдал и пробовал в реальных условиях



Размеры таблиц я указал на скриншоте, таблица выросла до таких размеров за 2 года, по расчётам ещё за 5 лет может вырасти на 500% максимум, и я уверен, что это тоже для вас будет кукиш размер. До объёмов таблиц о которых вы говорите и я предполагаю врятли дело дойдёт.

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

Для повышения читабельности запроса используют его форматирование. А экономия байтов... тебе что, приплачивают за неё, что ли?[/quot]
Вы утрируете, нашли к чему цепляться. Понятно же что имеется ввиду.

tanglir,

Я не понимаю, что вы имеете в виду. Напишите конкретнее, что предлагаете, я попросил вас об этом, но вы продолжили также загадочно отвечать, что и в первом своём посте.







Может быть я туплю уже, но я реально не понимаю из ваших фраз, что вы предлагаете?

miksoft,

Я понял вас, спасибо за исчерпывающий всё расставляющий на свои места ответ, сейчас полностью согласен со всем. На данный момент на сервере mysql 5.5.31, но в будущем не исключён вариант на 5.6. Хорошо, что сейчас всплыли данные факты, честно говоря не готов был к такому, спасибо за полезную и интересную информацию.

Akina,

В следующий раз буду более внимателен к вашим ответам/советам, понял, что нужно вам задавать доп. вопросы (т.к. по некоторым моментам сложно понять вашу мысль). Если бы вы аргументировали свою позицию в похожем на miksoft виде, то с моей стороны не возникло ни каких сомнений. Надеюсь, что вы не в обиде на меня. Также хотелось бы сказать - в спорах рождается истина (не помню автора). Хорошая получилась тема на форуме, надеюсь в будущем кому-то окажется полезной.

Без order by в данном случае мне необходим, предполагаю, что правильнее LIMIT добавить и RIGHT JOIN? Или всё-таки использовать STRAIGHT_JOIN?

SELECT *
FROM `game` `t`
##STRAIGHT_JOIN
RIGHT JOIN
(
SELECT *
FROM game_views_popular_month
ORDER BY game_views_popular_month.gv_cnt DESC
LIMIT 28
)
as `t1` ON `t`.`id`=`t1`.`game_id`