Tag: mysql

MySQL – Database Optimization

Posted on May 1, 2020 in Feature Questions

Database Optimization

Database optimization refers to a variety of strategies for reducing database system response time. In this guide, We will take you through the steps of optimizing SQL queries and databases.

#1: Index All Columns Used in ‘WHERE’, ‘ORDER BY’ and ‘GROUP BY’ Clauses

Apart from guaranteeing uniquely identifiable records, an index allows MySQL server to fetch results faster from a database. An index is also very useful when it comes to sorting records.

MySQL indexes may take up more space and decrease performance on inserts, deletes and updates. However, if your table has more than 10 rows, they can considerably reduce select query execution time.

#2: Optimize Like Statements with Union Clause

Sometimes, you may want to run queries using the comparison operator ‘OR‘ on different fields or columns in a particular table. When the ‘OR‘ keyword is used too much in WHERE clause, it might make the MySQL optimizer to incorrectly choose a full table scan to retrieve a record.

A UNION clause can make the query run faster especially if you have an index that can optimize one side of the query and a different index to optimize the other side.

#3: Avoid Like Expressions with Leading Wildcards

MySQL is not able to utilize indexes when there is a leading wildcard in a query. If we take example on the students table, a search like this will cause MySQL to perform full table scan even if you have indexed the ‘first_name’ field on the students table.

#4: Take Advantage of MySQL Full-text Searches

If you are faced with a situation where you need to search data using wildcards and you don’t want your database to under perform, you should consider using MySQL full-text search (FTS) because it is far much faster than queries using wildcard characters.
Furthermore, Full Text Search can also bring better and relevant results when you are searching a huge database.

#5: MySQL Query Caching

If your website or application performs a lot of select queries (e.g. WordPress), you should take advantage of MySQL query caching feature. This will speed up performance when read operations are conducted.

#6: Avoid unnecessary columns in SELECT clause

Instead of using ‘SELECT *’, always specify columns in the SELECT clause to improve MySQL performance. Because unnecessary columns cause additional load on the database, slowing down its performance as well whole systematic process.

#7: Use INNER JOIN, instead of OUTER JOIN if possible

Use OUTER JOIN only when it is necessary. Using it needlessly not only limits database performance but also limits MySQL query optimization options, resulting in slower execution of SQL statements.

#8: Use DISTINCT and UNION only if it is necessary

Using UNION and DISTINCT operators without any major purpose causes unwanted sorting and slowing down of SQL execution. Instead of UNION, using UNION ALL brings more efficiency in the process and improves MySQL performance more precisely.

#9 : Use DISTINCT instead of GROUP BY when appropriate

If one of them is faster, it’s going to be DISTINCT. This is because, although the two are the same, a query optimizer would have to catch the fact that your GROUP BY is not taking advantage of any group members, just their keys. DISTINCT makes this explicit, so you can get away with a slightly dumber optimizer.

#10: Don’t Use MySQL as a Queue

Queues can really affect your database performance right from the core and could enter in your app databases without your knowledge. For instance, if you are setting up a status for particular item so that a ‘relevant process’ can access it, you are unintentionally creating a queue. What it actually does is that it builds up extra load time to access the resource without any major reason.

#11 : Avoid Unused Tables

No matter how carefully you plan the initial approach to the database, as time goes by, you inevitably end up with unused tables. Just do not hesitate: delete them! Having unused tables is quite similar to keeping things you don’t use in a full drawer. When you go looking for something, it can take you much longer to find it! The same thing happens in the databases: the systems have to track all the tables and elements until they find the answer to the query.

#12 : Use Optimal Data Types

MySQL supports different data types including integer, float, double, date, date_time, Varchar, and text, among others. When designing your tables, you should know that “shorter is always better.”

For instances, if you are designing a system user’s table which will hold less than 100 users, you should use ‘TINYINT’ data type for the ‘user_id’ field because it will accommodate all your values from -128 to 128.

Also, if a field expects a date value (e.g. sales_order_date), using a DATE_TIME data type will be ideal because you don’t have to run complicated functions to convert the field to date when retrieving records using SQL.

