Explain Query in MYSQL

Explain

The EXPLAIN keyword is used throughout various SQL databases and provides information about how your SQL database executes a query. In MySQL, EXPLAIN can be used in front of a query beginning with SELECT, INSERT, DELETE, REPLACE, and UPDATE. For a simple query, it would look like the following:

Example :

EXPLAIN SELECT * FROM tbl_tgo WHERE TgoPnr = ‘QYO86C’ AND SubAgencyName = ‘Nimay tours and travels’ ORDER BY Tds, TgoPaxName ASC

Output :

Optimize your MYSQL database

1. Proper indexing

Index is basically a data structure that helps speed up the data retrieval process overall. Unique index is a kind of indexing that creates separate data columns without overlapping each other. Proper indexing ensures quicker access to the database. Excessive indexing or no indexing at all are both wrong. Without any indexing at all, the processing will be very slow, whereas indexing everything will render the insert and update triggers ineffective.

(Index All Columns Used in ‘where’, ‘order by’, and ‘group by’ Clauses)

2. Retrieve the relevant data only

Specifying the data one requires enables precision in retrieval. Using the commands * and LIMIT, instead of SELECT * as and when required is a great way of tuning the database, while avoiding retrieving the whole set of data when the user wants only a certain part of it. Of course, it will not be necessary when the amount of data overall is less. But when accessing data from a large source, specifying the portions required would save a lot of essential time.

The * command is for use in specifying data from columns, and the LIMIT command is when the user requires data from a certain number of rows from among the lot. Selecting sparingly is not exactly a necessary rule. However, it does help in avoiding system errors in the future. Also, limiting and specifying the data reduces the further necessity of optimizing of the database to a great deal.

3. Getting rid of correlated subqueries

A correlated sub-query basically depends on the parent or outer query. This kind of search is done row by row. That means it decreases the overall speed of the process. This problem usually lies in the command of WHERE from the outer query, applying which, the subquery runs for each row, returned by the parent query, consequently slowing the whole process and reducing the efficiency of the database. So, a better way of tuning the database, in this case, is to the INNER JOIN command, instead of the correlated sub-query. But in certain cases, using correlated sub-query is essential.

4. Using or avoiding temporary tables according to requirement

If any code can be well written in a simple way, there is absolutely no need to make it complex with temporary tables. Of course, if a data has a specific procedure to be set up which requires multiple queries, the use of temporary tables in such cases are, in fact, recommended. Temporary tables are often alternated by sub-queries, but one has to keep in mind the specific efficiency that each of these would provide in separate cases.

5. Avoid coding loops

Avoiding coding loops is very much needed in order to avoid slowing down of the whole sequence. This can be achieved by using the unique UPDATE or INSERT commands with individual rows, and by ensuring that the command WHERE does not update the stored data in case it finds a matching preexisting data.

6. Execution plans

The execution plan tool created by the optimizer play major role in tuning SQL databases. They help in creating proper indexes too. Although, its main function is to display graphically the various methods to retrieve data. This, in turn, helps in creating the needed indexes and doing the other required steps to optimize the database.

Of course, there are tons of other ways one can tune their SQL database in the most efficient manner. Also, there is big chance that the steps mentioned above, might not be the right choice for all databases. Each database will require optimizing techniques uniquely specific to its needs.

7. Avoid functions on RHS of the operator
Often developers use functions or method with their SQL queries. Consider the following example.

select *
from Customer
where YEAR(AccountCreatedOn) == 2005
and MONTH(AccountCreatedOn) = 6

Note that even though AccountCreatedOn has an index, the above query changes the where clause such a way that this index cannot be used anymore.

Rewriting the query in the following way will increase the performance tremendously.

Select *
From Customer
Where AccountCreatedOn between ‘6/1/2005’
and ‘6/30/2005’

8. Avoid foreign key constraints
Foreign keys constraints ensure data integrity at the cost of performance. Therefore, if performance is your primary goal you can push the data integrity rules to your application layer. A good example of a database design that avoids foreign key constraints is the System tables in most databases. Every major RDBMS has a set of tables known as system tables. These tables contain meta data information about user databases. Although there are relationships among these tables, there is no foreign key relationship. This is because the client, in this case the database itself, enforces these rules.

9. As a rule, queries that perform complicated calculations slow down your database’s performance. If your database regularly performs complicated calculations, it makes sense to add additional columns to a table to hold frequently used and hard to calculate data. Creating a column that contains pre-computed values can save a significant amount of time during query execution. However, it also requires timely updating of data in this column.

10. If a database contains large tables that contain long fields like Blob and Long, you can speed up query processing by moving long fields to a separate table.

11. Choose appropriate SQL Data Type to store your data since it also helps in to improve the query performance. Example: To store strings use varchar in place of text data type since varchar performs better than text. Use text data type, whenever you required storing of large text data (more than 8000 characters). Up to 8000 characters data you can store in varchar.

12. Practice avoiding the insertion of NULL values in the fixed-length (char) field. Since NULL takes the same space as the desired input value for that field. In case of requirement of NULL, use variable-length (varchar) field that takes less space for NULL.

13. Practice avoiding Having Clause since it acts as a filter over selected rows. Having clause is required if you further wish to filter the result of an aggregation. Don’t use HAVING clause for any other purpose.

