How To Tutorials

Writing a good optimized sql queries will help to reduce server load on MySQL and also improves the load time and server response time for the web page.
Few tips to optimize MySQL queries are below:

1. Select only required colums:

Selecting every thing from the table will cause more memory uses and thus utilize more server resources.
BAD:
SELECT * FROM <TABLE>;

GOOD:

SELECT name, age FROM <TABLE>;

Only select required fields to have better performance.

2. Indexing correctly:

Index is not only for primary key, we should index  field that is search contineously, and also the joined column.

Example:

Select name, age from table where age=32 and name =’alex’ ;

Select name, age,group from table1 left join table2 on table1.groupid=table.group where age=32 and name=’alex’;

In first case, name and age should be indexed. ( Way of indexing will be covered in next blog).
In second case, along with name and age , groupid and group also needed to be indexed.

3. Use of group by , order by , having:

Using group by , order by will slow down the query, as these some times requires to create temp table by MySQL. reducing this as much as possible will be best for the performance.
When using order by to more than one column better have same order for all the column, different order will cause query to slow down.
BAD: Select name, age from table order by age desc, name asc;

GOOD: select name, age from table order by age desc, name desc;

4.  Reduce  the column size:

if we just need 20 char in the column, then using varchar(20) is the best option, assigning more bytes in like varchar(250), is just takes more memory and slow down the performance. Same with integer, try to use much small as possible like SMALLINT, or even TINYINT   than just INT.

5. Choose the right storage engine:

Check your requirement and choose the right storage engine. Different engine has different pros, and cons. Right storage engine will give better performance base on your requirement. MySQL supports MyISAM, InnoDB, MEMORY, ARCHIVE, CSV e.tc.

for reference http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html

6. Always join table with proper index join:

Joining table with no index column will slower the performance.

 

7.  RAND(), CURDATE():

Never ‘ORDER BY’ , condition with RAND(), CURDATE() or any MySQL function which creates different data on each run.
example:
Select name, age from table order by id rand();

in above example, this query cant be cached so each time this query will consume resources.
Select name, age from table where date>CURDATE();
As CURDATE() will give you  current date, above query cant be cached so instead of doing above we can solve this problem by:
$date=date(‘Y-m-d’);
Select name, age, from table where date>$date;

8. Never assign table column with NULL:

NULL is also same as empty. So this requires spaces( bytes). So always assign column with NOT NULL when ever possible. As this also helps to do searching and sorting faster.

9. Be careful when using wildcards on search:

Don’t use % on the search condition in the beginning. It has no effect on indexing of that column.
example:
select name, age from table where name like ‘%ab’;
This sql has no effect with index, MySQL will search all the column for the above match. So try to avoid using wildcard search on the begining of keyword. Many people suggest to reverse the column and then use the keyword as ‘ba%’ which gives better performance.

10. Always  use explain on query:

using explain will help to get the bottle neck on the query so always use explain to see the performance of the query.