MySQL Performance Tuning

When MySQL becomes too slow (or too unstable), temptation usually is to tweak the MySQL configuration file. Indeed, it’s a good place to start. But if you ever looked at the available configuration options, you know things can get messy – MySQL now has over 450 configuration variables for your consideration, that are not classified in any way, and neither of them is included in the stock my.cnf.

We can setup/change default MySQL setup value by two methods.
1. using MySQL cmd / phpmyadmin which is the temporary solution. after restarting MySQL server all setup will change to the default value.

2. using the changing mysql config file name my.cnf  vis ssh
To edit the MySQL settings with my.cnf file type the following command. 

nano /etc/my.cnf

Find the settings you wish to edit. Say, for example, you want to increase the max_connections limit for MySQL server. Locate max_connections and set your desired value.

max_connections=20

To increase the max_connectionsto 30 change it to the following.

max_connections=30

Enter Ctrl + O to save the settings and Ctrl + X to exit. Restart the MySQL server by typing the following command.

service mysql restart

What does it mean the process "Sleep"?

There is a lot of connection seeing sleep. It's not a query waiting for a connection; it's a connection pointer waiting for the timeout to terminate.

It doesn't have an impact on performance. The only thing it's using is a few bytes as every connection does. But if it crosse max_connection limit then MySQL will trigger error "Too many connections"

It'll better to kill "sleep" connections. By default connection still alive / stay in task list up to 8 hours

mysqld will timeout database connections based on two server options:

• interactive_timeout
• wait_timeout

Both are 28,800 seconds (8 hours) by default.

You can set these options in /etc/my.cnf

If your connections are persistent (opened via mysql_pconnect) you could lower these numbers to something reasonable like 600 (10 minutes) or even 60 (1 minute). Or, if your app works just fine, you can leave the default. This is up to you.

You must set these as follows in my.cnf (takes effect after mysqld is restarted):

[mysqld]
interactive_timeout=180
wait_timeout=180

We can check from directly MySQL using this command

show variables like "interactive_timeout"

If you do not want to restart MySQL, then run these two commands:

SET GLOBAL interactive_timeout = 180;
SET GLOBAL wait_timeout = 180;

This will not close the connections already open. This will cause new connections to close in 180 seconds automatically.

Update/Select stored records/content with prefix suffix in Mysql

Basically, I need to add a prefix OR a suffix to the contents of a cell for all records.
Example  Column name "mobile_number " in table name tableName with 5000 records...

mobile_number
0123456789
0987654321
0125556789
0123456999

I need to add the prefix "88" as a country code to all records so that it looks like this after SQL query runs...
mobile_number
880123456789
880987654321
880125556789
880123456999

For SELECT the query use

select concat('Prefix', mobile_number , 'Suffix')
  from theTable;For Updateupdate tableName 
   set mobile_number  = concat('88', mobile_number );For prefix and Suffix both or only prefix/suffixupdate tableName 
   set mobile_number  = concat('Prefix', mobile_number , 'Suffix');

Advantage of using NULL over Empty Strings or 0.00

NULL means a column is empty or not set. An empty string "" or 0 or 0.00 is not the same NULL.
0 means has a value but NULL is not a value.

It depends on which storage engine you use.

In MyISAM format, each row header contains a bitfield with one bit for each column to encode NULL state. A column that is NULL still takes up space, so NULL's don't reduce storage

In InnoDB, each column has a "field start offset" in the row header, which is one or two bytes per column. The high bit in that field start offset is on if the column is NULL. In that case, the column doesn't need to be stored at all.
So if you have a lot of NULL's your storage should be significantly reduced.

The only way I can imagine NULLs improving performance is that in InnoDB, a page of data may fit more rows if the rows contain NULLs. So your InnoDB buffers may be more effective.

1. 1 NULL requires 1 byte 
2. 1 Empty String ' ' requires 1 byte (assuming VARCHAR)
3. 1 Zero requires 4 bytes (assuming INT) 

You start to see the savings here:

1. 8 NULLs require 1 byte
2. 8 Empty Strings require 
3. 8 bytes 8 Zeros require 32 bytes 

On the other hand, I suggest using NULLs over empty strings or zeros, because they're more organized, portable, and require less space. To improve performance and save space, focus on using the proper data types, indexes, and queries instead of weird tricks.

NB: This suggestion is not for MyISAM. NULL using is not good for an index column.

Best way to replace \r\n with or clean mysql escape string php

When we use mysqli_real_escape_string() function to secure the input value to store in mysql there  \r\n  automatically inserted for using enter or line break.

The main problem you have with all the variations you've tried is that both \n and \r are escape characters that are only escaped when you use them in a double-quoted string.

In PHP, there is a big difference between '\r\n' and "\r\n". Note the single-quotes in the first, and double-quotes in the second.

So: '\r\n' will result in a four character string containing a slash, an 'r', another slash and an 'n', whereas "\r\n" will contain two characters, those being the new line and carriage return characters.

As there is fact about quoted so as usual function may not work. I wasted several hours on it.

str_replace(), preg_replace(), nl2br()

So with out worrying quote here is best solution
str_replace(array('\r', '\n', '\r\n', "\r", "\n", "\r\n"), "", $txt);

So here we escape both single and double quote. 

Multiply 2 columns from same row and sum all the results MYSQL

