MySQL – List User Defined Tables – Two Methods

There are different methods to list out user defined tables in MySQL. We will see two methods in this post.

Let us create the following tables in the database named Test

Create a database named Test

CREATE DATABASE Test;

Create the tables named items and sales in the database Test.

USE TEST;
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));

Now to list out all user defined tables, use any one of the following methods

Method 1: Use SHOW TABLES command

SHOW TABLES;

If you execute the above statement, it will list out all the user defined tables in the database in which it is run (in this example the database name is Test)

The result is

Tables_in_test
 --------------
 items
 sales

You can also use database name along with this

SHOW TABLES FROM Test;

Which will give you the same result

Method 2: Use INFORMATION_SCHEMA view

You can make use of ANSI standard INFORMATION_SCHEMA.TABLES
view to do the same shown as below

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='Test';

The result is

Tables_in_test
 --------------
 items
 sales

This is a very small trick but works great when you want to know what are different tables exist in MySQL Database.

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

About these ads

MySQL – How to do Straight Join in MySQL?

In an earlier post, we have learnt a new type of join called NATURAL JOIN supported in MySQL. In this post will see another type of join called STRAIGHT_JOIN.

STRAIGHT_JOIN is actually an INNER JOIN but it forces the order in which data should be processed.

As per MySQL’s documentation at, the definition is given below

“STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.”

Let us create the following data

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);

STRAIGHT_JOIN can be used in two methods

Method 1 Join tables using STRAIGHT_JOIN

SELECT t1.item_description,t2.sales_id,t2.sales_date,t2.sales_amount
FROM sales AS t2 STRAIGHT_JOIN items AS t1  ON t1.item_id=t2.item_id;

Method 2 Use INNER JOIN to join tables and use STRAIGHT_JOIN in the SELECT clause itself

SELECT STRAIGHT_JOIN t1.item_description,t2.sales_id,t2.sales_date,t2.sales_amount
FROM sales AS t2 INNER JOIN items AS t1  ON t1.item_id=t2.item_id;

Both the queries produce the following result

item_description sales_id sales_date sales_amount
 Television 1 1/1/2014 0:00 1200
 Mobile 2 1/2/2014 0:00 200
 laptop 3 1/9/2014 0:00 1700
 laptop 4 1/29/2014 0:00 1700
 laptop 5 2/11/2014 0:00 1700
 Television 6 2/16/2014 0:00 1200
 Mobile 7 2/16/2014 0:00 200
 Mobile 8 2/20/2014 0:00 200
 Mobile 9 2/20/2014 0:00 200
 Mobile 10 2/22/2014 0:00 200
 laptop 11 2/24/2014 0:00 1700
 Television 12 2/24/2014 0:00 1200

However the real test is when we execute the STRAIGHT_JOIN syntax with EXPLAIN command before SELECT statement and observe the order of table joined.

Method STRAIGHT JOIN

EXPLAIN SELECT STRAIGHT_JOIN t1.item_description,t2.sales_id,t2.sales_date,t2.sales_amount
FROM sales AS t2 INNER JOIN items AS t1 ON t1.item_id=t2.item_id;

Now let us see the result where we observe the order of tables used in Join.

You will notice that Sales Table which is aliased as t2 is first used in the result set.

Method INNER JOIN (without Straight Join)

EXPLAIN SELECT t1.item_description,t2.sales_id,t2.sales_date,t2.sales_amount
FROM sales AS t2 INNER JOIN items AS t1 ON t1.item_id=t2.item_id;

Now let us see the result where we observe the order of tables used in Join.

You will notice that Sales Table which is aliased as t2 is second used in the result set. That means MySQL have used internal optimizer and re-ordered the table order for maximum performance.

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

SQLAuthority News – Learn MySQL Indexing in 99 Minutes – MySQL Indexing for Performance – Video Course

Every year around September 1st, I have decided to do something cool. This is because September 1st is my daughter Shaivi’s birthday. In previous years, I have released my new books as well new courses on this day.  This year I have done something similar for her. I like to work hard and dedicate my efforts to my family as they are the one who provides me unconditional support to do various activities for the community.

Journey to Learn MySQL Indexing in 99 Minutes

Indexes are considered to be sure solution for Performance tuning but it has two sides of the story. A good index can improve performance of your query and bad index can degrade the performance of the query. I have build a course around MySQL indexing where I explain how to create indexes which improves the performances of the MySQL Query by many folds. Indexing is such a subject that I can talk about this subject for many days and still I will have more material to share. However, I had decided to build a course which is very sharp and to the point. I wanted to make sure that with the help of this course one can learn all the major points of MySQL indexes in very short period of the time.

While I was building this course – I had put a threshold that I will not build a very long boring course. I wanted to make sure that I build a crisp course which addresses the need of the hour and teach the most efficient methods to improve performance of the query. It took me more than 3 weeks to build all the material I wanted to cover in this course. Once I have build the material the real challenge was on, I wanted to make sure that I build a course which is basic enough for a novice to understand but advanced enough that an expert can learn various tricks as well. I wanted to balance the course for every level as well as I wanted to make sure that the pace of the course is good enough for everyone to keep up with it.

