Re: is it good add 3 index on a table with 30,000 insert in every 15 minutes?
Posted by:
Rick James
Date: November 30, 2013 02:48PM
In the 30K rows, is `hour` always the same? Is `eventDate` referring to the last 15 or so minutes?
Assuming yes...
You have 4 things to update for each row INSERTed. Since the table is large, the efficiency will depend on how many of those 5 need to hit the disk.
MyISAM data -- Assuming there have been no DELETEs or UPDATEs, then the new row is appended to the .MYD file. This is very easily cached.
PRIMARY KEY (`id`,`evedate`) -- `id` is AUTO_INCREMENT, so this is effectively 'appending' to the BTree in the .MYI file. Again, cached -- essentially one 1KB block at a time.
KEY `evedate_index` (`evedate`) -- Assuming these refer to the last 15 minutes, it might be several blocks, but still very cacheable.
KEY `hour_index` (`hour`) -- ditto
KEY `msisdn_index` (`msisdn`) -- This is probably 'random'. That is, it will probably need to do read-modify-write on lots of blocks. Perhaps not 30K blocks, but possibly a few thousand. That would be a few megabytes of cache 'churn'. This may cost 10-100 seconds, and may be the main cost of the whole INSERT.
If it is 2 minutes out of every 15, there is no problem of "getting behind". If you batch the INSERTs at 100 per batch, the table will probably be blocked for less than a second. At 1000/batch, a few seconds. (With InnoDB, there would be essentially no blocking.)
I suspect you will find not query using hour_index. Run EXPLAIN on each of your SELECTs.