In MySQL, a subquery is a query within a query. You can create subqueries within your SQL statements. You can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause. MySQL is a platform for writing query languages. And Getting Acquainted with the Interface. The program we will be working with in this course is called MySQL Workbench. It is the Oracle visual tool for database design, modelling, creation, manipulation, maintenance, and administration. SQL -MySQL Tutorial for Data Analytics.
Performance DashboardView Screenshot: Windows, Linux, OS X Performance ReportsView Screenshot: Windows, Linux, OS X Visual ExplainView Screenshot: Windows, Linux, OS X
MySQL Workbench includes various tools for both DBAs and Developers related to viewing and improving performance. The Performance Dashboard and reports allow DBAs to easily view overall server performance, and various reports provide views of IO hotspots, SQL statements, Network, Data Engine, and more. For developers, MySQL Workbench provides easy to understand views into optimizing queries and data access.
New! Performance Dashboard
The Performance Dashboard provides quick 'at a glance' views of MySQL performance on key server, network, and InnoDB metrics. Simply mouse over various graphs and visuals to get added details.
New! Performance Reports
Over 20 reports help to analyze the performance of your MySQL databases. Targeted reports make analyzing IO hotspots, high cost SQL statements, Wait statistics, InnoDB engine metrics. MySQL Workbench leverages the SYS views on the Performance Schema.
New! Explain Plan
The explain plan shows the operations MySQL performs when it runs SQL statements. This information can help optimize SQL performance. MySQLWorkbench Visualize Explain plans graphically show and highlight how SQL statements execute within MySQL. By showing developers costs and tuning hints, MySQL Workbench improves and simplifies SQL statement performance tuning.
New! Query Statistics
Query Statistics provide instant statistics on SQL executed from the Workbench Editor, such as details about the fields in your result set and key performance statistics from your query, such as client timing, network latency, server execution timing, index usage, number of rows scanned, joins, use of temporary data storage, and more.
Additional Resources
Summary: in this tutorial, you will learn how to use the MySQL
USE INDEX hint instruct the query optimizer to use only a list of named indexes for a query.
Introduction to MySQL USE INDEX hint
In MySQL, when you submit an SQL query, the query optimizer will try to make an optimal query execution plan.
To determine the best possible plan, the query optimizer makes use of many parameters. One of the most important parameters for choosing which index to use is stored key distribution which is also known as cardinality.
The cardinality, however, may be not accurate for example in case the table has been modified heavily with many inserts or deletes.
To solve this issue, you should run the
ANALYZE TABLE statement periodically to update the cardinality.
In addition, MySQL provides an alternative way that allows you to recommend the indexes that the query optimizer should by using an index hint called
USE INDEX .
The following illustrates syntax of the MySQL
USE INDEX hint:
In this syntax, the
USE INDEX instructs the query optimizer to use one of the named indexes to find rows in the table.
Notice that when you recommend the indexes to use, the query optimizer may either decide to use them or not depending on the query plan that it comes up with.
MySQL USE INDEX example
We will use the customers table from the sample database for the demonstration.
First, use the
SHOW INDEXES statement to display all indexes of the customers table:
Second, create four indexes as follows:
Third, find customers whose contact first name or contact last name starts with the letter A. Use the
EXPLAIN statement check which indexes are used:
The following shows the output of the statement:
As you can see, the Query Optimizer used the
idx_c_fn and idx_c_ln indexes.
Fourth, if you think that it is better to use the
idx_c_fl and idx_c_lf indexes, you use the USE INDEX clause as follows:
Notice that this is just for the demonstration purposes, not the best choice though.
The following illustrates the output:
These are the changes:
The
USE INDEX is useful in case the EXPLAIN shows that the Query Optimizer uses the wrong index from the list of possible indexes.
In this tutorial, you have learned how to use the MySQL
USE INDEX hint to instruct the Query Optimizer to use the only list of specified indexes to find rows in a table.
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
December 2022
Categories |