is there a strategy for creating proper database indexes?
Someone asked the question: "[INT, BIGINT or UUID/GUID in Oracle, DB2, Derby and HSQLDB?][1]" and I started to think about all the database schemas I've designed and the books that I've read and not one reference gave any *real* *clear* advice about creating indexes.

For example; if you have a compound index like

    date() ++ foo() ++ bar()

While this index is nice for searching and sorting date range data (read; read performance)... it is terrible for writes. (inserts always happen to the right side of the balanced tree forcing re-balancing which is an expensive operation)

Obviously... a) know your data. b) know your use-case. c) know your database engine.

But what are the common sense general rules for defining a reasonable schema for high performance databases?



  [1]: http://stackoverflow.com/questions/857775/int-bigint-or-uuid-guid-in-oracle-db2-derby-and-hsqldb
is there a strategy for creating proper database indexes?
===

Someone asked the question: "[INT, BIGINT or UUID/GUID in Oracle, DB2, Derby and HSQLDB?][1]" and I started to think about all the database schemas I've designed and the books that I've read and not one reference gave any *real* *clear* advice about creating indexes.

For example; if you have a compound index like

    date() ++ foo() ++ bar()

While this index is nice for searching and sorting date range data (read; read performance)... it is terrible for writes. (inserts always happen to the right side of the balanced tree forcing re-balancing which is an expensive operation)

Obviously... a) know your data. b) know your use-case. c) know your database engine.

But what are the common sense general rules for defining a reasonable schema for high performance databases?



  [1]: http://stackoverflow.com/questions/857775/int-bigint-or-uuid-guid-in-oracle-db2-derby-and-hsqldb