Re: Questions about performance for huge table (100M+ rows)

[Rick James]

When running benchmarks, toss the first try. It will have a lot of disk hits just loading the cache.

What where your cache settings? key_buffer_size, innodb_buffer_pool_size.

Without an index on 'updated', this will be a table scan:
SELECT * FROM `test` ORDER BY `updated` LIMIT 100
The bigger the table, the slower it will run. If that is an important query, then add INDEX(updated).

I can't explain the wide difference between MyISAM and InnoDB.

The SELECT ... WHERE name= is likely to be faster in MyISAM -- 1 BTree lookup in the .MYI, then a byte offset into the .MYD. For InnoDB, 2 BTree lookups, first for name, then for id.

I can't explain your observation on "down" the table. Based on what you say, there should be no difference (aside from what might be cached).