Optimize

Data types

  • Use the smallest data types possible
  • Use the smallest integer types possible
  • Declare columns to be NOT NULL whenever possible

Indexes

  • Only create indexes that are essential
  • The primary index of a table should be as small as possible
  • Use as little of the column as possible for indexes on string fields (however much will offer adequate uniqueness)
  • If a table is usually searched using multiple columns, create an index for the columns; for the first column in the index, prefer the most-used column, or a column with more duplicates
  • Verify whether queries actually make use of the indexes (if possible)
  • Avoid complex select queries on tables that are updated frequently (table locking would often block users trying to read the table)

Queries

Sample index
create table test (
    column1 char(30) not null,
    column2 char(30) not null,
    column3 char(30) not null,
    ...
    index test_index (column1, column2, column3));
  • All or part of the above index can be used for the following queries:
where column1='...'                                       (1)
where column1='...' and column2='...'                     (1, 2)
where column1='...' and column2='...' and column3='...'   (1, 2, 3)
where column1='...' and column3='...'                     (1)

where (column1='...' and column2='...') or (column1='...')                     (1) (only 1 is used in all "or" conditions)
where (column1='...' and column2='...') or (column1='...' and column3='...')   (1) (only 1 is used in all "or" conditions)
  • No part of the above index will be used for the following queries:
where column2='...'                         (column1 is not used)
where column2='...' and column3='...'       (column1 is not used)
where column1='...' or column2='...'        (column1 is not used in both "or" conditions)

De-normalizing the database

  • As needed

Tables

  • Use "alter table ... order by expr1, expr2, ..." if the rows are usually retrieved in expr1, expr2, ... order (especially after extensive changes to the table)

Misc

  • Use persistent connections whenever possible