Составить запрос для выгрузки скриншотов

SaneSL · 6 Мар 2009

Составить запрос для выгрузки скриншотов

Добрый день.

Помогите составить запрос для выгрузки скриншотов.

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

Есть две таблицы:

table_games с полями gamesid, games_name, games_screenshots_num(количество скриншотов к игре)

table_screenshots с полями screnid, screen_date

Мои соображения:
1. добавить в таблицу table_games поле в котором будет указана дата последнего добавленного скриншота, затем сортировать по этой дате таблицу и во вложенном селекте узнавать сколько этих самых новых скриншотов.

2. добавить в таблицу table_games поле в котором будет указана дата последнего добавленного скриншота и поле в котором будет указано сколько новых скриншотов.

Но опять же не хотелась бы совать все в таблицу table_games.

Фанат · 6 Мар 2009

Дело не в совать. Было бы, что совать - это был бы идеальный вариант. Но поле, в котором указано количество новых, устареет на следующий день.

П1, только считать новые не в уродском вложенном селекте, а джойном.

SaneSL · 6 Мар 2009

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

Хм, а как джоином получить количество новых скринов?

Фанат · 6 Мар 2009

группировкой, если я не ошибаюсь

SaneSL · 6 Мар 2009

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

-~{}~ 06.03.09 16:09:

И то врятли, так как сортировать с лимитом нужно.

Фанат · 6 Мар 2009

джойнить скриншоты к играм по ид игры. группировать по нему же, в условии писать дату -1 месяц

SaneSL · 6 Мар 2009

Это я понял, мне не понятно откуда возьмется такое значение как количество новых скриншотов.

Фанат · 6 Мар 2009

count(скриншотов)

SaneSL · 6 Мар 2009

получить новые скриншоты получилось.

Но нам в таблице также необходимо указать сриншоты которые не обновлялись, а их перекрывает условие "-1 месяц
".

Фанат · 6 Мар 2009

а вот общее количество пиши в денормализованную таблицу игр

SaneSL · 6 Мар 2009

Автор оригинала: *****
а вот общее количество пиши в денормализованную таблицу игр

Тоесть П.2 ?

Ну вот со вложеным селектом у меня что-то вроде этого получается:

PHP:

$date_limit = time() - 86400;

SELECT games.gamesid, games.name, games.games_screen_date, COUNT(screenshots.gamesid) AS new_screen_num
FROM tadle_games AS games
LEFT JOIN (SELECT * FROM tadle_screenshots WHERE date >= '$date_limit') AS screenshots 
ON games.gamesid = screenshots.gamesid
WHERE games.games_screen_date <> '0'
GROUP BY gamesid 
ORDER BY games.games_screen_date DESC limit 0,100

Работает но не знаю как по нагрузке будет.

Фанат · 6 Мар 2009

оёмоё
зачем здесь вложенный селект?

SaneSL · 6 Мар 2009

Автор оригинала: *****
оёмоё
зачем здесь вложенный селект?

Ну так как по другому то? Писать в table_games?

-~{}~ 07.03.09 04:13:

Сори, до меня дошло ))

PHP:

$date_limit = time() - 86400;

SELECT games.gamesid, games.name, games.games_screen_date, COUNT(screenshots.gamesid) AS new_screen_num
FROM tadle_games AS games
LEFT JOIN tadle_screenshots AS screenshots 
ON games.gamesid = screenshots.gamesid AND screenshots.date >= '$date_limit'
WHERE games.games_screen_date <> '0'
GROUP BY gamesid 
ORDER BY games.games_screen_date DESC limit 0,100

-~{}~ 07.03.09 04:30:

Только на сервере почему то не работает, не могу понять почему.
На локалхосте у меня стоит мускул версии 5.0.45 а на сервере 5.1.25

