ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Удалить все кроме последних N строк
15 сообщений из 15, страница 1 из 1
Удалить все кроме последних N строк
    #38511774
nk32
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
nk32
Гость
Здравствуйте.

Подскажите, пожалуйста, как реализовать такой запрос. Структура таблицы:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
USERNAME    |  DATETIME  |   IP_ADDRESS
user0
user0
user0
user1
user1
user1
user2
user2
user2
...
userN
userN
userN

Код: sql
1.
2.
3.
4.
5.
6.
CREATE TABLE `log` (
    `USERNAME` VARCHAR(50) NOT NULL,
    `DATETIME` DATETIME NOT NULL,
    `IP_ADDRESS` VARCHAR(15) NOT NULL
)
COLLATE='utf8_general_ci'


ENGINE=MyISAM;

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

Вот такой запрос как-бы работает
Код: sql
1.
2.
delete from `log` 
    where `USERNAME` = ? and `DATETIME` not in (select `DATETIME` from (select * from `log` order by `DATETIME` desc limit 100) s )



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

К сожалению, все что мне удалось нагуглить, это тоже только только примеры такого удаления для одного уникального ключа.
...
Рейтинг: 0 / 0
25.12.2013, 10:23:53
| Ответить | Цитировать | Написать  
Удалить все кроме последних N строк
    #38511817
bochkov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
bochkov
Участник
nk32,
а в чем проблема несколько тысяч раз запрос выполнить для каждого юзера?
...
Рейтинг: 0 / 0
25.12.2013, 11:22:06
| Ответить | Цитировать | Написать  
Удалить все кроме последних N строк
    #38511859
nk32
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
nk32
Гость
Наверняка можно. Но это не разовая, а рутинная операция, мне бы не хотелось гонять 10k запросов на удаление лишний раз. Гораздо лучше, если в одном.
...
Рейтинг: 0 / 0
25.12.2013, 11:42:11
| Ответить | Цитировать | Написать  
Удалить все кроме последних N строк
    #38511884
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
сделай хранимку и в ней через курсор прогони для всех юзеров
будет один запрос на сервер.
...
Рейтинг: 0 / 0
25.12.2013, 11:55:52
| Ответить | Цитировать | Написать  
Удалить все кроме последних N строк
    #38511885
nk32
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
nk32
Гость
... хотя вот насчет курсора нужно подумать.
...
Рейтинг: 0 / 0
25.12.2013, 11:55:58
| Ответить | Цитировать | Написать  
Удалить все кроме последних N строк
    #38512114
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
nk32Наверняка можно. Но это не разовая, а рутинная операция, мне бы не хотелось гонять 10k запросов на удаление лишний раз. Гораздо лучше, если в одном.Надо сделать её один раз. А затем с технологии добавления записей перейти на технологию обновления самой старой записи. И их ВСЕГДА m,eltn оставаться ровно сколько было для каждого юзера. Если же их менее ста (или для новых юзеров) - просто добавить фэйк-записей до сотни.
...
Рейтинг: 0 / 0
25.12.2013, 13:54:59
| Ответить | Цитировать | Написать  
Удалить все кроме последних N строк
    #38512149
nk32
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
nk32
Гость
Akina,
не получится.
Дело в том, что я дал сферический пример, чтобы не грузить общественность мелкими подробностями.
В реальности, у меня есть 10k уникальных сессий, которые висят на железе, которое не умеет вести лог. Чтобы приделать ему эту функцию, раз в 10 минут эти сессии сливаются оттуда telnet-скриптом и запихиваются в базу. К сожалению, у меня нет отдельного триггера на каждое событие.

Насчет курсора: ~0,5 сек. на одну итерацию по таблице в 30 раз меньше проектной. Не комильфо.
...
Рейтинг: 0 / 0
25.12.2013, 14:14:13
| Ответить | Цитировать | Написать  
Удалить все кроме последних N строк
    #38512190
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
nk32 сессии сливаются оттуда telnet-скриптом и запихиваются в базу.И что? или у тебя идёт тупая потоковая вставка без даже твёрдой копии?
...
Рейтинг: 0 / 0
25.12.2013, 14:53:18
| Ответить | Цитировать | Написать  
Удалить все кроме последних N строк
    #38512364
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
nk32 ,

посмотрите http://sqlfiddle.com/#!2/976aa/1
...
Рейтинг: 0 / 0
25.12.2013, 17:06:46
| Ответить | Цитировать | Написать  
Удалить все кроме последних N строк
    #38512453
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
То есть, собственно, (уникальный?) индекс (USERNAME,DATETIME) и
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
delete t
from (
  select d.`USERNAME`, d.`DATETIME`
  from `log` d
  where (
      select count(*)
      from `log` c
      where c.`USERNAME`=d.`USERNAME` and c.`DATETIME`>=d.`DATETIME`
    ) > 100
  ) d
join `log` t on t.`USERNAME`=d.`USERNAME` and t.`DATETIME`=d.`DATETIME`;
...
Рейтинг: 0 / 0
25.12.2013, 18:49:58
| Ответить | Цитировать | Написать  
Удалить все кроме последних N строк
    #38512511
