This is going to be a quick-and-dirty discussion on the importance of covering indexes using mySQL.  I recently was hired to steward a LAMP system that’s fairly complex.  It relies heavily on mySQL as the db-engine and has mostly PHP driving the business logic within a Smarty template engine.

So far, by adjusting tunables within mySQL, I’ve been able to improve response times (world-wide page load averages) by about 25%.  Now it’s time to start ploughing through the slow-query logs since the db server is stable under the current loads and we’ve established a zero-state, through metrics, of baseline performance.

A db index, or indexing to describe the act of adding one or more indexes to a table or tables, to seek improvements in data access and retrieval, is often seen as something akin to the burning of incense and killing of goats when scrying the fortune of the gods.  As in all things performance-related to mySQL, it requires an continuous cycle of minute adjustment, measurement, and evaluation.

We’re going to talk about covering indexes today — (here’s a good overview of indexes courtesy of wikipedia — good to know, or good review!) — which are indexes that contain all of the data from the query or, in other words, the data is stored with the index and is returned by accessing the index without doing a table look-up or scan.