Question: I have a huge (and growing) MyISAM table (700millions rows = 140Gb).

```
CREATE TABLE `keypairs` (
  `ID` char(60) NOT NULL,
  `pair` char(60) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM

```

The table option was changed to `ROW_FORMAT=FIXED`, cause both columns are always fixed length to max (60). And yes yes, `ID` is well a string sadly and not an INT.

`SELECT` queries are pretty ok in speed efficiency.

Databases and mysql engine are all `127.0.0.1/localhost`. (nothing distant)

Sadly, `INSERT` is slow as hell. I dont even talk about trying to `LOAD DATA` millions new rows... takes days.

There won't have any concurrent read on it. All SELECTs are done one by one by only my local server.(it is not for client's use)

(for infos : files sizes .MYD=88Gb, .MYI=53Gb, .TMM=400Mb) 

* How could i speed up inserts into that table?
* **Would it help to PARTITION that huge table ? (how then?)**
* I heard MyISAM is using "structure cache" as .frm files. And that a line into config file is helping mysql keep in memory all the .frm (in case of partitionned), would it help also? Actualy, my .frm file is 9kb only for 700millions rows)
* *string shortenning/compress function... the ID string? (same idea as rainbow tables) even if it lowers the max allowed unique ID's, i will anyway never reach the max of 60chars. so maybe its an idea? but before creating a new unique ID i have to check if shortened string doesn't exists in db ofc*
* **Same idea as shortening ID strings, what about using md5() on the ID? shorten string means faster or not in that case?**

Answer: 
Optimizing a table requires that you optimize for specific queries. You can't determine the best optimization strategy unless you have specific queries in mind. Any optimization improves one type of query at the expense of other types of queries.

For example, if your query is `SELECT SUM(pair) FROM keypairs` (a query that would have to scan the whole table anyway), partitioning won't help, and just adds overhead.

If we assume your typical query is inserting or selecting one keypair at a time by its primary key, then yes, partitioning can help a lot. It all depends on whether the optimizer can tell that your query will find its data in a narrow subset of partitions (ideally one partition).

Also make sure to tune MyISAM. There aren't many tuning options:

* Allocate `key_buffer_size` as high as you can spare to cache your indexes. Though I haven't ever tried anything higher than about 10GB, and I can't guarantee that MyISAM key buffers are stable at 53GB (the size of your MYI file).
* Pre-load the key buffers: <https://dev.mysql.com/doc/refman/5.7/en/cache-index.html>
* Size `read_buffer_size` and `read_rnd_buffer_size` appropriately given the queries you run. I can't give a specific value here, you should test different values with your queries.
* Size `bulk_insert_buffer_size` to something large if you want to speed up LOAD DATA INFILE. It's 8MB by default, I'd try at least 256MB. I haven't experimented with that setting, so I can't speak from experience.

I try not to use MyISAM at all. MySQL is definitely trying to deprecate its use.

> 
> ...is there a mysql command to ALTER TABLE add INT ID increment column automatically?
> 
> 
> 

Yes, see my answer to <https://stackoverflow.com/a/251630/20860>
First, your primary key is not incrementable.
Which means, roughly: at every insert the index have to be rebalanced.

No wonder it goes slowpoke at the table of such a size. 
And such an engine...

So, to the second: what's the point of keeping that MyISAM old junk? 

Like, for example, you don't mind to loose row or two (or -teen) in case of an accident? And etc, etc, etc, even setting aside that current MySQL maintainer (Oracle Corp) explicitly discourages usage of MyISAM.

So, here are possible solutions: 

1) Switch to Inno;

2) If you can't surrender the char ID, then:

* Add autoincrement numerical key and set it primary - then, index would be clustered and the cost of insert would drop significantly;
* Turn your current key into secondary index;

3) In case you can - it's obvious