Use integer values if you expect all values to be numbers (e.g. in a student_id or a payment_id field). Remember, when it comes to computation, MySQL can do better with integer values as compared to text data types such as VARCHAR.

#13 : Avoid Null Values

Null is the absence of any value in a column. You should avoid this kind of values whenever possible because they can harm your database results. For instance, if you want to get the sum of all orders in a database but a particular order record has a null amount, the expected result might misbehave unless you use MySQL ‘ifnull‘ statement to return alternative value if a record is null.

In some cases, you might need to define a default value for a field if records don’t have to include a mandatory value for that particular column/field.

#14 : Avoid Too Many Columns

Wide tables can be extremely expensive and require more CPU time to process. If possible, don’t go above a hundred unless your business logic specifically calls for this.

Instead of creating one wide table, consider splitting it apart in to logical structures. For instance, if you are creating a customer table but you realize a customer can have multiple addresses, it is better to create a separate table for holding customers addresses that refer back to the customers table using the ‘customer_id’ field.

#15 : Optimize Joins

Always include fewer tables in your join statements. An SQL statement with poorly designed pattern that involves a lot of joins may not work well.

Conclusion

In this guide, we have shown you how to optimize your MySQL server for speed and performance.

This article provides in detail, the account of the best practices for database optimization and handy MySQL performance tuning tips every developer must know. It’s a complete guide for those backend devs, who are uncertain about their poor database performance and need some handy techniques to optimize MySQL database from the core.

We believe that the guide will allow you to craft better queries and have a well-structured database structure that will not only be simple to maintain but also offer more stability to your software applications or website.

Thats all about Database Optimization. 🙂

Tags: , , ,

Mysql – DDL, DML, DCL and TCL

Posted on April 21, 2020 in Feature Questions

MySQL – DDL, DML, DCL and TCL Commands

DDL:

DDL is stands for DATA DEFINITION LANGUAGE, which deals with database schemas and descriptions, of how the data should reside in the database.

  • CREATE – To create a database and its objects like (table, index, views, store procedure, function, and triggers) 
    //Syntax
CREATE TABLE table_name (
      column1 datatype,
      column2 datatype,
    ....
 );

//Ex:
CREATE TABLE Tbl_Employee (
EmployeeID int,
LastName varchar(255),
FirstName varchar(255),
Gender varchar(255),
City varchar(255) 
);

Try It Now
  • ALTER – Alters the structure of the existing database. The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. 
    //Syntax
ALTER TABLE table_name ADD column_name COLUMN DEFINITION; 
//to add new column
ALTER TABLE table_name MODIFY(COLUMN DEFINITION....); 
//to modify existing coulmn

//Ex:
ALTER TABLE Tbl_Employee ADD Email varchar(255);
ALTER TABLE Tbl_Employee MODIFY Email varchar(50);

Try It Now
  • DROP – Delete objects from the database. It is used to delete both the structure and record stored in the table. 
    //Syntax:
DROP TABLE table_name;

//Ex:
DROP TABLE Tbl_Employee;
  • TRUNCATE – Remove all records from a table, including all spaces allocated for the records are removed. In this case primary key will get reset to 1. 
    //Syntax:
TRUNCATE TABLE table_name;

//Ex:
TRUNCATE TABLE Tbl_Employee;
  • RENAME – Rename an object. It might be Table name 
    //Syntax:
RENAME tablename to newtablename;

//Ex: RENAME Tbl_Employee TO Tbl_Employee_New; 
//rename table name

DML:

DML is stands for DATA MANIPULATION LANGUAGE which deals with data manipulation and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE, etc. and it is used to store, modify, retrieve, delete and update data in a database.

  • SELECT– Retrieve data from a database. 
    //Syntax:
SELECT column1, column2, ...
FROM table_name;

//Ex:
SELECT * FROM Tbl_Employee;

Try It Now
  • INSERT– Insert data into a table. 
    //Syntax:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
OR
INSERT INTO table_name
VALUES (value1, value2, value3, ...); 

//Ex:
INSERT INTO Tbl_Employee 
VALUES (1,'Smith','James','Male','Mumbai');

Try It Now
  • UPDATE– Updates existing data within a table. 
    //Syntax:
UPDATE table_name
  SET column1 = value1, column2 = value2, ...
  WHERE condition;

//Ex:
UPDATE Tbl_Employee SET City = 'Delhi' 
WHERE EmployeeID = 1;

Try It Now
  • DELETE– The DELETE statement is used to delete existing records in a table. 
    //Syntax:
DELETE FROM table_name WHERE condition;

//Ex:
UPDATE FROM Tbl_Employee WHERE EmployeeID = 1;

Try It Now

DCL:

DCL is stands for DATA CONTROL LANGUAGE which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.

  • GRANT – Allow users access privileges to the database. 
    //Syntax:
GRANT SELECT, UPDATE ON MY_TABLE 
TO SOME_USER, ANOTHER_USER;
  • REVOKE – Withdraw users access privileges given by using the GRANT command. 
    //Syntax:
REVOKE SELECT, UPDATE ON MY_TABLE 
FROM SOME_USER, ANOTHER_USER;

TCL:

TCL is stands for TRANSACTION CONTROL LANGUAGE which deals with a transaction within a database.

  • COMMIT – Commits a Transaction.When a successful transaction is completed, the COMMIT command should be issued so that the changes to all involved tables will take effect. 
    //Syntax:
COMMIT; //Execute after any mysql execution

//Ex:
DELETE FROM Tbl_Employee  WHERE EmployeeID = 1; 
COMMIT; ##transaction has been complete

Try It Now
  • ROLLBACK – Rollback a transaction in case of any error occurs. 
    //Syntax:
ROLLBACK; //Execute after any mysql execution 

//Ex: 
DELETE FROM Tbl_Employee WHERE EmployeeID = 1;
COMMIT; ##rollback previous transaction

Try It Now
  • SAVEPOINT – To rollback the transaction making points within groups. 
    //Syntax:
Mysql query statment1;
SAVEPOINT tran1; //Belongs to statment1

Mysql query statment2;
SAVEPOINT tran2; //Belongs to statment2

ROLLBACK TO tran1; 
//only output of statement1 will remain 
and output of statemnt2 will rollback.

//Ex:
DELETE FROM Tbl_Employee  WHERE EmployeeID = 1; 
SAVEPOINT tran1;

DELETE FROM Tbl_Employee  WHERE EmployeeID = 2; 
SAVEPOINT tran2;

ROLLBACK TO tran1; ##roll back trans1

Try It Now
  • SET TRANSACTION – Specify characteristics of the transaction.
Tags: , , , ,

MySQL indexes

Posted on April 2, 2020 in Feature Questions

What is MySQL indexes ?

A database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookup and efficient ordering of access to records. An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. Indexes can be created or dropped with no effect on the data.

The index can also be a UNIQUE index, which means that you cannot have duplicate values in that column, or a PRIMARY KEY which in some storage engines defines where in the database file the value is stored.

Create Index Syntax:
Create index on column and allow duplicate values.
CREATE INDEX id_index ON table_name(column_name);

//Example
CREATE INDEX id_index ON Tbl_Employee(EmployeeID);
Try It Now
Create Index Syntax:
Create unique index which can't allow duplicate values.
CREATE INDEX UNIQUE INDEX ON table_name(column_name);

//Example
CREATE UNIQUE INDEX city_index ON Tbl_Employee(City);
Try It Now
ALTER command to ADD and DROP INDEX

ALTER ADD INDEX
ALTER TABLE table_name ADD INDEX(column_name);
//Example
ALTER TABLE Tbl_Employee ADD INDEX(City);

ALTER DROP INDEX
ALTER TABLE table_name DROP INDEX(column_name);
//Example
ALTER TABLE Tbl_Employee DROP INDEX(City);

Try It Now
Show Index
Syntax:
SHOW INDEXES FROM table_name;
//Example
SHOW INDEXES FROM Tbl_Employee ;
Try It Now

How does MySQL indexes work?

Database table does not reorder itself every time the query conditions change in order to optimize the query performance: that would be unrealistic. In actuality, what happens is the index causes the database to create a data structure. The data structure type is very likely a B-Tree. Advantages of the B-Tree is that it is sortable. When the data structure is sorted in order it makes our search more efficient.

Test  Employee Table:

EmployeeID LastName FirstName Gender City
1 Smith James Male Mumbai
2 Johnson Maria Female New York
3 Miller David Male Mumbai
4 Wilson Maria Female Mumbai
5 Thomas Mary Female New York
6 Martin Steven Male New York
7 Lee Karen Female Mumbai

From above table, We have to search Employee fitstname which have city Mumbai then We write query.
SELECT FirstName FROM Tbl_Employee WHERE city= ‘Mumbai’;

Without indexing

In order to non indexing, database search for City = ‘Mumbai’ in all rows of table and return the result. It is time consuming for large data table.

When we apply indexing then index table look like:

EmployeeID LastName FirstName Gender City
1 Smith James Male Mumbai
3 Miller David Male Mumbai
4 Wilson Maria Female Mumbai
7 Lee Karen Female Mumbai
2 Johnson Maria Female New York
5 Thomas Mary Female New York
6 Martin Steven Male New York

In indexing, the query search only the rows in the column City that have Mumbai and then using the pointer that can go into the table to find the specific row where that pointer lives. The query can then go into the table to retrieve the fields for the columns requested for the rows that meet the conditions.

If the search were presented visually, it would look like this:

Index to non index relation

Tags: , ,

Mysql – Views

Posted on March 21, 2019 in Feature Questions

Mysql database view is known as a “virtual table”. virtual table created by a query by joining one or more tables. By Virtual, we mean, the tables do not store any data of their own but display data of other tables. In other simple words, VIEWS are nothing but SELECT Queries.

Create View Syntax:

CREATE VIEW[OR REPLACE] my_view_name AS
SELECT columnname1, columnname2, ...
FROM table_name
WHERE condition;

Example

Sample example of create and call view 
Ex : CREATE VIEW employee_view AS
SELECT EmployeeID, LastName, FirstName
FROM Tbl_Employee
WHERE Gender = "Female";
Try It Now

Views are nothing but SELECT Queries.

CREATE VIEW employee_view AS
SELECT EmployeeID, LastName, FirstName
FROM Tbl_Employee
WHERE Gender = "Female";

SAME AS

SELECT * FROM (
 SELECT EmployeeID, LastName, FirstName
 FROM Tbl_Employee
 WHERE Gender = "Female"
) x;

Drop view using-

DROP VIEW IF EXISTS <view_name>;

How to display the contents of a view?

SHOW CREATE TRIGGER <trigger_name>; 
Ex : SHOW CREATE TRIGGER after_insert_trigger; 
Try It Now

Show list of views in Database-

SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';
Try It Now

Advantages of views:

  • View the data without storing the data into the object.
  • You can restrict users to the view instead of the underlying table(s), thereby enhancing security.
  • Join two or more tables and show it as one object to user.
  • Restict the access of a table so that nobody can insert the rows into the table.
  • Simply If we are having complex queries with joins etc you can make and put in view and use that view where need of reusing it.
Tags: ,

Triggers

Posted on February 24, 2019 in Feature Questions

What Is MySQL Triggers?

The MySQL trigger is a database object that is associated with a table. It will be activated when a defined action is executed for the table. The trigger can be executed when you run one of the following MySQL statements on the table: INSERT, UPDATE and DELETE and it can be invoked before or after the event.

Definition –
A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. Some uses for triggers are to perform checks of values to be inserted into a table or to perform calculations on values involved in an update.
A trigger is defined to activate when a statement inserts, updates, or deletes rows in the associated table. These row operations are trigger events. For example, rows can be inserted by INSERT or LOAD DATA statements, and an insert trigger activates for each inserted row. A trigger can be set to activate either before or after the trigger event. For example, you can have a trigger activate before each row that is inserted into a table or after each row that is updated.

Uses for Triggers

  • Complex Auditing
  • Enforce Business Rules
  • Derive Column Values
  • Triggers Are Tricky!
  • automatically generate derived column values
  • prevent invalid transactions
  • enforce complex security authorizations
  • enforce referential integrity across nodes in a distributed database
  • enforce complex business rules
  • provide transparent event logging
  • provide sophisticated auditing
    Types of Triggers

    • DML Triggers
    • DDL Triggers

Logon Triggers

DML Triggers

DML stands for Data Manipulation Language. INSERT, UPDATE, and DELETE statements are DML statements. DML triggers get fired whenever data is modified using INSERT, UPDATE, and DELETE events.

DML triggers can be again classified into 2 types.
After triggers (Sometimes called FOR triggers)

  1. Instead of triggers
  2. After triggers

After triggers get fired after only with a condition when a modification action occurs. The INSERT, UPDATE and DELETE commands because of an after trigger gets fired after the execution of a complete statement.

Create Trigger Syntax:

CREATE TRIGGER trigger_name trigger_time trigger_event

ON table_name

FOR EACH ROW

BEGIN

...

END;
Sample example of create and call trigger
Try It Now

Drop trigger using-

DROP TRIGGER IF EXISTS <trigger_name>;

Show trigger definition-

SHOW CREATE TRIGGER <trigger_name>; 
Ex : SHOW CREATE TRIGGER after_insert_trigger; 
Try It Now

Show list of Triggers-

SHOW TRIGGERS; 

Try It Now
Tags: ,

Stored Procedure

Posted on February 25, 2018 in Feature Questions

What Is MySQL Stored Procedure?

A stored procedure is a segment of declarative SQL statements stored inside the database catalog. A stored procedure can be invoked by triggers, other stored procedures, and applications such as Java, Python, PHP..
In another word Stored procedure is set of SQL code which is stored in the database server and can be invoked by program or trigger or stored procedure itself. Stored procedure is a way to execute many tasks on your database server directly.

Create Procedure Syntax:

Syntax : 
CREATE PROCEDURE procedure_name [ (parameter datatype [, parameter datatype]) ]
BEGIN
   declaration_section
   executable_section
END;

Ex : 
CREATE PROCEDURE GetAllEmployee()
 BEGIN
 SELECT * FROM Tbl_Employee; 
 END 
 ;

How to call Procedure?

CALL <name>;
Ex : CALL GetAllEmployee();
Sample example of create and call procedure
Try It Now

Procedure Parameter-

  • CREATE PROCEDURE procedureName () : Parameter list is empty
  • CREATE PROCEDURE procedureName (IN varname DATA-TYPE) : One input parameter. The word IN is optional because parameters are IN (input) by default.
  • CREATE PROCEDURE procedureName (OUT varname DATA-TYPE) : One output parameter.
  • CREATE PROCEDURE procedureName (INOUT varname DATA-TYPE) : One parameter which is both input and output.

IN Parameter Example-

CREATE PROCEDURE `testProcedureInParameter` (IN var1 INT)
BEGIN
 SELECT var1 + 2 AS result;
END;
CALL testProcedureInParameter(10);
Try It Now

OUT Parameter Example-

CREATE PROCEDURE `testProcedureOutParameter` (OUT var1 VARCHAR(100))
BEGIN
 SET var1 = 'This Is a test OUT Parameter Procdure test';
END;
CALL testProcedureOutParameter(@var1);
Try It Now

INOUT Parameter Example-

SET @inoutvar = 10;
CREATE PROCEDURE `testProcedureINOutParameter` (INOUT inoutvar INT)
BEGIN
 SET inoutvar = inoutvar * 2;
END;
CALL testProcedureINOutParameter(@inoutvar);

SELECT @inoutvar;
Try It Now

Drop procedure using-

DROP PROCEDURE IF EXISTS <name>;
Ex : DROP PROCEDURE IF EXISTS GetAllEmployee;

Show procedure definition-

SHOW CREATE PROCEDURE <name>;
Ex : SHOW CREATE PROCEDURE GetAllEmployee;
Try It Now

Show list of procedure-

SHOW PROCEDURE STATUS;
Try It Now

 

