MySQL – How to Connect to MySQL Server on Command Prompt

I have so far authored 5 courses on MySQL at Pluralsight, and one of the most popular question, I keep on getting is how to connect to MySQL with Command Prompt. You may find it funny enough that people who are learning MySQL would know how to connect to command prompt, but trust me, we all start from somewhere and we do not have to be an expert at everything. I respect every single question and I would like to answer this very question over here.

Step 1: Go to the folder where the MySQL exe file is located

For example, in my machine the location of the MySQL execution file is at “C:\Program Files\MySQL\MySQL Server 5.6\bin”. I changed the context of the command prompt to the folder where the MySQL exe file is located.

Step 2: Enter following commands in the command prompt

mysql -h nameofthehost -u username -p

In the above command, replace nameofthehost with actual host name and username with actual username.  Now click on enter.

When clicked enter, it will ask for the password. Enter the password and click on enter.

It will connect you with MySQL Server.

Well – that’s it! You are connected to MySQL Server. Once you connect to MySQL Server, do not forget to continue watching MySQL Basics and Performance Tuning courses.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Does Use of CTE Change the Order of Join in Inner Join

I just had an interesting conversation the day before when I was discussing about Join Order in one of my recent presentations.

The comment which triggered all the conversation was “If I want to change the order of how tables are joined in SQL Server, I prefer to use CTE instead of Join Orders”. 

During the conversation user was suggesting that he wanted his tables to be joined in certain ways in SQL Server but SQL Server Engine Optimizer was re-organizing everything to give the most optimal performance. His needs were to join tables in certain ways and did not care about the performance. To join tables as per his need he had to use a FORCE ORDER hint of the table. However, he recently learned about CTE and believed that CTE will help him to achieve his tasks without the help of FORCE ORDER hint.

He believed that as CTE syntax is constructed before the SELECT statement SQL Server will build a result set instead first and will use the same resultset to join the SELECT statement following the CTE. Well, the casual conversation converted to debate and it was getting very difficult as everybody started to express their opinion with very loud voice. Finally, we decided to run an example on my machine.

NOTE: The example uses INNER JOIN only and the conclusion applies to only INNER JOIN. In the case of OUTER JOIN there is an entire different story, we will cover that in future blog posts.

We created two examples and executed them one by one. Once we executed them, we compared their resultant - they were identical. Right after that we compared the order of the join for both the example.

Example 1: Without CTE

USE AdventureWorks2012
GO
SELECT c.CustomerID, p.BusinessEntityID
FROM Person.Person p
INNER JOIN Person.EmailAddress ea ON ea.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.PersonPhone ph ON ph.BusinessEntityID = p.BusinessEntityID
INNER JOIN Sales.PersonCreditCard pc ON pc.BusinessEntityID = p.BusinessEntityID
INNER JOIN Sales.Customer c ON c.CustomerID = p.BusinessEntityID;

Execution Plan:

Example 2: With CTE

USE AdventureWorks2012
GO
WITH MyCTE AS
(SELECT c.CustomerID
FROM Sales.PersonCreditCard pc
INNER JOIN Sales.Customer c ON c.CustomerID = pc.BusinessEntityID)
SELECT cte.CustomerID, p.BusinessEntityID
FROM Person.Person p
INNER JOIN Person.EmailAddress ea ON ea.BusinessEntityID = p.BusinessEntityID
INNER JOIN MyCTE cte ON cte.CustomerID = p.BusinessEntityID
INNER JOIN Person.PersonPhone ph ON ph.BusinessEntityID = p.BusinessEntityID;

Execution Plan:

When we compare both the execution plan, it is very clear that there is no change in the order of the table orders. Both the statements have a very similar execution plan and tables were joined in exactly the same order. The matter of the fact in the case of CTE, it was interesting to see that SQL Server started to join very first one table from CTE and another from SELECT statements and continued to build the entire execution plan.

The reason for this is very simple – CTE is not temporary table kind of object or feature. It is just an expression to represent your SELECT statement in such a way that it increases your readability and usability. They do not execute before the regular SELECT statement or build a result set before hand. It actually executes just like regular SELECT statement all together.

In summary: CTE does not impact Table Join Order when all Joins in the query are INNER JOIN. 

