MySQL – Introduction to User Defined Variables

MySQL supports user defined variables to have some data that can be used later part of your query. You can save a value to a variable using a SELECT statement and later you can access its value.

Unlike other RDBMSs, you do not need to declare the data type for a variable. The data type is automatically assumed when you assign a value. A value can be assigned to a variable using a SET command as shown below

SET @server_type:='MySQL';

When you above command is executed, the value, MySQL is assigned to the variable called @server_type. Now you can use this variable in the later part of the code. Suppose if you want to display the value, you can use SELECT statement.

SELECT @server_type;

The result is MySQL. Once the value is assigned it remains for the entire session until changed by the later statements. So unlike SQL Server, you do not need to have this as part the execution code every time. (Because in SQL Server, the variables are execution scoped and dropped after the execution).

You can give column name as below

SELECT @server_type AS server_type;

You can also SELECT statement to DECLARE and SELECT the values for a variable.

SELECT @message:='Welcome to MySQL' AS MESSAGE;

The result is

Welcome to MySQL

You can make use of variables to effectively apply many logics. One of the useful method is to generate the row number as shown in this post MySQL – Generating Row Number for Each Row using Variable.

Reference: Pinal Dave (

About these ads

MySQL – Scalability on Amazon RDS: Scale out to multiple RDS instances

Today, I’d like to discuss getting better MySQL scalability on Amazon RDS.

The question of the day: “What can you do when a MySQL database needs to scale write-intensive workloads beyond the capabilities of the largest available machine on Amazon RDS?”

Let’s take a look.

In a typical EC2/RDS set-up, users connect to app servers from their mobile devices and tablets, computers, browsers, etc.  Then app servers connect to an RDS instance (web/cloud services) and in some cases they might leverage some read-only replicas.


Figure 1. A typical RDS instance is a single-instance database, with read replicas.  This is not very good at handling high write-based throughput.

As your application becomes more popular you can expect an increasing number of users, more transactions, and more accumulated data.  User interactions can become more challenging as the application adds more sophisticated capabilities. The result of all this positive activity: your MySQL database will inevitably begin to experience scalability pressures.

What can you do?

Broadly speaking, there are four options available to improve MySQL scalability on RDS.

1. Larger RDS Instances – If you’re not already using the maximum available RDS instance, you can always scale up – to larger hardware.  Bigger CPUs, more compute power, more memory et cetera. But the largest available RDS instance is still limited.  And they get expensive.

“High-Memory Quadruple Extra Large DB Instance”:

  • 68 GB of memory
  • 26 ECUs (8 virtual cores with 3.25 ECUs each)
  • 64-bit platform
  • High I/O Capacity
  • Provisioned IOPS Optimized: 1000Mbps

2. Provisioned IOPs – You can get provisioned IOPs and higher throughput on the I/O level.

However, there is a hard limit with a maximum instance size and maximum number of provisioned IOPs you can buy from Amazon and you simply cannot scale beyond these hardware specifications.

3. Leverage Read Replicas – If your application permits, you can leverage read replicas to offload some reads from the master databases. But there are a limited number of replicas you can utilize and Amazon generally requires some modifications to your existing application.

And read-replicas don’t help with write-intensive applications.

4. Multiple Database Instances – Amazon offers a fourth option:

You can implement partitioning,thereby spreading your data across multiple database Instances” (Link)

However, Amazon does not offer any guidance or facilities to help you with this. “Multiple database instances” is not an RDS feature.  And Amazon doesn’t explain how to implement this idea.

In fact, when asked, this is the response on an Amazon forum:

Q: Is there any documents that describe the partition DB across multiple RDS?
I need to use DB with more 1TB but exist a limitation during the create process, but I read in the any FAQ that you need to partition database, but I don’t find any documents that describe it.

A: “DB partitioning/sharding is not an official feature of Amazon RDS or MySQL, but a technique to scale out database by using multiple database instances. The appropriate way to split data depends on the characteristics of the application or data set. Therefore, there is no concrete and specific guidance.”

So now what?

The answer is to scale out with ScaleBase.

Amazon RDS with ScaleBase: What you get – MySQL Scalability!

ScaleBase is specifically designed to scale out a single MySQL RDS instance into multiple MySQL instances.

Critically, this is accomplished with no changes to your application code.  Your application continues to “see” one database.   ScaleBase does all the work of managing and enforcing an optimized data distribution policy to create multiple MySQL instances.

With ScaleBase, data distribution, transactions, concurrency control, and two-phase commit are all 100% transparent and 100% ACID-compliant, so applications, services and tooling continue to interact with your distributed RDS as if it were a single MySQL instance.

The result: now you can cost-effectively leverage multiple MySQL RDS instance to scale out write-intensive workloads to an unlimited number of users, transactions, and data.

Amazon RDS with ScaleBase: What you keep – Everything!

And how does this change your Amazon environment?

1. Keep your application, unchanged – There is no change your application development life-cycle at all.  You still use your existing development tools, frameworks and libraries.  Application quality assurance and testing cycles stay the same. And, critically, you stay with an ACID-compliant MySQL environment.

2. Keep your RDS value-added services – The value-added services that you rely on are all still available. Amazon will continue to handle database maintenance and updates for you. You can still leverage High Availability via Multi A-Z.  And, if it benefits youra application throughput, you can still use read replicas.

3. Keep your RDS administration – Finally the RDS monitoring and provisioning tools you rely on still work as they did before.

With your one large MySQL instance, now split into multiple instances, you can actually use less expensive, smallersmaller available RDS hardware and continue to see better database performance.


Amazon RDS is a tremendous service, but it doesn’t offer solutions to scale beyond a single MySQL instance. Larger RDS instances get more expensive.  And when you max-out on the available hardware, you’re stuck.  Amazon recommends scaling out your single instance into multiple instances for transaction-intensive apps, but offers no services or guidance to help you. This is where ScaleBase comes in to save the day.

It gives you a simple and effective way to create multiple MySQL RDS instances, while removing all the complexities typically caused by “DIY” sharding andwith no changes to your applications .

With ScaleBase you continue to leverage the AWS/RDS ecosystem: commodity hardware and value added services like read replicas, multi A-Z, maintenance/updates and administration with monitoring tools and provisioning.


If you’re curious to try ScaleBase on Amazon, it can be found here – Download NOW.

Reference: Pinal Dave (

SQL SERVER – 3 Challenges for DBA and Smart Solutions

Developer’s life is never easy. DBA’s life is even crazier.

DBA’s Life

When a developer wakes up in the morning, most of the time have no idea what different challenges they are going to face that day. Of course, most of the developers know the project and roadmap, which they are working on. However, developers have no clue what coding challenges which they are going face for that day.

DBA’s life is even crazier. When DBA wakes up in the morning – they often thank that they were not disturbed during the night due to server issues. The very next thing they wish is that they do not want to challenge which they can’t solve for that day. The problems DBA face every single day are mostly unpredictable and they just have to solve them as they come during the day.

Though the life of DBA is not always bad. There are always ways and methods how one can overcome various challenges. Let us see three of the challenges and how a DBA can use various tools to overcome them.

Challenge #1 Synchronize Data Across Server

A Very common challenge DBA receive is that they have to synchronize the data across the servers. If you try to manually write that up, it may take forever to accomplish the task. It is nearly impossible to do the same with the help of the T-SQL. However, thankfully there are tools like dbForge Studio which can save a day and synchronize data across servers. Read my detailed blog post about the same over here: SQL SERVER – Synchronize Data Exclusively with T-SQL.

Challenge #2 SQL Report Builder

DBA’s are often asked to build reports on the go. It really annoys DBA’s, but hardly people care about it. No matter how busy a DBA is, they are just called upon to build reports on things on very short notice. I personally like to avoid any task which is given to me accidently and personally building report can be boring. I rather spend time with High Availability, disaster recovery, performance tuning rather than building report. I use SQL third party tool when I have to work with SQL Report. Others have extended reporting capabilities. The latter group of products includes the SQL report builder built-in todbForge Studio for SQL Server. I have blogged about this earlier over here: SQL SERVER – SQL Report Builder in dbForge Studio for SQL Server.

Challenge #3 Work with the OTHER Database

The manager does not understand that MySQL is different from SQL Server and SQL Server is different from Oracle. For them everything is same. In my career hundreds of times I have faced a situation that I am given a database to manage or do some task when their regular DBA is on vacation or leave. When I try to explain I do not understand the underlying the technology, I have been usually told that my manager has trust on me and I can do anything. Honestly, I can’t but I hardly dare to argue. I fall back on the third party tool to manage database when it is not in my comfort zone. For example, I was once given MySQL performance tuning task (at that time I did not know MySQL so well). To simplify search for a problem query let us use MySQL Profiler in dbForge Studio for MySQL. It provides such commands as a Query Profiling Mode and Generate Execution Plan. Here is the blog post discussing about the same: MySQL – Profiler : A Simple and Convenient Tool for Profiling SQL Queries.

Well, that’s it! There were many different such occasions when I have been saved by the tool. May be some other day I will write part 2 of this blog post.

Reference: Pinal Dave (

SQL SERVER – Migration Assistant Upgraded to Support SQL Server 2014

We all start somewhere when it is about database. There are different reasons, why we go for one database over another database. Usually the reason is cost and convenience. After a period of time when business is successful and traffic is growing, the same two reasons of cost and convenience start to become secondary goals. I have seen quite a lot of companies starting with free databases and after a while switching to another database as they want stability and service from the product company.

Microsoft has an excellent product which lets you migrate your database from the alternate database to SQL Server. It is called SQL Server Migration Assistant (SSMA) and earlier this week, it has been upgraded to support SQL Server 2014. Now you can migrate from your database to to all editions of SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014. SQL Server Migration Assistant (SSMA) is a free supported tool from Microsoft.

Here is where you can download SSMA v5.3 for various databases.

Microsoft SQL Server Migration Assistant v5.3 for Access
Microsoft SQL Server Migration Assistant (SSMA) for Access is a tool to automate migration from Microsoft Access database(s) to SQL Server

Microsoft SQL Server Migration Assistant v5.3 for Oracle
Microsoft SQL Server Migration Assistant (SSMA) for Oracle is a tool to automate migration from Oracle database to SQL Server.

Microsoft SQL Server Migration Assistant v5.3 for Sybase
Microsoft SQL Server Migration Assistant (SSMA) for Sybase is a tool to automate migration from Sybase ASE database to SQL Server.

Microsoft SQL Server Migration Assistant v5.3 for MySQL
Microsoft SQL Server Migration Assistant (SSMA) for MySQL is a tool to automate migration from MySQL database to SQL Server.

Reference: Pinal Dave (

MySQL – Beginning Temporary Tables in MySQL

MySQL supports Temporary tables to store the resultsets temporarily for a given connection. Temporary tables are created with the keyword TEMPORARY along with the CREATE TABLE statement.

Let us create the temporary table named Temp

(id INT);

Now you can find out the column names using DESC command


The above returns the following result

This table can be accessed only for the current connection and it can be used like a permanent table and automatically dropped when the connection is closed. However, you can not find temporary tables using INFORMATION_SCHEMA. TABLES system view. It will only list out the permanent tables.

MySQL usually stores the data of temporary tables in memory and processed by Memory Storage engine. But if the data size is too large MySQL automatically converts this to the on – disk table and use MyISAM engine.

You can also create a permanent table with the same name of a temporary table in the same connection. However the structure of permanent table is visible only if the temporary table with the same name is dropped.

Let us create a permanent table with the same name Temp as below

(id INT,
names VARCHAR(100));

Now running the following command stills gives you the structure of the temporary table temp created earlier.


You can drop the temporary table using DROP TEMPORARY TABLE command;


After you executed the temporary table, run the following command


Now you will see the structure of the permanent table named temp

In summary – If there is a Temporary Table in MySQL it gets first priority over the permanent table in the session.

Reference: Pinal Dave (

SQLAuthority News – Great Time Spent at Great Indian Developers Summit 2014

The Great Indian Developer Conference (GIDS) is one of the most popular annual event held in Bangalore. This year GIDS is scheduled on April 22, 25. I will be presented total four sessions at this event and each session is very different from each other. Here are the details of four of my sessions, which I presented there.

Pluralsight Shades

This event was a great event and I had fantastic fun presenting a technology over here. I was indeed very excited that along with me, I had many of my friends presenting at the event as well. I want to thank all of you to attend my session and having standing room every single time. I have already sent resources in my newsletter. You can sign up for the newsletter over here.

Indexing is an Art

I was amazed with the crowd present in the sessions at GIDS. There was a great interest in the subject of SQL Server and Performance Tuning.

Audience at GIDS

I believe event like such provides a great platform to meet and share knowledge.

Pinal at Pluralsight Booth

Here are the abstract of the sessions which I had presented. They were recorded so at some point in time they will be available, but if you want the content of all the courses immediately, I suggest you check out my video courses on the same subject on Pluralsight.

Indexes, the Unsung Hero

Relevant Pluralsight Course

Slow Running Queries are the most common problem that developers face while working with SQL Server. While it is easy to blame SQL Server for unsatisfactory performance, the issue often persists with the way queries have been written, and how Indexes has been set up. The session will focus on the ways of identifying problems that slow down SQL Server, and Indexing tricks to fix them. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session.

Indexes are the most crucial objects of the database. They are the first stop for any DBA and Developer when it is about performance tuning. There is a good side as well evil side to indexes. To master the art of performance tuning one has to understand the fundamentals of indexes and the best practices associated with the same. We will cover various aspects of Indexing such as Duplicate Index, Redundant Index, Missing Index as well as best practices around Indexes.

SQL Server Performance Troubleshooting: Ancient Problems and Modern Solutions

Relevant Pluralsight Course

Many believe Performance Tuning and Troubleshooting is an art which has been lost in time. However, truth is that art has evolved with time and there are more tools and techniques to overcome ancient troublesome scenarios. There are three major resources that when bottlenecked creates performance problems: CPU, IO, and Memory. In this session we will focus on High CPU scenarios detection and their resolutions. If time permits we will cover other performance related tips and tricks.
At the end of this session, attendees will have a clear idea as well as action items regarding what to do when facing any of the above resource intensive scenarios. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session. To master the art of performance tuning one has to understand the fundamentals of performance, tuning and the best practices associated with the same. We will discuss about performance tuning in this session with the help of Demos.

Pinal Dave at GIDS

MySQL Performance Tuning – Unexplored Territory

Relevant Pluralsight Course

Performance is one of the most essential aspects of any application. Everyone wants their server to perform optimally and at the best efficiency. However, not many people talk about MySQL and Performance Tuning as it is an extremely unexplored territory. In this session, we will talk about how we can tune MySQL Performance. We will also try and cover other performance related tips and tricks. At the end of this session, attendees will not only have a clear idea, but also carry home action items regarding what to do when facing any of the above resource intensive scenarios. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session. To master the art of performance tuning one has to understand the fundamentals of performance, tuning and the best practices associated with the same. You will also witness some impressive performance tuning demos in this session.

Hidden Secrets and Gems of SQL Server We Bet You Never Knew

Relevant Pluralsight Course

SQL Trio Session!

It really amazes us every time when someone says SQL Server is an easy tool to handle and work with. Microsoft has done an amazing work in making working with complex relational database a breeze for developers and administrators alike. Though it looks like child’s play for some, the realities are far away from this notion. The basics and fundamentals though are simple and uniform across databases, the behavior and understanding the nuts and bolts of SQL Server is something we need to master over a period of time.

With a collective experience of more than 30+ years amongst the speakers on databases, we will try to take a unique tour of various aspects of SQL Server and bring to you life lessons learnt from working with SQL Server. We will share some of the trade secrets of performance, configuration, new features, tuning, behaviors, T-SQL practices, common pitfalls, productivity tips on tools and more.

This is a highly demo filled session for practical use if you are a SQL Server developer or an Administrator. The speakers will be able to stump you and give you answers on almost everything inside the Relational database called SQL Server.

I personally attended the session of Vinod Kumar, Balmukund Lakhani, Abhishek Kumar and my favorite Govind Kanshi.


If you have missed this event here are two action items 1) Sign up for Resource Newsletter 2) Watch my video courses on Pluralsight

Reference: Pinal Dave (

MySQL – How to Create Stored Procedure in MySQL

MySQL supports Stored Procedures which can execute set of codes by applying relevant business logics. In this post we will see how to create a stored procedure and execute it.

Let us create the following tables

CREATE TABLE items(item_id INT, item_description VARCHAR(100));
CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));
INSERT INTO items VALUES (1,'Television');
INSERT INTO items VALUES (2,'Mobile');
INSERT INTO items VALUES (3,'laptop');
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-01-01',1200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-01-02',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-09',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-29',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-11',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-16',1200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-16',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-22',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-24',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-24',1200);

Suppose you want to create a stored procedure which will accept item_description and returns the total sales_amount for the enire period, you can do it as shown below

CREATE PROCEDURE Test.usp_get_sales
param_item_description VARCHAR(100)
item_description,SUM(sales_amount) AS sales_amount FROM items NATURAL
JOIN sales
WHERE item_description=param_item_description
GROUP BY item_description;

Note that the creation of the stored procedure starts with setting the Delimiter $$. The default delimiter for MySQL statements are semicolon so in order to instruct the MySQL engine about the start and end of the stored procedure block, you need to use a different delimiter (which in this case $$ is used).

Now you can execute a stored procedure using CALL keyword as shown below.

Execution 1

CALL usp_get_sales('Television');

When you execute the above code, the result is

Item_description sales_amount
Television 3600.00

Execution 2

CALL usp_get_sales('laptop');

When you execute the above code, the result is

Item_description sales_amount
laptop 6800.00

Note: The parameters do not start with @ like we use in SQL Server. So in order to differentiate between the actual column name and parameter name, the name param_item_description is used. You may need to use different naming conventions as you like.

I have previously written a similar article here: MySQL – How to Create Stored Procedure.

Reference: Pinal Dave (