News for hackers. Stuff that matters.
Re:Database development mistakes made by application developers by cubicle67 on Monday December 13, 2010 @07:09AM (#2000184)
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,

Re:Database development mistakes made by application developers by nbpoole on Monday December 13, 2010 @07:25AM (#2000215)
> (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-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"

Re:Database development mistakes made by application developers by morgo on Monday December 13, 2010 @09:17AM (#2000496)
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).

Re:Database development mistakes made by application developers by mmt on Monday December 13, 2010 @05:53PM (#2002292)
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

Re:Database development mistakes made by application developers by nerfhammer on Monday December 13, 2010 @11:17PM (#2003107)
It'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.
Re:Database development mistakes made by application developers by ams6110 on Monday December 13, 2010 @09:51AM (#2000595)
This is called a "covering" index, e.g. the index on lastname+firstname also "covers" queries on with a predicate on just the last name. Most relational databases will do this, but it's always good to be sure in your particular case.
Re:Database development mistakes made by application developers by morgo on Monday December 13, 2010 @10:18AM (#2000671)
It's a composite index. A covering index would be this:

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.

Re:Database development mistakes made by application developers by ams6110 on Monday December 13, 2010 @11:22AM (#2000925)
Indeed you are correct.
Re:Database development mistakes made by application developers by cubicle67 on Monday December 13, 2010 @07:39AM (#2000253)
[Edit: removed defensive response. I've updated my original comment to remove its absoluteness]
Re:Database development mistakes made by application developers by swah on Monday December 13, 2010 @07:48AM (#2000277)
After reading the first example at 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".
Re:Database development mistakes made by application developers by henrikschroder on Monday December 13, 2010 @12:24PM (#2001171)
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.
Re:Database development mistakes made by application developers by nerfhammer on Monday December 13, 2010 @11:22PM (#2003116)
> In a primary index

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.

Re:Database development mistakes made by application developers by IgorPartola on Monday December 13, 2010 @08:17AM (#2000354)
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.
Re:Database development mistakes made by application developers by tbrownaw on Monday December 13, 2010 @08:05AM (#2000311)
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).
Re:Database development mistakes made by application developers by swah on Monday December 13, 2010 @08:27AM (#2000371)
My bad, I commented thinking 'id' was of serial primary key type.
Re:Database development mistakes made by application developers by jfb on Monday December 13, 2010 @11:16AM (#2000890)
Pedantically, SQL is built around bags. It's one of the worst misfeatures.
Re:Database development mistakes made by application developers by binarymax on Monday December 13, 2010 @08:28AM (#2000376)
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!)
Re:Database development mistakes made by application developers by tocomment on Monday December 13, 2010 @09:30AM (#2000535)
Use non-clustered, right? clustered is just for massive operations with distributed databases and junk, right?
Re:Database development mistakes made by application developers by binarymax on Monday December 13, 2010 @09:38AM (#2000559)
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)

Re:Database development mistakes made by application developers by ams6110 on Monday December 13, 2010 @09:46AM (#2000582)
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.
Re:Database development mistakes made by application developers by binarymax on Monday December 13, 2010 @10:01AM (#2000631)
DB2 supports clustering, and Oracle has a similar concept: 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.

Re:Database development mistakes made by application developers by simonw on Monday December 13, 2010 @07:18AM (#2000201)
> (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.

Re:Database development mistakes made by application developers by morgo on Monday December 13, 2010 @10:27AM (#2000705)
The poster corrected himself above - it's not the case.