Here is one of the my favorite videos on this subject:

http://www.youtube.com/watch?v=Z7VuYBXX2dg

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Turning On Graphical Execution Plan After Enabling ShowPlan Text

This may be surprising to many, but I have seen quite a few times so far so decided to blog over here.

Here is the sequence of the action:

  1. The developer turns on Graphical Execution plan for any query with CTRL+M
  2. Now turns on the execution plan in the text format with SET SHOWPLAN_TEXT ON command
  3. After this, the developer does his/her task to analysis the execution plan
  4. Now turns on the execution plan in the XML format with SET SHOWPLAN_XML ON command

Now when a developer has to turn back Graphical Execution Plan, he/she gets confused. We know that we can turn on TEXT and XML with the help of SET commands, but how to turn on graphical execution plans from T-SQL. Well, the matter of facts is there is no way to turn the graphical execution plan from T-SQL.

The only way to get the Graphical Execution plan back in SQL Server Management Studio (SSMS) is to turn off TEXT and XML plan and it will automatically turn on the graphical execution plan.

For example, in above situation when we have turned off TEXT and XML plan, set them off as described in the following steps

  1. Turn off XML execution plan with SET SHOWPLAN_XML OFF command
  2. Turn off TEXT execution plan with SET SHOWPLAN_TEXT OFF command

It will automatically turn on the graphical execution plan. Remember, if you have turned off either of XML or TEXT, you will need to turn off that one only. However, if you have turned on both of them, you will have to turn off both of them to get back your graphical execution plan.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Professional Development – Dr W. Edwards Deming’s 14 Principles on Total Quality Management

I was just reading Dr. W. Edwards Demings 14 principles of Total Quality Management. It is indeed very impressive and interesting. I have tried to collect a few of the important resources related to the same over here.

Dr. Demings’s 14 principles

  1. Create a constant purpose toward improvement
  2. Adopt the new philosophy
  3. Cease dependence on mass inspection
  4. Use a single supplier for any one item
  5. Improve every process
  6. Create training on the job
  7. Adopt and institute leadership aimed at helping people do a better job
  8. Drive out fear
  9. Break down barriers between departments
  10. Get rid of unclear slogans
  11. Eliminate arbitrary numerical targets
  12. Permit pride of workmanship
  13. Implement education and self-improvement
  14. Make transformation everyone’s job

Here are few other interesting resources related to Dr. W Edwards Demings

  • Wikipedia page (Link)
  • Original White Paper with 14 Key Principles (Link)
  • Original Website of Demings Institute (Link)
  • SlideShare PPT (Link)

Here is the Official YouTube channel of Demings Institute. They have excellent videos and I strongly encourage everyone to view them.  Additionally, here is the famous Deming’s Red Bead Experiment Video.

Before I end the post I would like to include the video of Daniel Pink who has authored books Drive, which is very motivational book and often time I felt it resonated with the same message as Dr. Demings. This video is from TED presentation of Dan where he discussed The Puzzle of Motivation.

http://www.youtube.com/watch?v=rrkrvAUbU9Y