FULLTEXT index in mysql

 

  • FULLTEXT indexes are different from all of the above, and their behaviour differs significantly between database systems. FULLTEXT indexes are only useful for full text searches done with the MATCH() / AGAINST() clause, unlike the above three – which are typically implemented internally using b-trees (allowing for selecting, sorting or ranges starting from left most column) or hash tables (allowing for selection starting from left most column).

    Where the other index types are general-purpose, a FULLTEXT index is specialised, in that it serves a narrow purpose: it’s only used for a “full text search” feature.

Create Fulltext Index :

Alter table students ADD FULLTEXT (first_name, last_name);

Use Fulltext index for search(select) full text

In your table first_name column contain “dinesh kumar” and last_name column contain “singh” and you want to search by d, di, din, dine, dines, k, ku, kum, kuma, s, si, sin, sing then out put is null if you want to search dinesh or kumar or singh then this query show out put

Select * from students where match(first_name, last_name) AGAINST (‘dinesh’);

 

Create Partition in Mysql

Create table using partition :

CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (1995),
        PARTITION p2 VALUES LESS THAN (2000),
        PARTITION p3 VALUES LESS THAN (2005)
    );

Simple Select query :
SELECT * FROM trb3 WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';

Select query using partition name :
SELECT * FROM trb3 PARTITION (p2);
 

Table fields validation in mysql using trigger

DROP TRIGGER IF EXISTS `agecheck`;
CREATE DEFINER=`root`@`localhost` TRIGGER `agecheck` BEFORE INSERT ON `people` FOR EACH ROW BEGIN IF (NEW.age < 0 OR new.age > 30) then SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘Error : age accept only 0 to 30’; ELSEIF new.name not rlike ‘^[a-zA-Z ]*$’ then SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘Erro : name accept only alphabet and space’; END IF; END

Calculate average time stamp

function average_time($total, $count, $rounding = 0) {
$total = explode(“:”, strval($total));
if (count($total) !== 3) return false;
$sum = $total[0]*60*60 + $total[1]*60 + $total[2];
$average = $sum/(float)$count;
$hours = floor($average/3600);
$minutes = floor(fmod($average,3600)/60);
$seconds = number_format(fmod(fmod($average,3600),60),(int)$rounding);
return $hours.”:”.$minutes.”:”.$seconds;
}

Addition of multiple timestamp

function sum_the_time($time1, $time2) {
$times = array($time1, $time2);
$seconds = 0;
foreach ($times as $time)
{
list($hour,$minute,$second) = explode(‘:’, $time);
$seconds += $hour*3600;
$seconds += $minute*60;
$seconds += $second;
}
$hours = floor($seconds/3600);
$seconds -= $hours*3600;
$minutes = floor($seconds/60);
$seconds -= $minutes*60;
if($seconds < 9)
{
$seconds = “0”.$seconds;
}
if($minutes < 9)
{
$minutes = “0”.$minutes;
}
if($hours < 9)
{
$hours = “0”.$hours;
}
return “{$hours}:{$minutes}:{$seconds}”;
}

Difference between two time stamp in Hms

Show difference in format =>  hour : minute: second

function TimeDiffHMS($start, $ends){
$starts = new DateTime($start);
$endss = new DateTime($ends);
$interval = $endss->diff($starts);

$days = $interval->format(‘%a’);
$hour = $interval->format(‘%h’);
$minu = $interval->format(‘%i’);
$seco = $interval->format(‘%s’);
$FinalTime = ”;

$DayInHour = (int)$days * 24;
$hour = (int)$hour + (int)$DayInHour;

$hour = str_pad($hour,2,”0″,STR_PAD_LEFT);
$minu = str_pad($minu,2,”0″,STR_PAD_LEFT);
$seco = str_pad($seco,2,”0″,STR_PAD_LEFT);

if($FinalTime == ” && $hour > 0){
$FinalTime = $hour.’:’;
}else if($FinalTime != ” && $hour > 0){
$FinalTime = $hour.’:’;
}else{
$FinalTime = ’00:’;
}
if($FinalTime == ” && $minu > 0){
$FinalTime = $minu.’:’;
}else if($FinalTime != ” && $minu > 0){
$FinalTime .= $minu.’:’;
}else{
$FinalTime .= ’00:’;
}
if($FinalTime == ” && $seco > 0){
$FinalTime = $seco;
}else if($FinalTime != ” && $seco > 0){
$FinalTime .= $seco;
}else{
$FinalTime .= ’00’;
}
return $FinalTime;
}

Difference between 2 time stamp

Show difference in format : Day : hour : minute: second

function TimeDiff($start, $ends){
$starts = new DateTime($start);
$endss = new DateTime($ends);
$interval = $endss->diff($starts);

$days = $interval->format(‘%a’);
$hour = $interval->format(‘%h’);
$minu = $interval->format(‘%i’);
$seco = $interval->format(‘%s’);

$FinalTime = ”;
if($days > 0){
$FinalTime = $days.’ day ‘;
}
if($FinalTime == ” && $hour > 0){
$FinalTime = $hour.’ hour ‘;
}else if($FinalTime != ” && $hour > 0){
$FinalTime .= $hour.’ hour ‘;
}
if($FinalTime == ” && $minu > 0){
$FinalTime = $minu.’ minute ‘;
}else if($FinalTime != ” && $minu > 0){
$FinalTime .= $minu.’ minute ‘;
}
if($FinalTime == ” && $seco > 0){
$FinalTime = $seco.’ second ‘;
}else if($FinalTime != ” && $seco > 0){
$FinalTime .= $seco.’ second ‘;
}
return $FinalTime;
}