nk32
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
nk32
Гость
Cygapb-007,

спасибо, но я понял, что в моей ситуации такой подход не поможет. Вот это на тестовой таблице при любом join'e на саму себя...

Код: plaintext
1.
2.
3.
4.
5.
6.
+----+--------------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | PRIMARY            | d     | ALL  | NULL          | NULL | NULL    | NULL | 124412 | Using where |
|  2 | DEPENDENT SUBQUERY | c     | ALL  | NULL          | NULL | NULL    | NULL | 124412 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+--------+-------------+
2 rows in set (0.00 sec)

По времени - курсор быстрее.

Буду делать циклический лог, как предложил Akina.

авторИ что? или у тебя идёт тупая потоковая вставка без даже твёрдой копии?
Да. А зачем мне лишний код.
...
Рейтинг: 0 / 0
25.12.2013, 20:26:31
| Ответить | Цитировать | Написать  
Удалить все кроме последних N строк
    #38512568
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
nk32,

7489069

переменные -- фактически аналог курсора.
Надо будет два прохода -- один для выставления
флага на удаление и второй -- само удаление.
Можно в едином селекте но все равно два прохода.

что-то типа

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
DELETE 
   TableA
FROM
(
SELECT 
  t1.id,
  IF( 100 <
    IF( @user=t1.user_id, 
        @rownum:=@rownum+1, 
        @rownum:=1+least(0,@user:=t1.user_id)),
     'must_delete',
     'keep'
   ) action 
FROM 
  tableA t1, 
  (select @rownum:=1, @user:=-99) zz
ORDER BY
   user_id, date_a DESC
) zz
STRIGTH_JOIN  TableA
ON
  TableA.id = zz.id
 AND zz.action = 'must_delete'
...
Рейтинг: 0 / 0
25.12.2013, 22:00:48
| Ответить | Цитировать | Написать  
Удалить все кроме последних N строк
    #38512768
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
nk32Cygapb-007,

спасибо, но я понял, что в моей ситуации такой подход не поможет. Вот это на тестовой таблице при любом join'e на саму себя...

Код: plaintext
1.
2.
3.
4.
5.
6.
+----+--------------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | PRIMARY            | d     | ALL  | NULL          | NULL | NULL    | NULL | 124412 | Using where |
|  2 | DEPENDENT SUBQUERY | c     | ALL  | NULL          | NULL | NULL    | NULL | 124412 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+--------+-------------+
2 rows in set (0.00 sec)

По времени - курсор быстрее.У меня получилось несколько по-другому в http://sqlfiddle.com/#!2/f107c/3 : для
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
select *
from (
  select d.`USERNAME`, d.`DATETIME`
  from `log` d
  where (
      select count(*)
      from `log` c
      where c.`USERNAME`=d.`USERNAME` and c.`DATETIME`>=d.`DATETIME`
    ) > 180
  ) d
join `log` t on t.`USERNAME`=d.`USERNAME` and t.`DATETIME`=d.`DATETIME`;

План выглядит так:IDSELECT_TYPETABLETYPEPOSSIBLE_KEYSKEYKEY_LENREFROWSFILTEREDEXTRA1PRIMARYALL381001PRIMARYtrefname_datename_date312d.USERNAME;d.DATETIME1100Using where; Using index2DERIVEDdindexname_date3128149100Using where; Using index3DEPENDENT SUBQUERYcrefname_datename_date303db_2_f107c.d.USERNAME74100Using where; Using index
...
Рейтинг: 0 / 0
26.12.2013, 08:26:09
| Ответить | Цитировать | Написать  
Удалить все кроме последних N строк
    #38513136
nk32
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
nk32
Гость
Cygapb-007,
спасибо огромное!
Я действительно снес индексы, так как во время экспериментов с таблицей они мне тормозили удаление записей.
С индексом по имени и дате на запрос уходит ~ 5 сек по таблице из 100k записей. Супер! Еще раз спасибо.
...
Рейтинг: 0 / 0
26.12.2013, 14:15:49
| Ответить | Цитировать | Написать  
Удалить все кроме последних N строк
    #38513744
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
nk32Cygapb-007,
спасибо огромное!
Я действительно снес индексы, так как во время экспериментов с таблицей они мне тормозили удаление записей.
С индексом по имени и дате на запрос уходит ~ 5 сек по таблице из 100k записей. Супер! Еще раз спасибо.

если сможете и памяти хватит, попробуйте на переменных,
раз в 5-10 быстрее получится... наверное.
...
Рейтинг: 0 / 0
27.12.2013, 01:19:47
| Ответить | Цитировать | Написать  
15 сообщений из 15, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Удалить все кроме последних N строк
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&fpage=194&tid=1835483]:
 0ms
get settings:
 6ms
get forum list:
 12ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 41ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 44ms
get tp. blocked users:
 1ms
others: 197ms
total:  316ms
созд. / загр.: 316ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]