на хостинге у меня выполняется раз в 15-20 мин такой запрос

DELETE FROM `ws_flights` WHERE `date`<1361518163;

в таблице при этом находится порядка 30000 - 50000 строк.
дабы ускорить его выполнение был добавлен индекс KEY `DATE` (`date`):



вопросы:

1. можно ли ещё что-то сделать для оптимизации? так как хостер жалуется что слишком много строк постоянно проверяется, в силу чего он отключает сайт и тогда жалуемся мы
2. чисто вопрос по терминологии: KEY `DATE` (`date`) - это индекс или нет? или просто ключ?

Я бы предложил попробовать создать индекс по (`date`,id) и получать в подзапросе перечень ID к удалению...



Либо то же, но JOIN, может, даже вьюшка...

Добавлено @ 10:01
PS. А какой процент записей удаляется? 
PPS. И вообще - поясните суть происходящего... если это тупо чистка лога - перейдите на лог постоянного объёма, и вместо удаления и вставки апдейтите просто самую старую запись.

это таблица сохраняемых результатов поиска из удалённой системы.

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



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

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




а этот запрос покатит, если SELECT id FROM `ws_flights` WHERE `date`<1361518163 соберёт до 20000 id-шников?

Вывод - удалять надо ежеминутно или даже чаще (версия сервера эвенты поддерживает? а права есть?). Тогда мой запрос даст нормальные результаты. При 1.7% и более высокой селективности метод будет достаточно эффективен.
Либо, как я указал выше, хранить постоянное количество записей (скажем, 100 тыс.), и не удалять-добавлять, а апдейтить самую старую.

Это сообщение отредактировал(а) Akina - 25.2.2013, 11:53

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



так если раз в 15-20 мин, то удаляется порядка 25% записей



пока сказать не могу, не пробовал.



подумаю над реорганизацией

Я разве сказал, что всё? удаляй раз в минуту то, что старше часа от роду...


Фуллскан таблицы, файлсорт при отборе... у хостера есть все основания материть тебя...

Добавлено через 3 минуты и 9 секунд


Добавлено через 5 минут и 26 секунд

Я могу ошибаться в силу плохого знания специфики MySQL, но, кажется, где-то читал, что индексы в нем, де факто являются кластерными, т.е. де факто содержат в хвосте значения ПК. Опять же, могу ошибаться, но кажется, я читал что это одинаково реализовано и в MyISAM и в InnoDB. Если это действительно так, и добавление в хвост значения PK не будет оптимизировано движком, то данным решением будет достигнут чистый оверхед без какого бы то ни было профита.

Почти да. Они содержат внутренний уникальный номер записи. С ПК, кстати, не связанный.


Только для InnoDB. И то с выпендронами.

Если это так, сканирование индекса и доступ по адресу строки, должен бы быть эффективнее нежели сканирование, пусть и покрывающиего индеса, извлечение PK, скариноварие PK, доступ по адресу строки.


Понял. Т.е. то жжж блыо про InnoDB только.

И да, я высказался сумбурно. Кластерный индекс, он на таблицу один и (что не совсем имхо правильно) в первую голову он - ПК. Остальные, не кластерные индексы рефают на него, т.е. не явно содержат значение кластерного индекса в хвосте.

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

вот начиная с этой фразы я всё нижеследующее не осилил

короче, мне надо вместо

DELETE FROM `ws_flights` WHERE `date`<1361518163; раз в 15-20 минут

использовать раз в минуту


* `date`< 1361518163 - число из пхп подставляется

а "subquery" - команда или константа какая? или мне полагается что-то вставить на его место?

bars80080, раз в минуту тебе надо запускать запрос, который самый первый в области "ниасилил". Из PHP при этом ничего подставлять не нужно, запрос передавать "как есть". При необходимости - можно в тексте запроса изменить интервал и давать его в изменённом виде.
Если же тебе доступно создание эвентов - надо один раз запустить второй код из той же области, и он (точнее, созданное им задание) каждую минуту будет запускать первый (сам первый при этом запускать не нужно).
subquery - это алиас, необходимый для корректной обработки сервером запроса. Можно не менять, оставить как есть.

DELETE FROM `ws_flights` WHERE id IN (SELECT id FROM `ws_flights` WHERE `date`<1361868779) subquery
error: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'subquery' at line 1


DELETE FROM `ws_flights` WHERE `id` IN (SELECT `id` FROM `ws_flights` WHERE `date`< NOW() - INTERVAL 1 HOURS) subquery
error: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HOURS) subquery' at line 1


DELETE FROM `ws_flights` WHERE `id` IN (SELECT `id` FROM `ws_flights` WHERE `date`< NOW() - INTERVAL 1 HOUR) subquery
error: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'subquery' at line 1


что-то не выходит

bars80080, всегда смотри, на что ругается сервер, ибо первый байт в кавычках - это первый байт того, что серверу не нравится.
Видимо, он не желает понимать алиас в выражении WHERE IN (я этим методом не пользуюсь. предпочитая JOIN - они обычно эффективнее, но там без алиасов никуда) - ну убери алиас...

так что ли?

query:
DELETE FROM `ws_flights` WHERE `id` IN (SELECT `id` FROM `ws_flights` WHERE `date`< NOW() - INTERVAL 1 HOUR)
error: 1093
You can't specify target table 'ws_flights' for update in FROM clause

Добавлено через 26 секунд
query:
DELETE FROM `ws_flights` WHERE `id` IN (SELECT `id` FROM `ws_flights` WHERE `date`< NOW() - INTERVAL 1 HOURS)
error: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HOURS)' at line 1

Ну тогда так:

Не утверждение! предположение...   
А что если взять партиционирование? Делить по времени, раз в сколько надо минут, убивать самую старую часть?

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

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

bars80080, 

Разумное зерно во всём этом есть. Однако я бы создал бы на основе эвентов надстройку, которая раз в полчаса создаёт новую таблицу, в которую собственно в течение ближайших получаса и сливаются все данные. Для полноценного доступа к данным используется надтаблица на основе merge engine. Та же надстройка одновременно с созданием новой таблицы перестраивает описание надтаблицы, подключая в структуру свежесозданную таблицу, и отцепляя, а затем убивая вместе с данными, устаревшую, полуторачасовой давности, таблицу.
При таком подходе единственное, что теряется - это возможность создания уникальных на уровне надтаблицы индексов (и констрейнтов). Однако всё это с лихвой может окупиться снижением накладнЫх расходов на процесс. При этом логика обслуживания полностью остаётся в рамках сервера, а, значит, прозрачна для клиента.

Вообще-то под такие объемы можно и нужно свой отдельный сервер под БД. Где все стоны хостера идут лесом.
Что впрочем не отменяет оптимизации.