| 0 comments ]

Mysql optimization by configuring the query cache
In Ubuntu (debian) the query cache settings can be changed in the following file /etc/mysql/my.cnf
 Use the following command to edit the file
 nano /etc/mysql/my.cnf or vi /etc/mysql/my.cnf 

In this file you can find following settings.
#  Query Cache Configuration
#   
query_cache_limit       = 1M
query_cache_size        = 16M
#

Query Cache options SQL_CACHE
SELECT SQL_CACHE id, name FROM students;

SQL_NO_CACHE
 SELECT SQL_NO_CACHE id, name FROM students; 

We can check whether cache enabled in mysql database
 mysql> SHOW VARIABLES LIKE 'have_query_cache'; 

We can set Query_cache by queries
mysql>SET GLOBAL query_cache_size = 41984; 

If "query_cache_size " has value '0', then query cache is disabled, in the mysql environment. Also need to take care about the setting the query_cache_size with huge value. that will affect the system. For more information about the query caching you can visit http://dev.mysql.com/doc/refman/5.1/en/query-cache.html

| 0 comments ]

Lets have a look on How to use the Stored Procedures in Mysql. We can use these stored procedures in PHP. The following example shows how to use mysql stored functions in PHP scripts. Defining a stored function

DELIMITER $$

DROP PROCEDURE IF EXISTS `tester`.`GetAllProducts`$$
CREATE PROCEDURE `tester`.GetAllProducts(IN t INT,IN age INT)
 BEGIN
 UPDATE students SET age = age WHERE id = t;
 INSERT INTO students (name,age,sex) VALUES ('Jayan',35,'M');
 END $$
DELIMITER ;

Calling a stored function from PHP

$res = mysql_query('call GetAllProducts(2,30)');

if ($res === FALSE) {
    die(mysql_error());
} else {
    echo "@";
    print_r($res);
}