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

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

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

Подскажите куда копать

DDL, код, explain - в студию.

К примеру мы посчитали новые данные за позавчера, нужно этот день в табличке обновить
Строк может быть как больше так и меньше
например за 2013-03-20 00:00:00 было 2 млн уникальных строк
после пересчета вышло 2,1 млн строк, при чем в старых данных встречаются только 1,9 млн уникальных ключей, остальные новые.. А еще в старых данных 0,1 млн строк с ключами которых в новых данных уже нет
Теоретически мы можем 1,9 строк обновить апдейтом (update) потом нужно удалить 0,1 млн старых строк и еще потом вставить недостающие 0,2 млн строк с новыми ключами которых раньше  не было

Сейчас делается так:  Delete from table where date_time = '2013-03-20 00:00:00'  а затем массовый инсерт данных на эту дату

Это сообщение отредактировал(а) lancelot555 - 29.3.2013, 16:16

Движок - InnoDB?

да InnoDB

Тогда суть очевидна. Первичного индекса в таблице нет, посему первый уникальный индекс, не содержащий NULL-полей, становится кластерным. Т.е. вот этот твой составной монстр. И его перестройка при удалении (а удаление-то выполняется по одной записи!) коматозит весь процесс.
Просто введи в структуру первичный автоинкрементный ключ. Пусть даже он тебе и нахрен не нужен. Но именно он станет кластерным.

Хм.. не подумал об этом что-то. Спасибо за наводку, попробую.

Добавил в таблицу поле id 

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

Наверное нужен индекс по полю date_time

он есть 
KEY `date_time` (`date_time`)
отдельно и в составе ключа

Возможный результат. Никто чуда не обещал.

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

А если убрать:
 
Но оставить вот это внесённое вами изменение:
 
и 
.

Если конечно по полям  не ведётся поиск.