Finally, after long hours and many days of work, I finalized the content of the course which just is the right fit for everybody – a novice who does not know anything about index as well an expert who already have a clear idea about the index.  I had pretty much mapped out every single minute of the course with the demo and slide. As per my estimate the course should have not gone over 100 minutes. When I was done with the course, I was extremely delighted as the entire course was recorded in 99 minutes – YES, only 99 minutes. I have previously recorded many courses but this course was built with perfection in the seconds of the time.

You can learn MySQL Performance Tuning with the help of Indexing in 99 minutes.

Course Content

Here is the course outline which I have built to learn MySQL Indexing. You can watch this course by creating free trial at Pluralsight. If you do not see the play button to the watch the course, please login to the Pluralsight with your valid credentials.

  • MySQL Indexing for Performance
    • Introduction
  • Types of Indexes
    • Introduction
    • InnoDB vs MyISAM
    • B-Tree Index
    • Advantages of B-Tree Index
    • Clustered Index
    • Advantages of Clustered Index
    • Disadvantages of Clustered Index
    • Secondary Index
    • Building Clustered B-Tree
    • Building Secondary Index B-Tree
    • InnoDB Clustered Table
    • Hash Index
    • Limitation of Hash Index
    • Adaptive Hash Index
    • Building Hash Index
    • Other Indexes
    • Summary in Sixty Seconds
  • Indexing Strategies for High Performance
    • Introduction
    • Effectiveness of Index
    • Demo: List Indexes
    • Demo: Basics of Indexes
    • Demo: Order of Columns in Index
    • Demo: Optimize OR Conditions
    • Demo: Optimize OR Conditions – Advanced
    • Demo: Optimize AND Conditions
    • Demo: Optimize AND Conditions – Advanced
    • Demo: Cover Index
    • Demo: Clustered Index
    • Demo: Index Hints
    • Summary in Sixty Seconds
  • Index Maintenance
    • Introduction
    • MySQL Query Optimizer
    • Statistics
    • InnoDB Statistics
    • Data and Index Fragmentation
    • Index Maintenance
    • Other Index Maintenance Tips
    • Summary in Sixty Seconds
  • Checklists
    • Checklists

Watch Video Course

Click here to watch the MySQL Indexing for Performance video course.

As an author of this course, I have few free codes to watch this course. Please leave a comment to watch this course and I will send a few of the readers a free card to watch this course. You can also request the free code on my Facebook page as I am very active there.

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

MySQL – Learning MySQL Online in 6 Hours – MySQL Fundamentals in 320 Minutes

MySQL is one of the most popular database language and I have been recently working with it a lot. Data have no barrier and every database have their own place. I have been working with MySQL for quite a while and just like SQL Server, I often find lots of people asking me if I have a tutorial which can teach them MySQL from the beginning. Here is the good news, I have written two different courses on MySQL Fundamentals, which is available online.

The reason for writing two different courses was to keep the learning simple. Both of the courses are absolutely connected with other but designed if you watch either of the course independently you can watch them and learn without dependencies. However, if you ask me, I will suggest that you watch MySQL Fundamentals Part 1 course following with MySQL Fundamentals Part 2 course.

Let us quickly explore outline of MySQL courses.

MySQL Fundamental – 1 (157 minutes)

MySQL is a popular choice of database for use in web applications, and is a central component of the widely used LAMP open source web application software stack. This course covers the fundamentals of MySQL, including how to install MySQL as well as written basic data retrieval and data modification queries.

  • Introduction (duration 00:02:12)
  • Installations and GUI Tools (duration 00:13:51)
  • Fundamentals of RDBMS and Database Designs (duration 00:16:13)
  • Introduction MYSQL Workbench (duration 00:31:51)
  • Data Retrieval Techniques (duration 01:11:13)
  • Data Modification Techniques (duration 00:20:41)
  • Summary and Resources (duration 00:01:31)

MySQL Fundamental – 2 (163 minutes)

MySQL is a popular choice of database for use in web applications, and is a central component of the widely used LAMP open source web application software stack. In this course, which is part 2 of the Fundamentals of MySQL series, we explore more advanced topics such as stored procedures & user-defined functions, subqueries & joins, views and events & triggers.

  • Introduction (duration 00:02:09)
  • Joins, Unions and Subqueries (duration 01:03:56)
  • MySQL Functions (duration 00:36:55)
  • MySQL Views (duration 00:19:19)
  • Stored Procedures and Stored Functions (duration 00:25:23)
  • Triggers and Events (duration 00:13:41)
  • Summary and Resources (duration 00:02:18)

Note if you click on the link above and you do not see the play button to watch the course, you will have to login to the system and watch the course.

I would like to throw a challenge to you – Can you watch both of the courses in a single day?

If yes, once you are done watching the course on your Pluralsight Profile Page (here is my profile http://pluralsight.com/training/users/pinal-dave) you will get following badges. If you have already watched MySQL Fundamental Part 1, you can qualify by just watching MySQL Fundamental Part 2.

Just send me the link to your profile and I will publish your name on this blog. For the first five people who send me email at Pinal at sqlauthority.com; I might have something cool as a giveaway as well.

Watch the teaser of MySQL course.

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