Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Удалить все кроме последних N строк / 15 сообщений из 15, страница 1 из 1
25.12.2013, 10:23:53
    #38511774
nk32
nk32
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удалить все кроме последних N строк
Здравствуйте.

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

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

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

посмотрите http://sqlfiddle.com/#!2/976aa/1
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.12.2013, 18:49:58
    #38512453
Cygapb-007
Cygapb-007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удалить все кроме последних N строк
То есть, собственно, (уникальный?) индекс (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, 20:26:31
    #38512511
nk32
nk32
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удалить все кроме последних N строк
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, 22:00:48
    #38512568
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удалить все кроме последних N строк
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
| Ответить | Цитировать | Написать  
26.12.2013, 08:26:09
    #38512768
Cygapb-007
Cygapb-007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удалить все кроме последних N строк
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, 14:15:49
    #38513136
nk32
nk32
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удалить все кроме последних N строк
Cygapb-007,
спасибо огромное!
Я действительно снес индексы, так как во время экспериментов с таблицей они мне тормозили удаление записей.
С индексом по имени и дате на запрос уходит ~ 5 сек по таблице из 100k записей. Супер! Еще раз спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.12.2013, 01:19:47
    #38513744
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удалить все кроме последних N строк
nk32Cygapb-007,
спасибо огромное!
Я действительно снес индексы, так как во время экспериментов с таблицей они мне тормозили удаление записей.
С индексом по имени и дате на запрос уходит ~ 5 сек по таблице из 100k записей. Супер! Еще раз спасибо.

если сможете и памяти хватит, попробуйте на переменных,
раз в 5-10 быстрее получится... наверное.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Удалить все кроме последних N строк / 15 сообщений из 15, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 279ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1835483]:
 0ms
get settings:
 5ms
get forum list:
 8ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 27ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 27ms
get tp. blocked users:
 1ms
others: 198ms
total:  279ms
x
x
Закрыть


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