It focuses on how the query is executed in terms of the relationship between parts of the query and the order in which the parts are executed. It automatically selects FORMAT=tree and executes the query (with no output to the user). EXPLAIN ANALYZE formats #ĮXPLAIN ANALYZE can be used with SELECT statements, multi-table UPDATE statements, DELETE statements, and TABLE statements. If you use EXPLAIN ANALYZE before a statement, you get both the estimation of what the planner expected ( highlighted in yellow above) and what actually happened when the query was run ( highlighted in green above). Number of rows returned by the iterator.When there are multiple loops, this figure shows the average time per loop. Time spent executing this iterator (including child iterators, but not parent iterators), in milliseconds. Estimated execution cost (the cost model does not account for some iterators, so they aren’t included in the estimate).In addition to the query plan and estimated costs, which a normal EXPLAIN will print, EXPLAIN ANALYZE also prints the actual costs of individual iterators in the execution plan.ĮXPLAIN ANALYZE actually runs the query, so if you don’t want to run the query against your live database, do notįor each iterator, the following information is provided: In MySQL 8.0.18, EXPLAIN ANALYZE was introduced, a new concept built on top of the regular EXPLAIN query plan inspection tool. To recap, by using EXPLAIN, you get the list of things expected to happen. Any extra information relevant to the query.If you multiply the rows column by this percentage, you will see the number of rows MySQL estimates it will join with the previous tables in the query plan. The percentage of rows filtered by a specified condition - This column shows a pessimistic estimate of the percentage of rows that will satisfy some condition on the table, such as a WHERE clause or a join condition.The fewer rows your query accesses, the faster your queries will be. This column displays how many rows MySQL accessed to complete a request, which can be useful when designing indexes. The number of rows accessed by the query - When designing indexes inside of your database instances, keep an eye on the rows column too.The length of the index chosen by MySQL - When MySQL chooses a composite index, the length field is the only way you can determine how many columns from that composite index are in use.Types of JOINs used (if any) - Please keep in mind that this column gets populated even on queries that don’t have joins.The table on which your query was running.If you are explaining a DELETE, the select_type will be DELETE The query contains no subqueries or UNIONsĬomplex types can be grouped into three broad classes: simple subqueries, derived tables (subqueries in the FROM clause), and UNIONs. The SELECT_TYPE - If you are running a SELECT, MySQL divides SELECT queries into simple and primary (complex) types, as described in the table below.The ID of the query - The column always contains a number, which identifies the SELECT to which the row belongs.By leveraging this internal MySQL tool, you can observe the following: When you prepend the EXPLAIN keyword to the beginning of a query, it explains how the database executes that query and the estimated costs. It’s essential to understand its features and how to leverage it best to achieve performance goals. Although creating an EXPLAIN plan is relatively simple, the output isn’t exactly intuitive. On the Technical Solutions team here at PlanetScale, we frequently talk with users who seek advice regarding query performance. This blog post will demonstrate how to utilize MySQL EXPLAIN to remedy problematic queries. In the MySQL world, EXPLAIN is a keyword used to gain information about query execution.
0 Comments
Leave a Reply. |