Indexing for High Performance - High Performance MySQL - page 177

Figure 5-11. Inserting nonsequential values into a clustered index
When Primary Key Order Is Worse
For high-concurrency workloads, inserting in primary key order can actually create
points of contention in InnoDB. The upper end of the primary key is one hot spot.
Because all inserts take place there, concurrent inserts might fight over next-key locks.
Another hot spot is the AUTO_INCREMENT locking mechanism; if you experience problems
with that, you might be able to redesign your table or application, or configure
innodb_autoinc_lock_mode . If your server version doesn't support innodb_auto
inc_lock_mode , you can upgrade to a newer version of InnoDB that will perform better
for this specific workload.
Covering Indexes
A common suggestion is to create indexes for the query's WHERE clause, but that's only
part of the story. Indexes need to be designed for the whole query, not just the WHERE
clause. Indexes are indeed a way to find rows efficiently, but MySQL can also use an
index to retrieve a column's data, so it doesn't have to read the row at all. After all,
the index's leaf nodes contain the values they index; why read the row when reading