Сам запрос работает но new_screen_num всегда равен нулю (((

x-yuri · 7 Мар 2009

а зачем
[sql]WHERE games.games_screen_date <> '0'[/sql]
?

как насчет сортировать по MAX(table_screenshots.screen_date)?

еще можно джойнить все, а количество новых определять так: COUNT(IF(screenshots.date >= NOW() - INTERVAL 30 DAY, 1, NULL))

Только на сервере почему то не работает, не могу понять почему

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

SaneSL · 7 Мар 2009

Как выяснилось на сервере оба варианта тормозят, с джоином 1,5 сек с вложеным селектом до 0,7
Индексы висят на gamesid как еще можно оптимизировать?

Автор оригинала: x-yuri
а зачем
[sql]WHERE games.games_screen_date <> '0'[/sql]
?

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

как насчет сортировать по MAX(table_screenshots.screen_date)?

Гениально

Значит не нужно добавлять новое поле в таблицу с игрой.

еще можно джойнить все, а количество новых определять так: COUNT(IF(screenshots.date >= NOW() - INTERVAL 30 DAY, 1, NULL))

Не хочет почему то.

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

Ну их разве что больше в несколько раз, сейчас где то 12 тыс игр и 70 тыс скринов, я думаю это не так много для MySQL.

x-yuri · 7 Мар 2009

Индексы висят на gamesid как еще можно оптимизировать?

денормализация - добавлять количество, дату в table_games

Проверка есть ли у игры скриншоты, здесь так же можно проверить по полю с количеством скриншотов

а зачем тогда LEFT JOIN?

Не хочет почему то

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

SaneSL · 7 Мар 2009

Автор оригинала: x-yuri
как насчет сортировать по MAX(table_screenshots.screen_date)?

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

денормализация - добавлять количество, дату в table_games

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

а зачем тогда LEFT JOIN?

Джоин нужен для того чтобы узнать количество новых скриншотов за последнии 30 дней.
WHERE games.games_screen_date <> '0' нужен для того чтобы не выгружались игры у которых нет скриншотов, если условие вбрать они выгрузятся, если бы у нас был просто джоин то другое дело, а левый джоин дает выгрузится всему остальному.

x-yuri · 7 Мар 2009

Он оказывается не правильно работает, видимо потому что стоит GROUP BY он выгружает не последнею дату

конкретный пример?

а левый джоин дает выгрузится всему остальному

а чему там еще выгружаться?

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

почему не обновлять table_games при добавлении нового скриншота? при удалении?
уже понял

SaneSL · 7 Мар 2009

Автор оригинала: x-yuri
конкретный пример?

PHP:

SELECT games.gamesid, games.name, games.games_screen_date, COUNT(screenshots.gamesid) AS new_screen_num
FROM tadle_games AS games, MAX(screenshots.date)  AS screen_date
LEFT JOIN tadle_screenshots AS screenshots 
ON games.gamesid = screenshots.gamesid AND screenshots.date >= '$date_limit'
WHERE games.games_screen_date <> '0'
GROUP BY gamesid 
ORDER BY screen_date DESC limit 0,100

И здесь без разницы куда его пихать что в выгружаемые поля что в ORDER BY результат получается одинаковый

а чему там еще выгружаться?

Ну я же говорю выгрузятся игры у которых нет скриншотов, мне они нафиг не нужны в разделе скриншотов.

почему не обновлять table_games при добавлении нового скриншота? при удалении?
уже понял

Потому что количество новых скриншотов изменяется каждую секунду.

x-yuri · 7 Мар 2009

конкретный пример?

имелось в виду не пример запроса. В общем не понятно, что там может не работать

Ну я же говорю выгрузятся игры у которых нет скриншотов, мне они нафиг не нужны в разделе скриншотов

так зачем ты используешь LEFT JOIN если они тебе нафиг не нужны?

Составить запрос для выгрузки скриншотов

Новичок

oncle terrible

Новичок

oncle terrible

Новичок

oncle terrible

Новичок

oncle terrible

Новичок

oncle terrible

Новичок

oncle terrible

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок