I don't believe that's true, at least not for all RDBMS. I just checked the docs for MySQL and PostgreSQL and they seem to agree with me.
http://dev.mysql.com/doc/refman/5.0/en/multiple-column-index... | "The name index is an index over the last_name and first_name columns. The index can be used for queries that specify values in a known range for last_name, or for both last_name and first_name."
http://www.postgresql.org/docs/8.2/static/indexes-multicolum... | "A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns"
- 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).
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
ALTER TABLE staff ADD INDEX idx (last_name, first_name); SELECT first_name FROM staff WHERE last_name = 'n';
A covering index is something very special - all results are returned from the index. Not all databases do this - Postgres does not, because it does not want to maintain all the versions (see MVCC in wikipedia) in the index.
I thought that operations using primary keys were always fast, that they were somehow always indexed.
I thought this would be fast:
SELECT content FROM test1 WHERE id = constant;
and this would be slow because it wasn't on a primary key: SELECT content FROM test1 WHERE content = "xxx";
EDIT: this comment was supposing that 'id' was actually of type "serial primary key".In a primary index, the leaf nodes are the actual data records, so when you've followed the index to the leaf you have the exact physical location on disk that you need to get the data from. If you do a query that retrieves multiple records, for example
SELECT * FROM test1 WHERE id > constant;
...then that translates very nicely into a bulk read from the underlying datastore.Contrast that with a secondary index, where each leaf node essentially contains the physical address of the record, so for each record you're returning, you have to make an additional random seek to the underlying datastore to get the entire record. This is why primary indexes are faster than secondary indexes, in general. Various caching schemes smooth out the difference though.
Another good tip to keep in mind when writing indexes is that you can have covering indexes. If you often do a query of the type
SELECT a, b FROM table WHERE c = ... AND d > ... ORDER BY e, f
If you then have an index over (c,d,e,f,a,b), that index can cover the entire query, i.e. you can return the records by just looking at the index, you don't need to fetch the actual records.That's a clustered index. In some data stores primary keys are always clustered (innodb), in some they're not (myisam), and some even let you optionally cluster the table data on a non-primary index (sql server).
> Contrast that with a secondary index, where each leaf node essentially contains the physical address of the record, so for each record you're returning, you have to make an additional random seek to the underlying datastore to get the entire record.
Not necessarily: often the non-leaf nodes of the clustered index are stored somewhere completely separate from the leaf nodes and end up with the address of a leaf node page. In such case seeks may not any better in principle but scans on the clustered key where you need lots of non-key data from the data page are. If you do not need lots of non-key columns from the data page, scans on the clustered key can actually be slower because the key columns are stored so much further apart and not all next to each other.
On the other hand, the secondary index may not end with row addresses but instead with primary keys which then must be looked up in turn, so in such case logically cannot be any faster than a primary key for a seek to a data page.
> This is why primary indexes are faster than secondary indexes, in general.
Clustered indexes are actually often not faster for many operations. A secondary index is often much faster if it covers the fields selected by the query without needing to do a bookmark lookup to data pages, and scan on secondary indexes are generally faster than clustered key scans, again moderated by whether you need to do bookmark lookups or not.
You are definitely right about each RDBMS having their own quirks though! Solid advice: know what you are working with and its nuances.
I did not know that - I always thought that a compound index would be used for a query against a subset of those columns provided the subset was a prefix of the overall list - so in your example, a query on surname would use the index but a query on firstname and dob would not.
Indexes. Everybody says it, but I'm still surprised at the number of devs who have next to no idea about index and how they work.
Few guides: Every index adds a write penalty, so don't just add them to everything. If you've got a compound index, it will only be used if the query also uses all the fields in the index (If you build an index on surname, firstname and dob, but execute select * from users where firstname="Bob" and surname="Jones", the index won't be used). Benchmark the difference each index makes, and stand back in awe at the improvement a few well places indexes can contribute.
[Edit: as pointed out below, my experience is well out of date and it looks like there's a number of database engines where the compound index thing doesn't apply]
Learn to think in sets. Most devs are used to operating on discrete pieces of data; we're not used to thinking in sets, and so writing efficient SQL doesn't come naturally. Put in the effort and learn it and you'll be well rewarded. SQL is built around sets of data, and can almost assuredly do stuff faster than your code can. Hint: anytime you find yourself getting the result of a query, iterating over the result set and executing another query for each result you're doing it wrong
Learn how your ORM works. If you're using an ORM (and most of us do), learn how to use it efficiently. This is where having a good working knowledge of SQL helps, because you can then find out how to help your ORM create good SQL. If you're using ActiveRecord, understand how to correctly use :conditions, :include etc, and what they're doing behind the scenes. Ideally you want your ORM to make one call to the DB, and to retrieve everything you need in that one call (calls are expensive, even for very small queries). Poorly written code causes the DB to get hit constantly. Not good,