10 Mysql query optimization good practices

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.


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.


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.
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:
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.
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.

Fixing 500 internal server error

“500 inernal server error’ can be due to various server and code related issue. To fix ‘500 internal server error’ we have to look into various aspect in the server and code.

1. File permissions / File size issue: 

If server cant read / write / or execute different files or even directories, it throws 500 internal server errors. This can be writing to the file generated by php like log files, image files, or creating directories from php. Same error problem also be created when server services like httpd ( apache), mysql et.c cant save / read / or write logs files associated with it.  To solve the problem, first check the server side ,  so need to see ‘messages’ log file which is generally , inside /var/ log/messages every error / status / alert created by linux system will be copied to this file so just reading it and finding the error and fixing the problem will solve this issue.

2. Tmp folder might be full:

As we know every linux server has tmp folder, which can be just a folder within a drive or new mount to the server.  If this folder is full that will also cause 500 internal server error.

its always better to run tempwatch script in the server to manage the size of the tmp folder, it can be run as a cron job in a server which automatically clears the tmp folder on periodic basics.

3.  .htaccess syntax issue or misconfigured.

if there is any mistake on .htaccess syntax error or if some module related condition or module mention in that htaccess that is not loaded in the server then we will get this error. Best way to solve this problem is to  comment out the htaccess line that you have recently added and reloading the page to see if that is the problem.

4. Mod security or similar security proxies in the server.

Some of the rules in the mod security is set to give out 500 internal server error  in case of match with criteria. So checking the mod security log file can also give the idea if there is any problem there.  If server has other proxy security installed, we need to check it there too.

5. Mis-configuration of apache / nginx/ php

Any web server like apache / nginx  e.tc misconfiguration can also cause the 500 internal server error.  Commonly, many times we are loading the module on web server that is not installed, or any module needs certain access to the files or folder can also cause this problem. Similarly misconfiguration of php.ini also cause this problem. To solve this problem log files of webserver and error log of php must be checked to get to the root of the problem.

6. php su_exec folder permission:

Many of us now, running php with su_exec for better security option. But this only allows folder to have 755 permission and if you have 777 permission to the folders then we will get 500 internal server error.

7. Bad email gateway:

if your code is trying to send an email and you get 500 internal server error , that means your email protocol is not set properly, so a quick look on this is worth trying.

I suggest to clear the browser cache, and if the server have internal cache like varnish, file cache e.t.c please clear those too and refresh after the problem is fixed, else even the problem is fixed we will get the same response as its being served by cache.

8. PHP and Apache settings:

Make sure to check few php config parameters:
a) upload_max_filesize : If you trying to upload and get 500 error make sure your file size is less than upload_max_filesize value in php.ini, if not change it to required value.

b) max_execution_time: If you script requires longer time to execute like importing xml data, computing large data set e.t.c you can try by increasing these value. If you have set your php as mod_fcgi then you have change two parameters in httpd.conf file or php.fcgi file.

FcgidMaxRequestLen 10737418240
FcgidIOTimeout 3600