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.

Options: ReplyQuote


Subject
Written By
Posted
Re: is it good add 3 index on a table with 30,000 insert in every 15 minutes?
November 30, 2013 02:48PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.