key_buffer_size ? RAM size?
On huge tables, indexes, especially if they are not "appended to", cause disk hits -- whether MyISAM or InnoDB.
var0, being an AUTO_INCREMENT, will be "appended to" the end. Essentially no disk hits for it. I can't tell about the other 4 indexes. Let's say they are all very random. This implies that each row inserted will need 4 disk hits. On normal drives that means about 25 rows inserted per second.
Solutions...
RAID striping (0,5,6,10). That will give you a factor of improvement.
SSDs. $$$
PARTITIONing. This may help. It may also help with some SELECTs. To discuss further, please reveal some of the semantics of the indexed fields. (dates? md5s? names?) Also provide some of the SELECTs, so we can optimize them at the same time. The "partition key" must be part of any UNIQUE key. This is not a problem with the AUTO_INCREMENT, but it may be with the other UNIQUE key.
Nothing (yet) says whether the Engine will make any difference.
OBSERVATION #1
Look at this query
SELECT * FROM likes WHERE userid='100002047302002' and pageid='113623891994626' Limit 1
The likes table have no indexes other than the PRIMARY KEY. Please run this:
ALTER TABLE likes ADD INDEX userid_pageid_ndx (userid,pageid);
Unfortunately, you cannot index likes because of the datatype (MEDIUMTYPE). Please consider changing the datatype of userid and friendtype.
Your should create a new version of the likes table.
CREATE TABLE likesnew LIKE likes;
ALTER TABLE likesnew MODIFY COLUMN userid CHAR(15) NOT NULL;
ALTER TABLE likesnew MODIFY COLUMN friendid CHAR(15) NOT NULL;
ALTER TABLE likesnew ADD INDEX userid_pageid_ndx (userid,pageid);
ALTER TABLE likesnew DISABLE KEYS;
INSERT INTO likesnew SELECT * FROM likes;
ALTER TABLE likesnew ENABLE KEYS;
ALTER TABLE likes RENAME likesbak;
ALTER TABLE likesnew RENAME likes;
Please implement the datatype suggested.
Looking at the other query
SELECT * FROM friends WHERE userid='100001087100886' and friendid='100005456647732' Limit 1
I find the same story. Please make the same changes
CREATE TABLE friendsnew LIKE friends;
ALTER TABLE friendsnew MODIFY COLUMN userid CHAR(15) NOT NULL;
ALTER TABLE friendsnew MODIFY COLUMN friendid CHAR(15) NOT NULL;
ALTER TABLE friendsnew ADD INDEX userid_pageid_ndx (userid,pageid);
ALTER TABLE friendsnew DISABLE KEYS;
INSERT INTO friendsnew SELECT * FROM friends;
ALTER TABLE friendsnew ENABLE KEYS;
ALTER TABLE friends RENAME friendsbak;
ALTER TABLE friendsnew RENAME friends;
If you ever want to revert back to the old tables, then do this:
CREATE TABLE likesnew LIKE likesbak;
INSERT INTO likesnew SELECT * FROM likes;
ALTER TABLE likes RENAME likeszap;
ALTER TABLE likesnew RENAME likes;
CREATE TABLE friendsnew LIKE friendsbak;
INSERT INTO friendsnew SELECT * FROM friends;
ALTER TABLE friends RENAME friendszap;
ALTER TABLE friendsnew RENAME friends;
OBSERVATION #2
Your key_buffer_size is way too big. Remember the query I asked you to run ?
mysql> SELECT SUM(index_length) FROM information_schema.tables WHERE engine='MyISAM';
+-------------------+
| SUM(index_length) |
+-------------------+
| 150684672 |
+-------------------+
1 row in set (0.01 sec)
That's about 150M. So, here is the deal:
If you do not implement OBSERVATION #1 set key_buffer_size to 150M
[mysqld]
key_buffer_size=150M
If you do implement OBSERVATION #1, after making the new indexes, rerun that query like this:
SELECT SUM(index_length)/power(1024,2) RKBS
FROM information_schema.tables WHERE engine='MyISAM';
Whatever number comes back, use that number to set the key_buffer_size.
OBSERVATION #3 (Optional)
Keep in mind that the MyISAM Storage Engine does not take advantage of Multiple CPUs/Core no matter how many CPUs/Cores are installed. You need to consider changing all the tables to InnoDB. Here are my past posts on MySQL 5.5 and activating multiple cores for InnoDB
Best Answer
You need to rebuild the table in order to shrink the ibd file. This command will do it: