Learning MySQL 10X Faster

MySQL Introduction

MySQL is the world’s most open-source relational database management system. 20+Million production instances and daily 65000 daily downloads. It Becomes Popular because of its simplicity, effective architecture. It has used by the top 9 sites out the top 10. Facebook, YouTube, Google, Yahoo, Twitter, LinkedIn, Uber, Ola, NASA, Tesla, Swiggy, Adobe, Booking.com, Github and many more top branded companies are using MySQL Source code is open and many companies are adding new features on top of MySQL code.

Also, developing tools for word the best DB HA solutions, connection pooling, DB proxy routing. MySQL is best to choose if your company is a start-up and not enough budget, for Academic projects, and if your project doesn’t have DB Expert. MySQL supports SQL and NoSQL and also we can save OLAP and OLTP data in the same instance.

Next Generation GUI Tools

Many companies have developed next-generation tools for MySQL because of its popularity. My Favorite tool called SmartWorkbench and it is Smartmysql.com product. Using this tool you can learn MySQL 10X faster, and it seeps your SQL development more than 10X faster, you can optimize your SQL queries with your fingertips and speedup more than 100X, and debug MySQL real production issues and fix them even if you are not MySQL Expert.

In this article, I am going to explain how to learn MySQL 10X faster using the SmartWorkbench tool. You can download the SmartWorkbench from www.smartmysql.com/downloads page. It works on all platforms. Once you download and install then you can connect you DB with MySQL host, user, password, and port. After you connected your database then it displays all databases at your left-hand side and there is SQL query browse on the right side.

How to Speed your SQL Development?

The SmartMySQL’s Workbench Artificial algorithm generates your SQL queries with simple table drag & drop options and preloaded SQL templates.

• Drag a table from the left-hand tree to the Query browser then it generates basic Query.

SELECT * FROM EMP;

• Type S or SELECT then drag table then it generates a basic query with column names in the SELECT clause.

SELECT FName,LName,Salary,did FROM EMP;

• Drag any column then it generates SELECT Query with where clause with the dragged column. Ex if you drag FName from EMP table then it generates

SELECT * from EMP where FName ='';

• Type S or SELECT then Drag column after S letter or SELECT keyword then it generates

SELECT FName, LName, Salary,did FROM EMP WHERE FName=''; 

• Drag another table from the same table then it will be adding the column on which clause you drop. Ex: if you drag second column LName on the query WHERE clause then it generates the following query

 SELECT FName, Lname, Salary,did FROM EMP WHERE FName='' AND LName=''; 

• Drag the second table into the current query then it builds JOIN condition. Ex: If you drag DEP table then it generates the following Query

SELECT FName, Lname, Salary,did, d.* FROM EMP e JOIN DEP d on e.did=d.did WHERE FName='' and LName='';

• It also works for DML command. You just need to type I or INSERT then drag table it will generate INSERT command for the table and you just change values

INSERT INTO EMP( FName, Lname, Salary,did) values ( 'FName', 'Lname', 'Salary' ,'did')

• Type U or UPDATE then drag table after U letter or UPDATE keyword then it generates UPDATE command.

UPDATE EMP SET  FName='', Lname='', Salary='', did='' where ID='';

• Type U or UPDATE then drag a column from the table after U letter or UPDATE keyword then it generates UPDATE query with where Clause with the dragged column. Ex if you Drag FName from EMP table

 UPDATE EMP SET  FName='', Lname='', Salary='', did='' where FName=''; 

• Type D or DELETE then drag table after D letter or DELETE keyword then it generates DELETE command

DELETE FROM EMP where ID='';

• Type D or DELETE then drag a column from the table after D letter or DELETE keyword then it generates DELETE query with where Clause with the dragged column. Ex if you Drag FName from EMP table

DELETE FROM EMP where FName='';

How does it work Multiple Tables?

SELECT, UPDATE and DELETE commands can write with multiple Tables Using JOIN Conditions. If you drag a new table into the existing Query then SmartMySQL Workbench identifies JOIN condition with new table and generates new Query with new table with JOIN condition. It Just adds JOIN condition in the query within a fraction of seconds and it won’t change anything in the existing query. You can JOIN any number of tables based on your requirement it generates JOIN condition. It will work even if those tables don’t have a Foreign key relation.

How does it work for the WHERE clause?

We can write SELECT, UPDATE & DELETE SQL commands with WHERE clause. If you drag a table into new like then it generates Query with WHERE Clause. IF you drag one more table from the same table then the new column will add in where clause with “AND” operator. Ex. If you drag FName after U then it generates the following UPDATE

 UPDATE EMP SET  FName='', LName='', Salary='', did='' where FName=''; 

Then if you drag LName then it generates the following UPDATE

 UPDATE EMP SET  FName='', LName='', Salary='', did='' where FName='' and LName=''; 

You can add as many columns you want and if you need OR operator then you need to change manually. Also, Need to remove unwanted columns in the SET clause and fill proper values in empty single quotes.

DDL commands

You type CT or _createTable then there is a loaded SQL template and it will replace the following Create table and you just need to change table name and column names and if you need more columns you need to add.

CREATE TABLE TABLE_NAME
 (
     id bigint UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
     Column1Name Varchar(50) not null, 
     Column2Name Varchar(50) not null, 
     /* add columns  */
     CreateAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
     UpdateAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 Key Idx_CreateAt(CreateAt) /* FOREIGN KEY (Column1Name) REFERENCES ReferenceTable(Column1Name) ON UPDATE CASCADE ON DELETE CASCADE */ );

It is one of the fast ways to create a table. Even Smart MySQL Workbench providing good GUI to creating a table and ALTER table.

If you Type DROP keyword then drop table then it generates DROP command

DROP TABLE EMP;

If you Type TRUNCATE keyword then drop table then it generates TRUNCATE command.

 TRUNCATE TABLE TABLE_NAME

There are many features in SmartWorkbench

• Syntax error highlights in the exact location
• SQL Query Optimization and improve query response more than 100X
• Fixing Deadlock issues
• Fixing Query Locking
• Fixing Replication lagging
• SP code generator
• Trigger code generator for audit tables
• Smart auto complete
• Query beautifier
• MySQL Slow log analyzer
• Reuse historical, Frequently executing and saved favorite queries with simple short cuts (Ctrl+Shift+H /F/Q)

Start Download SmartMySQL. It has a Free version.