Re: Partitioning or indexing for millions of records

[Rick James]

> 20% of 4GB RAM(3.1GB for use)

Use:
key_buffer_size = 600M
(G = giga- = billion = 1e9; M = million = 1e6)
After changing my.ini, restart mysqld.

> every 15 minute you are going to insert 30,000 records
> batch insert statement

That is the best approach.

> INSERTs and SELECTs block each other.

To minimize blockage, use batches of 100-1000 records at a time. (Do not do 30K at once -- too much blockage, and very little speedup for the INSERT.)

> And yes we are going to purge old data also.

How long will you keep the data? Design the PARTITIONs as discussed in my link;
1 month: ~33 daily partitions
1 quarter: ~15 weekly partitions
1 year: ~14 monthly partitions
(etc).