Reference: Pinal Dave (http://blog.sqlauthority.com)

MySQL – Search For Values Within A Comma Separated Values – FIND_IN_SET

MySQL has an inbuilt function called FIND_IN_SET which will search for values within a comma separated values. It basically returns the index position of the first parameter within the second parameter. This can be alternatively used to replace the IN clause.

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 find out all sales made by Mobile and laptop. We can use IN clause as below
SELECT * FROM items NATURAL JOIN sales
WHERE item_description IN ('Mobile','laptop');

However you can also use FIND_IN_SET function to acheive the same

SELECT * FROM items NATURAL JOIN sales
WHERE FIND_IN_SET (item_description,'Mobile,laptop');

FIND_IN_SET accepts two parameters (first is column and second is column values seperated by commas).

Both the queries produce the following result

2 Mobile 2 2014-01-02 00:00:00 200.00
 3 laptop 3 2014-01-09 00:00:00 1700.00
 3 laptop 4 2014-01-29 00:00:00 1700.00
 3 laptop 5 2014-02-11 00:00:00 1700.00
 2 Mobile 7 2014-02-16 00:00:00 200.00
 2 Mobile 8 2014-02-20 00:00:00 200.00
 2 Mobile 9 2014-02-20 00:00:00 200.00
 2 Mobile 10 2014-02-22 00:00:00 200.00
 3 laptop 11 2014-02-24 00:00:00 1700.00

Well, technically it does not matter which option we use both the solution produces almost same results and performance, it is just an alternate way to search for values within a comma separated values.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Index Fragmentation Next Steps – Notes from the Field #020

[Notes from Pinal]: Indexes are considered as a silver bullet to performance tuning and do amazing job when they are properly created. Just like any other engine they need maintenance and tuning. As we get more data indexes start to get fragmented and performance starts to degrade. I asked direct questions to my friend Tim about fragmentation and he gave me a very interesting answer.

Linchpin People are database coaches and wellness experts for a data driven world. In this 20th episode of the Notes from the Fields series database expert Tim Radney (partner at Linchpin People) shares very interesting conversation with me. I asked:

Pinal: “Tim, what would you do if your indexes gets fragmented?”


When analyzing systems I often find that little maintenance is being performed. Although proper backups are my number one priority when checking out a system, index maintenance is also very important.  I have a script that I run that check for index fragmentation and very often I find extremely high fragmentation levels for most of the tables in the database.

Finding high fragmentation is very common and the fix is quite easy. I simply have to apply a maintenance script to clean up the fragmentation.  The path I take can vary depending on the size of the database and the edition of SQL being ran.  Rebuilding indexes on standard edition will have a vastly different impact, then an online index rebuild on enterprise edition.  I usually take a surgical approach on a large database with very high levels of fragmentation until I get the fragmentation under control. Once the indexes are at a more manageable level of fragmentation I allow the script to keep things in check.

There are numerous products on the market that can help and there are free products as well.  Microsoft even includes an option within Database Maintenance plans; however, I personally use Ola Hallengren’s http://ola.hallengren.com/scripts for Index maintenance and for updating statistics.  Ola also includes a comprehensive backup solution, but unless the client needs a complete overhaul I typically don’t make changes there.

The great thing about this solution is the ability to rebuild when needed and reorganize when needed. You get to choose which option based on the level of fragmentation.   This solution gives you a great deal of flexibility with how it is run.  When running index maintenance on a system that is highly fragmented for the first time, even with the online options, be careful to monitor your transaction log size.  You will also need to have extra space in your data file for index rebuilds as well.

Regardless of which solution you choose for your index maintenance, the important thing is that you have a proper solution in place to deal with fragmentation.  What causes fragmentation in the first place, good for starters inserts, updates, and deletes.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Note: Tim has also written an excellent book on SQL Backup and Recovery, a must have for everyone.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Retrieve Last Inserted Rows from Table – Question with No Answer

Some conversations are very interesting and I would like to bring them to everyone’s attention. Here is one of the conversation happened earlier this week. Let us say the question was asked by Dustin.

Dustin: Can we retrieve last 10 rows from a table?

Pinal: Sure, just get the top rows from the end of the table. For this you will have to order table with Identity Column Descending.

Dustin: I do not have an identity column in table.

Pinal: No problem. Do you have a column which tracks the date and time for the newly inserted row or timestamp?

Dustin: No such thing in my table.

Pinal: Hmmm… That means there is no property which can identify if the rows were inserted last or first.

Dustin: I believe so. Any other thoughts?

Pinal: Well, is there any auditing on the table or any trigger or any other mechanism where you store your data to another table for example with an OUTPUT clause?

Dustin: No, nothing like that. Is there any administrative ways to get the last inserted row?

Pinal: What do you mean?

Dustin: Can I read the logs and know what were the rows inserted?

Pinal: I have personally never tried to read log files and I have no clue how to do that. If you are going do that, I suggest you take backup of your log file and do the experiment on it. There are good chances if you use tool which is not safe you will get your database at very high risk.

Dustin: Do you recommend any tool?

Pinal: No, I do not recommend any tool as of now. If I will find one, I will write a blog post.

Note: Please do not post a comment with third party tools link, I will delete them without notice. I will be doing my research soon and will post a blog about it.

Reference: Pinal Dave (http://blog.sqlauthority.com)