Steps to follow for query tuning

Few steps for query tuning.

1)  Check for if any sub query or exists or not exists is there if yes then try to change it in the joins .

2)  Try to check the way joins had been written try to put it in format to follow one to many relation ship and joins are in primary to foreign keys.

3)  if dead locks are happening due try to implement the serialization or try to use hint with (nolock) with select statement and with (rowlock) with insert update and delete statement.

There are many more way to do.

Advertisements

Copy a MySQL database from one computer/server to another

You can run all the above 3 commands in one pass using mysqldump and mysql commands (insecure method, use only if you are using VPN or trust your network):
$ mysqldump db-name | mysql -h remote.box.com db-name

Use ssh if you don’t have direct access to remote mysql server (secure method):
$ mysqldump db-name | ssh user@remote.box.com mysql db-name
OR
$ mysqldump -u username -p’password’ db-name | ssh user@remote.box.com mysql -u username -p’password db-name

You can just copy table called foo to remote database (and remote mysql server remote.box.com) called bar using same syntax:mysqldump
$ mysqldump db-name foo | ssh user@remote.box.com mysql bar
OR
$ mysqldump -u user -p’password’ db-name foo | ssh user@remote.box.com mysql -u user -p’password’ db-name foo

This will not just save your time but you can impress your friend too ;). Almost all commands can be run using pipes under UNIX/Linux oses.

MySQL Transactional and Locking Statements

MySQL5.0 supports local transactions by statements like START TRANSACTION, SET AUTOCOMMIT, COMMIT and ROLLBACK. Transaction means a group of SQL Statements, which executes as a unit. And MySQL either executes all the statement successfully or it doesn’t execute anyone. This can be achieved by the commit and rollback. When all the statements executes successfully then you can commit it to effect the database permanently. But if any error has occurred then you can roll it back for cancellation it. 
START TRANSACTION, COMMIT and ROLLBACK

The general syntax of Start Transaction, commit and rollback is:

START TRANSACTION | BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET AUTOCOMMIT = {0 | 1}

START TRANSACTION and BEGIN statement is used start a new transaction. And COMMIT is used to commit (saved) the current transaction means that makes the changes permanently. ROLLBACK is used to rolls back the current transaction means canceling the changes. SET AUTOCOMMIT statement is used to disable or enable the default autocommit mode for current transaction.

The optional keyword WORK is used to support for COMMIT and ROLLBACK. And CHAIN and RELEASE keyword are used to additional control over the completion of transaction. The AND CHAIN clause is used to start a new transaction when the current one ends. And this new one has the same isolation level. The RELEASE clause is used for disconnecting the server with current client connection after terminating the current transaction. By default MySQL in autocommit mode. When we are using a transaction safe storage engine like BDB, InnoDB then we can disable the autocommit mode by using the following statement :
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.06 sec)
By using above statement we can disable the autocommit mode, then we must use the COMMIT to store the changes in disk. And if we want to ignore the changes then we can use ROLLBACK. For disabling the autocommit mode to a single series of statement then we can use the START TRANSACTION statement. Example :
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE Emp SET Perks=Perks*1.03 WHERE Salary>15000;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.02 sec)
Statements that can not be Rolled Back
In MySQL some statements are available that we cannot be rolled back these include DDL (Data Definition Language) statements like create database, create table, drop database, drop table, alter table. Make sure you transaction does not include these type of statements. If you have issued a DDL statement early in transaction and another statement fails then you cannot the rolled back the full effect of the transaction by issuing the ROLLBACK statement.
SAVEPOINT and ROLLBACK TO SAVEPOINT 
The general syntax of SAVEPOINT and ROLLBACK TO SAVEPOINT is :
SAVEPOINT savepoint_name 
ROLLBACK [WORK] TO SAVEPOINT savepoint_name
RELEASE SAVEPOINT savepoint_name

MySQL DELETE JOIN

MySQL DELETE JOIN with INNER JOIN

You often use the INNER JOIN clause in the SELECT statement to select records from a table that have corresponding records in other tables. To make it more convenient, MySQL also allows you to use the INNER JOIN clause with the DELETE statement to delete records from a table and also the corresponding records in other tables e.g., to delete records from both T1 and T2 tables that meet a particular condition, you use the following statement:

DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition

Notice that you put table names T1 and T2 between DELETE and FROM. If you omit the T1 table, the DELETE statement only deletes records in the T2 table, and if you omit the T2 table, only records in the T1 table are deleted.

The join condition T1.key = T2.key specifies the corresponding records in the T2 table that need be deleted.

The condition in the WHERE clause specifies which records in the T1 and T2 that need to be deleted.