Indexes degrade performance for write-only tables.
To add to this: a simplified, early explanation an old master gave me, indexes slow down writing and updating, but speed up
searching. So don't just index everything, index only the fields that will require some form of seeking/searching. This isn't always "where field="value", it's also the joins mentioned.
Since M is asking about indexing, an important point that is relevant, one I've (shamefully) never really understood or gotten a good answer for,
how do you know what is a "good" length for indexing on text fields? Integer fields need no length,
user_id int(11), index(user_id),
Indexing tiny fields is worthless (or really, overhead, no need)
state_sbbrev char(2)
But text fields benefit greatly by indexing:
email varchar(255), index (email(6))
The closest I've come to an answer is if the index length is the smallest average indicator of most significant difference between other records for that field, it's "probably" an optimized index length. I'm not happy with "probably." :-)
From the docs [dev.mysql.com]: CREATE INDEX part_of_name ON customer (name(10));
If names in the column usually differ in the first 10 characters, this index should not be much slower than an index created from the entire name column.
Huh? "Not much slower?" Then why index(10), why not index 3, or 6?) In the email example above, an index of 1 would be of little benefit. As the index length increases, it becomes more beneficial, but increases the size of the index table (right?) So how much is "not enough," how much is "too much?"
I suppose a testing environment and experimentation would give some answers, but "correlation does not always equal causation" and I'm not sure this would lead to a straight answer. There must be some form of formula or guideline to go by, I've never found it.
MySQL documentation on Indexing [dev.mysql.com] doesn't really answer it for me. :-)