If we want to multiply 2 columns from same row and then sum all the results in mysql

Here is example table

ProductID	Price	QTY
1	100	2
1	100	2
2	200	3
1	100	1

 SELECT SUM(price*qty) price, SUM(qty) qty, proID FROM `table1` GROUP BY proID  

Result Will like blew

ProductID	Price	QTY
1	500	5
2	600	3

Arrange your mysql table's primary ID which is auto increment

What if you have a large MYSQL table where you've used auto increment ID but your ID are not arrange as many of your row deleted. In this case if you want to arrange your ID like fresh one so that it may increase your MYSQL performance.

First create a blank duplicate table where no data exist. If new created table name "table1".

Your main table name is "table".

Now run this query

CREATE TEMPORARY TABLE tmp SELECT * FROM table;
UPDATE tmp SET id=NULL;
INSERT INTO table1 SELECT * FROM tmp;
DROP TABLE tmp;

You'll see the new "table1" table copied data from old table with arrange ID. now delete the old table.

That's it. Every easy haaa!!!!

Update A Joined Table Mysql

I want to update a table in a statement that has several joins.
The multi-table UPDATE syntax in MySQL is like blew as sample

UPDATE tableA a
JOIN tableB b
   ON a.a_id = b.a_id
JOIN tableC c
   ON b.b_id = c.b_id
SET b.val = a.val+c.val
WHERE a.val > 10
    AND c.val > 10;

Duplicate / Copy rows/ records in the same MySQL table

I need to copy/duplicate a lot of rows. If you just export and import sql file it will not work as the table has auto increment id column. So I find an easy solution for my problem. I would like to duplicate a record in a table, but of course, the unique primary key needs to be updated.
First it need to create a temporary table and then update all id to null. then insert into table.

Here is an example query

CREATE TEMPORARY TABLE tmp SELECT * FROM sms_history WHERE date='2017-12-22';
UPDATE tmp SET id=NULL;
INSERT INTO sms_history SELECT * FROM tmp WHERE date='2017-12-22';
DROP TABLE tmp;

This is the most easy and fastest solution for this kind of problem.

Store php variable in mysql / dynamic email, SMS template

This is a good tutorial if we want to create dynamic Email Template/ Dynamic SMS Template or dynamic content using php mysql. Storing php variable in content and save to mysql database. when fetching data from database replace variable with value dynamically.

 First create a table as your require.  Suppose we want to store this kind of sms template in datasbse

 <p>  
 Hello : [FULL_NAME] <br/>   
 username: [USER_NAME] <br/>   
 email : [USER_EMAIL] <br/>  
 website : [SITE_URL]  
 </p>  

Now we can fetch this data from database and assign in a php variable

 $content = $row['content'];  
 //replace template var with value  
 $token = array(  
   'FULL_NAME' => 'Sharif Ahmed',  
   'USER_NAME' => 'winsharif',  
   'SITE_URL' => 'http://www.esteemcorporation.com',  
   'USER_EMAIL'=> 'winsharif@test.com'  
 );  
 $pattern = '[%s]';  
 foreach($token as $key=>$val){  
   $repVar[sprintf($pattern,$key)] = $val;  
 }  
 $SMSContent = strtr($content,$repVar);  
 echo $SMSContent;  

/////////////////////////////////////Output will be //////////////////////
Hello : Sharif Ahmed 
username: winsharif
email : http://www.esteemcorporation.com 
website :  winsharif@test.com

Database Storage allocation/Choosing Right Column Types mysql

Here integer type along with minimum maximum value length and storage in byte.

Type	Storage	Minimum Value	Maximum Value
	(Bytes)	(Signed/Unsigned)	(Signed/Unsigned)
TINYINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

In contrast to CHAR (0 to 65,535),  VARCHAR (length 0 to 65,535) values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

Value	CHAR(4)	Storage Required	VARCHAR(4)	Storage Required
''	'    '	4 bytes	''	1 byte
'ab'	'ab  '	4 bytes	'ab'	3 bytes
'abcd'	'abcd'	4 bytes	'abcd'	5 bytes
'abcdefgh'	'abcd'	4 bytes	'abcd'	5 bytes

varchar = 1 byte which is fix + number of characters( 0 to 255) byte
varch = 1+4 = 5 byte (abcd is 4 character+1 = 5 byte)
varchar = 2 byte which is fix + number of characters(256 to 65535) byte

For more text type :

TEXT	String length + 2 bytes	A string with a maximum length of 65,535 characters
MEDIUMTEXT	String length + 3 bytes	A string with a maximum length of 16,777,215 characters
LONGTEXT	String length + 4 bytes	A string with a maximum length of 4,294,967,295 characters

For other field/column type

FLOAT[Length, Decimals]	4 bytes	A small number with a floating decimal point
DOUBLE[Length, Decimals]	8 bytes	A large number with a floating decimal point
DECIMAL[Length, Decimals]	Length + 1 or 2 bytes	A DOUBLE stored as a string, allowing for a fixed decimal point
DATE	3 bytes	In the format of YYYY-MM-DD
DATETIME	8 bytes	In the format of YYYY-MM-DD HH:MM:SS
TIMESTAMP	4 bytes	In the format of YYYYMMDDHHMMSS; acceptable range starts in 1970 and ends in the year 2038
TIME	3 bytes	In the format of HH:MM:SS