Re: 350+ million rows -- is this as fast as it can get? (too slow at the moment!)

[Rick James]

You fell over the cliff...

As long as the table and indexes are small enough to fit in cache (in RAM), adding a new row takes a constant amount of time (approx.)

Once your table is bigger, disk hits are required; and the frequency increases. Let's look at some cases...

Simple AUTO_INCREMENT PRIMARY KEY, no secondary keys. Well this won't slow down -- you are always appending to the end of the table and the PK.

Let's add a secondary key that is "random" (a name, or whatever, that won't be ordered as you do the inserts). Now, for each insert, it has to reach for the index block where it needs to put the pointer to that record. If the index can't be cached fully, it may have to do a disk read (and eventually a write).

If the index is 5% bigger than cache, you might have to do such a read 5% of the time. Get the picture? If the index is 5 times as big as the cache, it will usually have to hit the disk for a random block. (~80% of the time)

More secondary keys? More disk hits (if they are random).

I don't think it is actually "exponential", but it may feel like it.

Techniques that help:
1. Shrink the data
2. Shrink the data
3. Shrink the data
Ok, seriously:
* MyISAM is 2x-3x smaller.
* Use INT (4 bytes) instead of BIGINT (8 bytes); or even MEDIUMINT (3), etc.
* Compress blobs and big text fields
* Normalize
* In InnoDB, with 4 secondary indexes, remember that the PRIMARY KEY is in every secondary index -- so keep the PK small. (MyISAM has a byte offset or record number, so this does not apply.)
* When bulk loading, sort the data (of course, you can't sort it in all the ways you have indexes)
* Turn off indexing while inserting; turn back on afterwards. It will be a lengthy "Reindexing via Repair", but might still be faster.
* (InnoDB) Turn off Foreign key checks during a bulk load.