6 ms·

For what it's worth, here's a few from me Indexes. Everybody says it, but I'm still surprised at the number of devs who have next to no idea about index and ho

For what it's worth, here's a few from me 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,

simonw 16y ago
> (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). 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.
- morgo 16y ago
  The poster corrected himself above - it's not the case.
nbpoole 16y ago
> (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). 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-indexes.html 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-multicolumn.html 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"
- cubicle67 16y ago
  [Edit: removed defensive response. I've updated my original comment to remove its absoluteness]
- deleted 16y ago
  [deleted]
- 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 ago
    I'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
swah 16y ago
After reading the first example at http://www.postgresql.org/docs/8.2/static/indexes-intro.html http://www.postgresql.org/docs/8.2/static/indexes-intro.html, I'm a little confused. 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".
- tbrownaw 16y ago
  I don't see any primary keys in that example, just one column that has an index (fast to look up on) and one that doesn't (slow to look up on, but they don't do that in the example).
  - swah 16y ago
    My bad, I commented thinking 'id' was of serial primary key type.
- IgorPartola 16y ago
  A primary key is just a unique index. All of its other properties and uses have nothing to do with the underlying implementation. It may be slightly faster for some types of queries than a "secondary" unique index if the RDBMS clusters the data by the primary key and you are using traditional hard drives. In your example, if you indexed the content column, the lookup on it would be fast. As a bonus, make sure to read up on composite indecies. They are the bread and butter , so to speak.
- henrikschroder 16y ago
  Generally: 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.
deleted 16y ago
[deleted]
binarymax 16y ago
Good Additions. I would also point out that if devs mess with indexes they definitely need to know the difference between clustered and nonclustered. I've seen atrocities due to a simple misunderstanding of this (guess what happens when you confuse the two and implement the opposite!)
- tocomment 16y ago
  Use non-clustered, right? clustered is just for massive operations with distributed databases and junk, right?
  - binarymax 16y ago
    Clustered is directly related to the physical order of the records. Non-clustered is a logical order that uses pointers to the physical location. Only one clustered is allowed per table. http://en.wikipedia.org/wiki/Index_(database) http://en.wikipedia.org/wiki/Index_(database)
    - ams6110 16y ago
      The concept also does not exist in some databases. Oracle, IIRC, does not have the notion of a clustered index. SQL Server does. Another illustration of why you should not assume that all relational databases work the same way.
      - binarymax 16y ago
        DB2 supports clustering, and Oracle has a similar concept: http://www.oracle-base.com/articles/8i/IndexOrganizedTables.php http://www.oracle-base.com/articles/8i/IndexOrganizedTables.... You are definitely right about each RDBMS having their own quirks though! Solid advice: know what you are working with and its nuances.
jfb 16y ago
Pedantically, SQL is built around bags. It's one of the worst misfeatures.

simonw 16y ago

> (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). 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.

morgo 16y ago
The poster corrected himself above - it's not the case.

nbpoole 16y ago

> (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). 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-indexes.html 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-multicolumn.html 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"

cubicle67 16y ago
[Edit: removed defensive response. I've updated my original comment to remove its absoluteness]
deleted 16y ago
[deleted]
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 ago
  I'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

swah 16y ago

After reading the first example at http://www.postgresql.org/docs/8.2/static/indexes-intro.html http://www.postgresql.org/docs/8.2/static/indexes-intro.html, I'm a little confused. 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".

tbrownaw 16y ago
I don't see any primary keys in that example, just one column that has an index (fast to look up on) and one that doesn't (slow to look up on, but they don't do that in the example).
- swah 16y ago
  My bad, I commented thinking 'id' was of serial primary key type.
IgorPartola 16y ago
A primary key is just a unique index. All of its other properties and uses have nothing to do with the underlying implementation. It may be slightly faster for some types of queries than a "secondary" unique index if the RDBMS clusters the data by the primary key and you are using traditional hard drives. In your example, if you indexed the content column, the lookup on it would be fast. As a bonus, make sure to read up on composite indecies. They are the bread and butter , so to speak.
henrikschroder 16y ago
Generally: 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.

deleted 16y ago

[deleted]

binarymax 16y ago

Good Additions. I would also point out that if devs mess with indexes they definitely need to know the difference between clustered and nonclustered. I've seen atrocities due to a simple misunderstanding of this (guess what happens when you confuse the two and implement the opposite!)

tocomment 16y ago
Use non-clustered, right? clustered is just for massive operations with distributed databases and junk, right?
- binarymax 16y ago
  Clustered is directly related to the physical order of the records. Non-clustered is a logical order that uses pointers to the physical location. Only one clustered is allowed per table. http://en.wikipedia.org/wiki/Index_(database) http://en.wikipedia.org/wiki/Index_(database)
  - ams6110 16y ago
    The concept also does not exist in some databases. Oracle, IIRC, does not have the notion of a clustered index. SQL Server does. Another illustration of why you should not assume that all relational databases work the same way.
    - binarymax 16y ago
      DB2 supports clustering, and Oracle has a similar concept: http://www.oracle-base.com/articles/8i/IndexOrganizedTables.php http://www.oracle-base.com/articles/8i/IndexOrganizedTables.... You are definitely right about each RDBMS having their own quirks though! Solid advice: know what you are working with and its nuances.

jfb 16y ago

Pedantically, SQL is built around bags. It's one of the worst misfeatures.