SQL Indexes

Some of my notes on indexes :

A Table have Columns, Keys, Indices,Foriegn Keys 

Only index those columns that are required in WHERE and ORDER BY clauses. Indexing columns in abundance will result in some disadvantages.

Use the NOT NULL attribute for those columns in which you consider the indexing, so that NULL values will never be stored.

The EXPLAIN statement helps you to reveal how MySQL will execute a query. It shows how and in what order tables are joined. This can be much useful for determining how to write optimized queries, and whether the columns are needed to be indexed.

Any index (good/bad) increases insert and update time.

You want to maximize insert performance. Every index on a table reduces insert and update performance because they must be updated each time the data changes.

Primary key columns are typically great for indexing because they are unique and are often used to lookup rows.

The reason for this is when you add or update a row, it must add-to or update both the table itself and every index that row is a member of. So if you have five indexes on a table, each addition must write to six places – five indexes and the table – and an update may be touching up to six places in the worst case.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s