Algorithmically, it is doing what your colleague says. But, do you see what it is doing ???

It is generating 10,000 temp tables each containing 1 row after traversing 317 million rows through in the InnoDB internal index. Each temp table is a complete regeneration of the rowids in sernumbers_results_2009 table along with executing handler_read_prev commands internally to sort the data by an index scan from the back of the internal rowid index. Also, please remember you are dealing with InnoDB. Who knows what Multiversioning (via MVCC) is going on so that the INSERT is completed without interference and with rollback capabilities.

Is there any reason why this query wouldn't work for you ???

INSERT INTO sernumbers_results_2009
SELECT * FROM sernumbers_results
ORDER BY rowid DESC LIMIT 10000;

This will definitely generate one temp table.

Give it a Try !!!

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

• Jun 01, 2012 : I've got 16GB of ram, how should I configure MySQL Server?
• May 07, 2012 : MySQL Server Performance
• Apr 26, 2012 : Is the CPU performance relevant for a database server?
• Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian
• Oct 07, 2011 : Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?
• Sep 20, 2011 : Multi cores and MySQL Performance
• Sep 12, 2011 : Possible to make MySQL use more than one core?