How to Optimize MySQL Queries with Query Profiler by Devart?

Learn how to analyze performance and optimize MySQL queries using visual Query Analyzer built in dbForge Studio for MySQL.
Learn more: http://www.devart.com/dbforge/mysql/studio/query-profiler.html

---

Closed Caption:

Welcome to dbForge Studio for MySQL
Tutorials.
Let’s learn how to optimize your queries in a
visual mode without much effort, using the Query Profiler tool.
On the Start Page, click Query Profiler.
Let's create a query that sums the salary and
calculates the average value for a particular
employee.
As you can see, the Join condition is promted.
To expand columns, press Tab key,
and change the output columns.
For this query the SUM and the Average functions are avalible,
The Where expression is promted.
As you see, the Columns list,
and the Group By expression are also avalible.
Now let’s execute the query and see the profiling results.
On the Profile tab you can see the results of the SHOW PROFILE command.
Here the summed duration for our query is
shown.
To define in what table the full scan takes place,
refer to the EXPLAIN results, shown on the Plan tab.
As you can see, the quantity of the analyzed
rows is 26 thousand.
On the Session statistics tab you can see the change of the Status variables.
Now MySQL Server does the full scan. It’s a
time-consuming operation.
Now go to the Employees table,
right-click the Indexes node and select New Index.
To create a composite index with the first and the last name columns using a convenient editor,
add the required columns,
unselect the Unique check box,
and click OK.
Now execute the query.
As you can see, the new query results appeared
as a new node.
As you see on the Profile tab, the query duration time has been reduced to 1.1 milliseconds.
Now move to the Plan tab.
The quantity of the analyzed rows decreased in thousand times.
On the Session Statistics tab, see the changes of Status variables.
Now let's compare the profiling results.
Select them by holding the Ctrl key and click Compare Selected Results.
See the differences highlighted.
Find Select Scan.
Here are the comparison of the SHOW PROFILE results,
and the comparison of profiling properties results.
See the differences highlighted.
Here is the SQL query result.
You have been watching the dbForge Studio for My SQL Query Profiler Tutorial.
Thank you for your time.