How can MySQL be optimized for fast reads? From this Flickr article, they state:


  MySQL is optimized for fast random reads.


Also, one of the answers to this Stack Overflow question states:


  MySQL is typically built for 80/20 read/write ratio


I used those two sentences as keywords to search a lot but cannot find any useful resouces to actually prove/explain that opinion. 

Can you explain to me in which way MySQL is optimized for fast random reads with some materials as references, please (or just offer some keyword to help to find out)?
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.