3 ms·
If we want to 100% correct, indexes don't just hurt writes, and they don't hurt all writes either. Two examples (there are more): - If I do an UPDATE query, a
by morgo 16y ago
If we want to 100% correct, indexes don't just hurt writes, and they don't hurt all writes either. Two examples (there are more):
- If I do an UPDATE query, and I don't modify indexed columns, the index doesn't need to be modified.
- If I have indexes on (last_name, first_name) and (last_name) the (last_name) index is redundant. Some queries may use the last_name index when I don't specify the first_name - but they could have used the other index. Now I have bloated what contents need to be memory resident (affecting reads as well).
- mmt 16y agoI'd say that suggesting there's a "penalty" at all implies a naive hardware setup[1]. Total system I/O is a finite resource, but this can be hard to reach if indexes are on separate spindles[2] from the data and each other. This can cost substantially more initially, but disappears at scale. Perhaps, then, as for the OP's original topic, one mistake is not hiring someone who knows these performance strategies. Another is using an RDBMS that doesn't support them. [1] which, granted, is far and away the norm [2] or sets of spindles, or SSDs
- nerfhammer 16y agoIt's not totally redundant. The index on last_name alone will be more efficient than (last_name, first_name) because the index will be able to contain more keys per index page. The difference can be significant if you're doing something that does an index scan on this column.