Resources | developer.brewmp.com Resources | developer.brewmp.com

Developer

resources

Best practices

Best practices in these areas should be considered to gain optimum performance.

  • Design
  • Indexing
  • Transactions
  • Advanced techniques

Design

Keep these principles in mind when designing a database:

  • The schema should be designed with a use-case in mind.
  • Prototype and collect performance data up front.
  • Avoid complicated trigger language.

Indexing

When indexing, these best practices should be followed:

  • Primary keys are indexed by default.
  • Indexing turns a linear search into a binary search.
  • Indexing is useful in queries that join tables.
  • Queries that do ordering can also take advantage of indexing.
  • Performance benefits gained by creating indexes usually outweigh the cost of increased database size and maintenance overhead.

Following is a case where indexing would be useful

SELECT msgText FROM userTbl INNER JOIN messages USING(userId)WHERE screenName
 = ‘John Smith’;

Transactions

Transactions should be designed to accomplish the following:

  • Each commit results in file system writes.
    • File system writes can be expensive.
  • Each statement executes within a transaction.
  • Transactions can be used to group statement executions.
    • Overall time for writing will decrease.
    • Individual commit time will increase.