Как создать Mysql-запрос на удаление записей с нетривиальным условием?

Question

artemev @artemev

MySQL

Как создать Mysql-запрос на удаление записей с нетривиальным условием?

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

id | user_id | game_id | score
таблица имеет составной индекс по полям user_id и game_id

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

Вопрос:
как составить запрос на удаление всех записей за исключением последних 30 и самой первой (для конкретного пользователя и конкретной игры)? Возможно ли такое вообще? Или придется пересмотреть архитектуру приложения...

Вопрос задан более трёх лет назад
2711 просмотров

Комментировать

Подписаться 4 Оценить Комментировать

Пригласить эксперта

Ответы на вопрос 3

5 комментариев

artemev @artemev Автор вопроса

@svd71 спасибо большое! Но запрос с двумя подзапросами... если учесть, что в таблице будет минимум 30 млн. записей, то насколько это будет быстро работать?

Написано более трёх лет назад
svd71 @svd71

поверьте, очень быстро.

Написано более трёх лет назад
svd71 @svd71

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

Написано более трёх лет назад
artemev @artemev Автор вопроса

@svd71 нет, как раз выполняться он будет довольно часто. При каждом сохранении нового результата. Т.е. при сохранении результата:

1. нашим запросом удаляем "лишние" записи (поскольку мы будем делать это при каждом сохранении, то "лишней" будет всегда одна запись)
2. добавляем новую запись

В итоге получаем, что каждый пользователь для конкретной игры может сохранить 30 последних результатов + самый первый

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

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

Написано более трёх лет назад
svd71 @svd71

тогда стоит ее поручить каждому пользователю в отдельности. В запрос добавить проверку совпадения user_id с указанным пользователем. При логине этого юзера использовать этот скрипт для подчиски мусора.

Написано более трёх лет назад

3 комментария

artemev @artemev Автор вопроса

@gwer
1. Тогда будет не удобно делать выборку, т.к. придется фильтровать, показывая только 30 последних значений и самое первое. А если делать как я объяснял, то просто делаешь селект с условием по user_id и game_id (по ним есть индекс). Результатов будет всегда <= 30 + самый первый результат.

2. Вы забыли о том, что необходимо хранить (не удалять) самую первую запись (чтобы можно было видеть свой прогресс).

Написано более трёх лет назад
artemev @artemev Автор вопроса

@gwer "на мой взгляд, такое решение удобнее, чем каждый раз проверять, появилась, существует ли уже 30 записей" - тогда придется проверять первая ли это игра

Написано более трёх лет назад
Владлен Грачев @gwer

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

Я же сказал, удалять вторую запись по порядку. Или предпоследнюю, не знаю, как вам яснее будет.

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

Поэтому и предложил два варианта. В каких-то случаях это некритично. В одном случае, неважно, что число записей колеблется от 30 до 40, например, если основная цель — сократить массивность БД и сохранить первую и последние записи. В другом — можно отсеивать лишние, но еще существующие, после выборки. От задачи и пожеланий зависит.

Тогда придется проверять первая ли это игра

Флаги для игр для каждого пользователя (заведомо меньшая таблица). Обработка первой игры отдельным скриптом. Реализация зависит от фантазии и требований. Можно придумать множество вариантов вплоть до полного переделывания архитектуры. Но панацею вряд ли найдете.

Написано более трёх лет назад

2 комментария

artemev @artemev Автор вопроса

@demimurych честно сказать, даже и не знаю... Если рассуждать чисто логически, то чем меньше записей, тем быстрее все это дело будет работать. Возможно в данном случае это не так? Не знаю. Я в MySQL не большой специалист. Если кто просветит по этому поводу, буду только рад!

Написано более трёх лет назад
demimurych @demimurych

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

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

Если говорить очень поверхностно, то любое удаление на самом деле не приводит к удалению данных из служебных файлов базы. Частое удаление приводит к дефрагментации таблиц что в свою очередь начинает влиять на производительность. Бороться с фрагментацией можно при помощи optimize table НО чтобы с ней бороться нужно четко понимать есть ли она вообще. т.е. исходить из конкретных цифр.