Tags: ,

What is difference between WHERE and HAVING clause in MySQL

Posted on February 21, 2018 in Feature Questions

Difference between WHERE and HAVING clause in MySQL

1) Apart from SELECT queries, you can use WHERE clause with UPDATE and DELETE clause but HAVING clause can only be used with SELECT query.

2) WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups in MySQL.

3) One syntax level difference between WHERE and HAVING clause is that, former is used before GROUP BY clause, while later is used after GROUP BY clause.

4) When WHERE and HAVING clause are used together in a SELECT query with aggregate function, WHERE clause is applied first on individual rows and only rows which pass the condition is included for creating groups. Once group is created, HAVING clause is used to filter groups based upon condition specified.

That’s all on difference between WHERE and HAVING clause in MySQL.

Always remember key difference between WHERE and HAVING clause in SQL, if WHERE and HAVING clause is used together, first WHERE clause is applied to filter rows and only after grouping HAVING clause is applied.

Try It Now
Tags: , ,

MySQL Select

Posted on February 15, 2018 in My SQL

MySQL SELECT Statement

The SELECT statement is used to select data from a database.

SELECT Syntax

SELECT column1, column2, ...
FROM table_name;

Here, column1, column2, … are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:

SELECT * FROM table_name;

Test  Employee Table

EmployeeID LastName FirstName Gender City
1 Smith James Male Mumbai
2 Johnson Maria Female New York
3 Miller David Male London
4 Wilson Maria Female Paris
5 Thomas Mary Female Singapore
6 Martin Steven Male Bangkok
7 Lee Karen Female Dubai 
Try It Now
Tags: ,

MySQL Home

Posted on February 14, 2018 in My SQL

MySQL is a Relational DataBase Management System (RDBMS).

What is SQL?

  • SQL stands for Structured Query Language
  • SQL lets you access and manipulate databases
  • SQL is an ANSI (American National Standards Institute) standard

Features of MySQL

1. Speed:Ofcourse, the speed at which a server side program runs depends primarily on the server hardware. Given that the server hardware is optimal, MySQL runs very fast. It supports clustered servers for demanding applications.

2. Ease of use:MySQL is a high-performance, relatively simple database system. From the beginning, MySQL has typically been configured, monitored, and managed from the command line. However, several MySQL graphical interfaces are available as described below:

  • MySQL Administrator: This tool makes it possible for administrators to set up, evaluate, and tune their MySQL database server. This is intended as a replacement for mysqladmin.
  • MySQL Query Browser: Provides database developers and operators with a graphical database operation interface. It is especially useful for seeing multiple query plans and result sets in a single user interface.
  • Configuration Wizard: Administrators can choose from a predefined list of optimal settings, or create their own.
  • MySQL System Tray: Provides Windows-based administrators a single view of their MySQL instance, including the ability to start and stop their database servers.

3. Cost:MySQL is available free of cost. MySQL  is a “Open Source” database. MySQL is part of LAMP (Linux, Apache, MySQL, PHP / Perl / Python) environemtn, a fast growing open source enterprise software stack. More and more companies are using LAMP as an alternative to expensive proprietary software stacks because of its lower cost, reliability, and documentation.

4.Query Language Support:MySQL understands standards based SQL (Structured Query Language).

5.Capability :Many clients can connect to the server at the same time. Clients can use multiple database simultaneously. You can  access MySQL using several interfaces such as command-line clients, Web browsers.

6.Connectivity and security: MySQL is fully networked, and database can be accessed from anywhere on the Internet, so you can share your data with  anyone, anywhere. The connectivity could be achieved with Windows programs by using ODBC drivers. By using the ODBC connector to MySQL, any ODBC-aware client application (for example, Microsoft Office, report writers, Visual Basic) can connect to MySQL.

7.Portability: MySQL runs on many varieties of UNIX, as well as on other non-UNIX systems, such as Windows and OS/2. MySQL runs on hardware from home PCs to high-end server. MySQL can be installed on Windows XP, Windows Server 2003, Red Hat Fedora Linux, Debian Linux, and others.

Tags: