Optimizing SQL Queries

From simple expressions to string expressions or even in joins there are some simple yet effective things you can do to make you SQL Queries run a bit faster.

Simple Expression Example:

A good rule of thumb when using where/and to query for values put the most restrictive ones first.

SELECT *
FROM Students
WHERE sex = ‘female’
AND grade = ‘A’

The above query would run faster if you checked for the A first because less students will have an Another words

SELECT *
FROM Students
WHERE grade = ‘A’
AND sex = ‘female’

Another Example:

SELECT COUNT(*)
FROM Students

Might not be as fast if student_ID is the primary key

SELECT COUNT(student_ID)
FROM Students

For Strings minimize using % or _ in LIKE statements another words don’t use LIKE ‘%Tom%’ if LIKE ‘Tom%’ is all you really need.

Carefully chosen indexes speed up SQL queries without slowing updates too much. Almost all tables can benefit from an index, and experience has shown that the "ideal index" is almost never the primary key (even though a primary key index may be required to preserve referential integrity).


Any way just a few ideas. http://www.ebooksquad.com/2008/10/28/optimizing-sql-queries-over-text-databases.html has a free ebook download which discusses Optimizing SQL Queries in more detail.