Database Optimization

Indexing can dramarically improve performance of MySQL database. Indexing is the most important tool for speeding up your queries.

Facts, Tips & Terms:
- Database is a set of tables. Table is a set of rows and columns
- Full Table scan (slow) - examine each row in the table
- Data rows kept in a data file and index values are kept in a index file
- MySQL indexes speed up searches for rows matching terms of a WHERE clause. It also improve performance of MIN(), MAX()
and ORDER By operations.
- Index columns that you search for, not columns you select
- Declare columns to be NOT NULL - fater processing and less storage.
- Disadvantages of Indexing
a). Slow inserts, deletes and updates (need to update data and index files)
b). Index file takes up the disk space

- How to choose indexes:
b). Use short indexes (int is better than varchar). A smaller index involves less disk I/O, so it can be compared more quickly.
a). Index should be uniue (spread of values in a column). So index work best for columns with unique values.
c). Indexes are used for
<, >, <=, =>, =