mysql query optimization

Is MySQL query Caching really good for performance optimization ?

Ananda Raj Pandey1 Comment

MySQL query cache is one of the option on MySQL server optimization.  But question here is, is it really good to do MySQL query Caching?

To understand this we have to know what really happens during MySQL query caching.

Example:

Select * from table ;

Untitled-1

Above is the result of running a simple query on 2M rows, with query cache enabled. Here we see each time query is executed, Query cache is checked for existing result. If result not found, new cache is written to the query cache.  Which seems good but imaging the different scenario.

1. Each time there is modification in the table like update, edit on column e.t.c  query that is cached is invalid, so whole process of reading the query and writing to query has to perform again.

2. When query cache is written, its locks the cache of that query, so if one query is changing anything to the cache, no other query can not access it unless the lock is released, which results more response time. If there is higher frequency of updates on the query there will be more response time.

3. If you have millions of combination of query that can be generated in your database, caching query is not an option, as caching is only for few sec and holding millions of cached query to the memory, is not a possible options.

So, this summarized that query cache is not always good so when we use it ?

1. if we have same query is written in same piece of code very frequently,  then there is a less chance table is updated with in that milli- second, in that case we can use query caching.  Many framework related development of highly oops based development we are using same query many time in the code for example:

$result = $dataModel->find->id(‘5’);

// php code

if($dataModel->RelationalModel->status == 2)  {

// php code

}

In above example  dataModel is called twice, so same query will be executed in same code twice ( if object is not cached) , in that case query caching can be effective. The code is just a basic idea, its not a real code or working code of any kind.

So what we do?

1. If we have more read and less writes to the tables then query cache  can be efficient. Sites like forums, blogs, directory, news sites e.t.c has very less update frequency so query cache will be effective.

2. Limiting query cache size can be good option like few mega byte or few hundred mega byte relative to huge data size will limit the frequency of query caching.

3. if possible, disable query cache and test over all effect on performance can be good idea.

MySQL optimization depends on the database usage frequency and database data size, so testing different scenario with benchmark will be the best option. There is no hard written rule on optimization. But every time we optimize  MySQL server just enabling the query cache is not always the best option.

10 Mysql query optimization good practices

Ananda Raj Pandey3 Comments

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.