W
W
WiNNeR_tig2017-10-07 12:29:31
MySQL
WiNNeR_tig, 2017-10-07 12:29:31

When you need a lot of indexes, what to do?

I have the following table structure

CREATE TABLE `tb_visitors` (
  `id` int(11) UNSIGNED NOT NULL,
  `idad` int(11) UNSIGNED NOT NULL,
  `idus` int(11) UNSIGNED NOT NULL,
  `ip` int(10) UNSIGNED NOT NULL,
  `date` int(10) UNSIGNED NOT NULL,
  `status` enum('0','1') NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8 COMMENT='Запись визитов';

--
-- Индексы сохранённых таблиц
--

ALTER TABLE `tb_visitors`
  ADD PRIMARY KEY (`id`),
  ADD KEY `status` (`status`),
  ADD KEY `idad` (`idad`),
  ADD KEY `idus` (`idus`),
  ADD KEY `ip` (`ip`);

SELECT queries work fine in this case. But that's when it comes to
DELETE FROM `tb_visitors` WHERE `status` = '1' AND `date` < '".time()."'

The table is locked and can cause a huge queue if many records need to be deleted.
Here is the question: what can be done in this case? Is it stupid to set the index to date or change everything?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
R
Rsa97, 2017-10-07
@Rsa97

Build only those indexes that are needed. Every insert/modify/delete operation is an index rebuild. The more indexes, the longer these operations take. As long as the fetch rate is sufficient without the use of indexes, there is no need to build them.
With a mass change, you can first disable indexes, after the change, enable them again.

LOCK TABLES `table` WRITE;
ALTER TABLE `table` DISABLE KEYS;
INSERT/UPDATE/DELETE ...
ALTER TABLE `table` ENABLE KEYS;
UNLOCK TABLES;

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question