Tips to speed up your database

Know what you should optimize

        If you want to optimize a specific query, it is extremely useful to be able to get an in-depth look at the result of a query. Using the EXPLAIN statement, you will get lots of useful info on the result produced by a specific query, as shown in the example below:
ex:

EXPLAIN SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

Don’t select what you don’t need

          A very common way to get the desired data is to use the * symbol, which will get all fields from the desired table

•   SELECT * FROM Posts;

        Instead, you should definitely select only the desired fields as shown in the example below. On a very small site with, let’s say, one visitor per minute, that wouldn’t make a difference. But on a site such as Cats Who Code, it saves a lot of work for the database.

• SELECT title, excerpt, author FROM Posts;

Use join instead of subqueries

         As a programmer, subqueries are something that you can be tempted to use and abuse. Subqueries, as show below, can be very useful:

• SELECT a.id, (SELECT MAX(created) FROM posts WHERE author_id = a.id) AS latest_post FROM authors a

          Although subqueries are useful, they often can be replaced by a join, which is definitely faster to execute.

• SELECT a.id, MAX(p.created) AS latest_post FROM authors a INNER JOIN posts p ON (a.id = p.author_id) GROUP BY a.id

Source: http://20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/

Use UNION instead of OR

The following example use the OR statement to get the result:

• SELECT * FROM a, b WHERE a.p = b.q or a.x = b.y;

The UNION statement allows you to combine the result sets of 2 or more select queries. The following example will return the same result that the above query gets, but it will be faster:

• SELECT * FROM a, b WHERE a.p = b.q UNION SELECT * FROM a, b WHERE a.x = b.y

Source: http://www.bcarter.com/optimsql.htm

Use indexes

         Database indexes are similar to those you can find in libraries: They allow the database to find the requested information faster, just like a library index will allow a reader to find what they’re looking for without loosing time.
An Index can be created on a single column or a combination of columns in a database table. A table index is a database structure that arranges the values of one or more columns in a database table in specific order.
The following query will create an index on the Model column from the Product table. The index is called idxModel:

• CREATE INDEX idxModel ON Product (Model);

Source: http://www.sql-tutorial.com/sql-indexes-sql-tutorial/

Be careful when using wildcards

          Wildcards are very useful because they can substitute for one or more characters when searching for data in a database. I’m not saying that you shouldn’t use them, but instead, you should use them with caution and not use the full wildcard when the prefix or postfix wildcard can do the same job.
In fact, doing a full wildcard search on a million records will certainly kill your database.

• #Full wildcard -  SELECT * FROM TABLE WHERE COLUMN LIKE '%hello%'; 
• #Post fix wildcard  -SELECT * FROM TABLE WHERE COLUMN LIKE 'hello%'; 
• #Prefix wildcard - SELECT * FROM TABLE WHERE COLUMN LIKE '%hello';

 Source: http://hungred.com/useful-information/ways-optimize-sql-queries/