Написано более трёх лет назад

Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации

Похожие вопросы

MySQL

+1 ещё

Простой
Как извлечь топ 15 очков из таблицы чтобы игроки не дублировались?
- 1 подписчик
- 22 апр.
- 110 просмотров
1

ответ
MySQL

Простой
Как сохранить mysql базу?
- 1 подписчик
- 21 апр.
- 89 просмотров
1

ответ
PHP

+1 ещё

Простой
Как исправить ошибку Uncaught Error: Call to undefined function mysql_real_escape_string?
- 1 подписчик
- 21 апр.
- 103 просмотра
3

ответа
PHP

+2 ещё

Средний
Ошибка при регистрации PHP, в Unity, что делать?
- 1 подписчик
- 20 апр.
- 98 просмотров
1

ответ
MySQL

Простой
Объясните CASE WHEN THEN?
- 1 подписчик
- 20 апр.
- 106 просмотров
2

ответа
MySQL

Простой
Как обновить определенные значения в базе, в зависимости от значения переменной?
- 1 подписчик
- 20 апр.
- 42 просмотра
1

ответ
PHP

+2 ещё

Простой
Как в Drupal 10 массово проставить noindex для >1000 страниц?
- 1 подписчик
- 18 апр.
- 52 просмотра
1

ответ
MySQL

+1 ещё

Средний
Как восстановить базу данных mysql, если служба mysql не запускается на windows?
- 1 подписчик
- 18 апр.
- 109 просмотров
1

ответ
WordPress

+1 ещё

Простой
Как импортировать большую базу данных в Wordpress?
- 1 подписчик
- 18 апр.
- 68 просмотров
3

ответа
MySQL

+1 ещё

Средний
MySQL ошибка InnoDB: Attempted to open a previously opened tablespace. Куда копать?
- 1 подписчик
- 15 апр.
- 58 просмотров
1

ответ
Показать ещё Загружается…

Программист C++ Builder / базы данных MySQL

RU Electronics • Москва

от 180 000 до 200 000 ₽

Инженер технической поддержки с английским языком и знанием PHP/MySQL

IT-Aces

от 100 000 до 150 000 ₽

PHP FullStack Developer (Middle+)

ГК «Талант» • Сочи

от 100 000 до 200 000 ₽

Создать дудл - видео с AI аватаром

25 апр. 2024, в 10:25

2000 руб./за проект

Настроить на Asterisk общение с ботом в живом времени

25 апр. 2024, в 09:55

1000 руб./за проект

Доработать дизайн лендинга

25 апр. 2024, в 09:29

2500 руб./за проект

Answer 1 · 2014-04-19 08:24:35

Select * from users  u0 where
Not exists (select * from users u1 where
U1.user_id=u0.user_id order by  id asc limit 1)
Or
Not exists ( select * from users u2 where
U2.user_id=u0.user_id order by id desc 30)

Сначала убеждаешься, что запрос вываливает нужные данные. Потом переписываешь его на delete from.

Answer 2 · 2014-04-19 11:45:37

При каждом добавлении новой записи такой запрос делать не нужно.

Варианта вижу два:
1. Запускать по расписанию скрипт, удаляющий все лишние записи раз в сутки, например (суть скрипта уже описал @svd71).
2. При добавлении нового результата удалять второй (сортируем, задаем offset). Остается вопрос обработки первых 30 игр. Его можно решить, например, создавая после первой игры еще 29 нулевых значений (на мой взгляд, такое решение удобнее, чем каждый раз проверять, появилась, существует ли уже 30 записей).

Answer 3 · 2014-04-19 12:08:07

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

Как создать Mysql-запрос на удаление записей с нетривиальным условием?

Войдите, чтобы написать ответ

Минуточку внимания

Войдите на сайт