How can MySQL be optimized for fast reads?
90% of Production servers are humming along nicely because they are not "pushing the envelope".

9% of Production servers are humming along nicely because they pushed the envelope, but then found a way around the problem(s) they encountered.

(I made up the numbers, based loosely on years of experience.)

My point is that you probably fall into the 90% and don't need to worry.  But when you get close to hundreds of writes/second or thousands of reads/second, you may migrate to the 9%.  Then bring your question to us, together with SHOW CREATE TABLE and some info about the queries.

As for Flickr, that article was written before their "Ticket server" melted down.  It was a server with exactly one purpose:  Produce the next photo_id.  This experience has little to do with 80/20 or random reads.  Most of the rest of Flickr is sharded because of high read and write load.  Are you anywhere near needing sharding?  And it is possible to essentially eliminate that "ticket server" by a minor architectural change.

The other article starts with "several hundred update and inserts on them per minute".  Well, that is not a problem unless you are sloppy with indexes, etc.  Several hundred per second is likely to be a problem.

UUIDs on huge tables are a known source of trouble due to the randomness of access for updating.

So, what is MySQL -- InnoDB in particular -- optimized for?  I would say "some of everything".  But then I would be quick to point out that this forum (and stackoverflow) are littered with inefficient SQL that can easily be worked around.


Read: Many novices don't really understand indexes, especially 'composite' indexes.
Read: Certain design patterns are prone to inefficiency:  EAV, UUID.
Write:  Inserting one row at a time is literally 10 times as slow as at least two alternatives.


Most of the optimizations (read or write) are focused around the 16KB blocks that comprise the BTrees that both data and indexes are stored in.  Discussing the details there would take hours.

Another anecdote:  Later, Flickr sped up most reads by 50-100% by making essentially one index change.