Tip: Speed up your SQL database with indexing

When your database starts to grow, you'll find that your queries take longer and longer to complete. This is because they have to search more and more rows. This slowdown can affect your entire website or application as the slow queries inevitably occupy more simultaneous threads. You can even experience temporary outages if slow queries occupy all your threads, forcing every query request to wait!

There are numerous ways to fix this:
1. Code conservatively so that your query the database as little as possible.
2. Include primary keys in your WHERE and JOIN-ON clauses.
3. Run a Query Analyzer tool on slow queries to find out what parts of your query are slowest, and then...
4. Create indexes on columns that are frequently used in WHERE or JOIN clauses.

Main points when indexing:
1. Don't index columns where a large percentage of rows haveh the same values. This defeats the whole purpose.
2. Don't index large varchar columns. Again, this will not increase performance.
3. Don't index every column. Just index the ones used to identify unique rows in a large number of WHERE and JOIN clauses.
4. Go read some more in-depth documentation on indexing. This is just a general overview. Here is a decent article regarding Clustered VS. Non-Clustered Indexes: daemon: Database Indexing Strategies for FarCry. (FYI Farcry is a CMS, but the DB principals are the same)

For example:
In the case of one website I'm working on, my predecessor chose to ignore Primary Keys and generate his own uniquely identifying numbers. These numbers are used in many queries, but none of the columns were indexed. After applying an index to these columns, the entire website saw a significant performance boost. In fact, this has helped reduce the number of SQL Deadlocks by minimizing row-lock time.