Hey folks,
After a long interval, I have again returned to my blog. Sorry for this long gap.
I am going to share a very important part of web development. It is the query optimization. For any web application irrespective of their technologies, SQL is query required to insert the records as well as fetch the records from database. If the number of tables/records are less, there is no major effect on the performance of the website. But, if we have to fetch the data by joining multiple tables and one or more tables contains huge amount of records, site performance degrade and it increase a heavy load on the web server as well as database server. So, writing optimized query is very important. We must follow the RDBMS rules while designing the database for an application.
Starting out improving performance with MySQL (assuming you don't already know what the problem query is) is to check the slow query log - it logs to a file all queries taking longer than x seconds.
Lets focus on optimization of queries:
During the work, I found a keyword "Explain" and "Explain Extended".
Please visit below urls to get the details knowledge about these:
http://dev.mysql.com/doc/refman/5.0/en/using-explain.html (Optimizing Queries with Explain)
http://dev.mysql.com/doc/refman/5.0/en/explain-extended.html (Explain Extended Ouptput Format)
http://stackoverflow.com/questions/362223/best-mysql-performance-tuning-tool
explain is anonymous of describe. But, it works magically when we use this keyword before any select query. After execution, it returned following fields:
ID,
select_type,
table,
type,
possible_keys,
key,
key_len,
ref,
rows,
extra
Below is the best tutorial which will help you to understand in and out of Explain:
http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/
Below are some common things which must be taken care while writing / optimizing queries:
After a long interval, I have again returned to my blog. Sorry for this long gap.
I am going to share a very important part of web development. It is the query optimization. For any web application irrespective of their technologies, SQL is query required to insert the records as well as fetch the records from database. If the number of tables/records are less, there is no major effect on the performance of the website. But, if we have to fetch the data by joining multiple tables and one or more tables contains huge amount of records, site performance degrade and it increase a heavy load on the web server as well as database server. So, writing optimized query is very important. We must follow the RDBMS rules while designing the database for an application.
Starting out improving performance with MySQL (assuming you don't already know what the problem query is) is to check the slow query log - it logs to a file all queries taking longer than x seconds.
Lets focus on optimization of queries:
During the work, I found a keyword "Explain" and "Explain Extended".
Please visit below urls to get the details knowledge about these:
http://dev.mysql.com/doc/refman/5.0/en/using-explain.html (Optimizing Queries with Explain)
http://dev.mysql.com/doc/refman/5.0/en/explain-extended.html (Explain Extended Ouptput Format)
http://stackoverflow.com/questions/362223/best-mysql-performance-tuning-tool
explain is anonymous of describe. But, it works magically when we use this keyword before any select query. After execution, it returned following fields:
ID,
select_type,
table,
type,
possible_keys,
key,
key_len,
ref,
rows,
extra
Below is the best tutorial which will help you to understand in and out of Explain:
http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/
Below are some common things which must be taken care while writing / optimizing queries:
- Try to avoid In clause
- Add Index for those columns which are frequently used by the query
- Arrange the WHERE clause properly - In WHERE clause write those conditional statements first which are going to filter more records.
- Setup MySQL query cache and give it more RAM if possible
- Put a timestamp instead of now() function in mysql query
- If required add new columns
- If your query has Group by and /or order by clause on a column, then try to place the table of that respective column at first place in joining of multiple tables. For example, if you have applied Group by and /or order by clause on column X of table XYZ then try to place XYZ table as first table in join. This effort will avoid creation of temporary tables.
- Try to avoid Blob or Text column in your column. Use these data types as a last option. If it happens - Temporary tables will create on Disk only.
- Place Group by or distinct clause on smaller columns (size <=512 bytes).
- Try to avoid Union / Union ALL in your queries.
- GROUP_CONCAT() or COUNT(DISTINCT) evaluation also tend to create temporary tables. If possible, you can also try to avoid such clauses.
- If executing update statement which will update multiple tables also tend to create temporary tables.
