Featured
Percona Products
Percona Services
Use Cases
Sveta SmirnovaWe’ve already discussed using the ORDER BY clause with subqueries. You can also, however, use the ORDER BY clause with sorting results of one of the columns. Actually, this is most common way to use this clause. Sometimes such queries require using temporary tables or filesort, and a regular EXPLAIN clause provides this information. But it doesn’t show if this […]
In the previous blog post, we discussed the group_by_subqueries member (which is child of grouping_operation). Let’s now focus on the grouping_operation and other details of GROUP BY processing. grouping_operation simply shows the details of what happens when the GROUP BY clause is run:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
mysql> explain format=json select dept_no from dept_emp group by dept_noG *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "14.40" }, "grouping_operation": { "using_filesort": false, "table": { "table_name": "dept_emp", "access_type": "range", "possible_keys": [ "PRIMARY", "emp_no", "dept_no" ], "key": "dept_no", "used_key_parts": [ "dept_no" ], "key_length": "4", "rows_examined_per_scan": 9, "rows_produced_per_join": 9, "filtered": "100.00", "using_index_for_group_by": true, "cost_info": { "read_cost": "12.60", "eval_cost": "1.80", "prefix_cost": "14.40", "data_read_per_join": "144" }, "used_columns": [ "emp_no", "dept_no" ] } } } } 1 row in set, 1 warning (0.00 sec) Note (Code 1003): /* select#1 */ select `employees`.`dept_emp`.`dept_no` AS `dept_no` from `employees`.`dept_emp` group by `employees`.`dept_emp`.`dept_no` |
In the listing above, you can see which table was accessed by the GROUP BY operation, the access type, and if an […]
Another post in the EXPLAIN FORMAT=JSON is Cool! series! In this post, we’ll discuss how the EXPLAIN FORMAT=JSON provides optimization details for ORDER BY and GROUP BY operations in conjunction with order_by_subqueries and group_by_subqueries. EXPLAIN FORMAT=JSON can print details on how a subquery in ORDER BY is optimized:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
mysql> explain format=json select emp_no, concat(first_name, ' ', last_name) f2 from employees order by (select emp_no limit 1)G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "60833.60" }, "ordering_operation": { "using_filesort": true, "table": { "table_name": "employees", "access_type": "ALL", "rows_examined_per_scan": 299843, "rows_produced_per_join": 299843, "filtered": "100.00", "cost_info": { "read_cost": "865.00", "eval_cost": "59968.60", "prefix_cost": "60833.60", "data_read_per_join": "13M" }, "used_columns": [ "emp_no", "first_name", "last_name" ] }, "order_by_subqueries": [ { "dependent": true, "cacheable": false, "query_block": { "select_id": 2, "message": "No tables used" } } ] } } } 1 row in set, 2 warnings (0.00 sec) Note (Code 1276): Field or reference 'employees.employees.emp_no' of SELECT #2 was resolved in SELECT #1 Note (Code 1003): /* select#1 */ select `employees`.`employees`.`emp_no` AS `emp_no`,concat(`employees`.`employees`.`first_name`,' ',`employees`.`employees`.`last_name`) AS `f2` from `employees`.`employees` order by (/* select#2 */ select `employees`.`employees`.`emp_no` limit 1) |
The above code shows member ordering_operation of query_block (which includes the order_by_subqueries array) with information on how the subquery in ORDER BY was optimized. This is […]
The EXPLAIN FORMAT=JSON is Cool! series showed an example of the query select dept_name from departments where dept_no in (select dept_no from dept_manager where to_date is not null), where the subquery was materialized into a temporary table and then joined with the outer query. This is known as a semi-join optimization. But what happens if we turn off this optimization? EXPLAIN FORMAT=JSON can help us with this investigation too. First lets look at the original output […]
EXPLAIN FORMAT=JSON The regular EXPLAIN command already provides some information about subquery optimization. For example, you can find out if the subquery is dependent or not, and (since version 5.6) if it was materialized:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
mysql> explain select dept_name from departments where dept_no in (select dept_no from dept_manager where to_date is not null)G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: departments partitions: NULL type: index possible_keys: PRIMARY key: dept_name key_len: 42 ref: NULL rows: 9 filtered: 100.00 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: <subquery2> partitions: NULL type: eq_ref possible_keys: <auto_key> key: <auto_key> key_len: 4 ref: employees.departments.dept_no rows: 1 filtered: 100.00 Extra: NULL *************************** 3. row *************************** id: 2 select_type: MATERIALIZED table: dept_manager partitions: NULL type: ALL possible_keys: dept_no key: NULL key_len: NULL ref: NULL rows: 24 filtered: 90.00 Extra: Using where 3 rows in set, 1 warning (0.00 sec) Note (Code 1003): /* select#1 */ select `employees`.`departments`.`dept_name` AS `dept_name` from `employees`.`departments` semi join (`employees`.`dept_manager`) where ((`<subquery2>`.`dept_no` = `employees`.`departments`.`dept_no`) and (`employees`.`dept_manager`.`to_date` is not null)) |
However, you can’t find details on exactly how this subquery was materialized. To find out more, use EXPLAIN FORMAT=JSON:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
mysql> explain format=json select dept_name from departments where dept_no in (select dept_no from dept_manager where to_date is not null)G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "16.72" }, "nested_loop": [ { "table": { "table_name": "departments", <skipped> }, { "table": { "table_name": "<subquery2>", "access_type": "eq_ref", "key": "<auto_key>", "key_length": "4", "ref": [ "employees.departments.dept_no" ], "rows_examined_per_scan": 1, "materialized_from_subquery": { "using_temporary_table": true, "query_block": { "table": { "table_name": "dept_manager", "access_type": "ALL", "possible_keys": [ "dept_no" ], "rows_examined_per_scan": 24, "rows_produced_per_join": 21, "filtered": "90.00", "cost_info": { "read_cost": "1.48", "eval_cost": "4.32", "prefix_cost": "5.80", "data_read_per_join": "345" }, "used_columns": [ "dept_no", "to_date" ], "attached_condition": "(`employees`.`dept_manager`.`to_date` is not null)" } } } } } ] } } 1 row in set, 1 warning (0.00 sec) Note (Code 1003): /* select#1 */ select `employees`.`departments`.`dept_name` AS `dept_name` from `employees`.`departments` semi join (`employees`.`dept_manager`) where ((`<subquery2>`.`dept_no` = `employees`.`departments`.`dept_no`) and (`employees`.`dept_manager`.`to_date` is not null)) |
We can see […]
In the previous post for this ongoing “used_columns array can help to choose them wisely. There is one more type of multiple-column indexes: composite indexes. Composite indexes are just indexes on multiple columns. Covered indexes are a subgroup of the larger set “composite indexes.” In this post we’ll discuss how “used_key_parts” can help show which part of a multiple column […]
In the “MySQL Query tuning 101” video, Alexander Rubin provides an excellent example of when to use a covered index. On slide 25, he takes the query select name from City where CountryCode = ’USA’ and District = ’Alaska’ and population > 10000 and adds the index cov1(CountryCode, District, population, name) on table City. With Alex’s query tuning experience, making the right index decision is simple – but what about us mere mortals? If a query is more […]
At the end of my talk LinuxPiter conference, a user asked me a question: “What does the EXPLAIN ‘filtered’ field mean, and how do I use it?” I explained that this is the percentage of rows that were actually needed, against the equal or bigger number of resolved rows. While the user was happy with the answer, I’d […]
When you work with complicated queries, especially ones which contain subqueries, it is easy to make a typo or misinterpret column name. While in many cases you will receive a column not found error, sometimes you can get strange results instead. Like finding 4079 countries in Antarctica:
|
1 2 3 4 5 6 7 |
mysql> select count(*) from City where CountryCode in (select CountryCode from Country where Continent = 'Antarctica'); +----------+ | count(*) | +----------+ | 4079 | +----------+ 1 row in set (0.05 sec) |
Or not finding any cities in Georgia:
|
1 2 |
mysql> select Name, Language from City join CountryLanguage using (CountryCode) where CountryCode in (select Code from Country where District='Georgia' and Continent='Asia'); Empty set (0.18 sec) |
I used a standard
In MySQL QA Episode #12, “MySQL is Crashing, now what?,” Roel demonstrated how to collect crash-related information that will help Percona discover what the issue is that you are experiencing, and fix it. As a Support Engineer I (Sveta) am very happy to see this post – but as a person who better understands writing than recording – […]
Lately, I saw many cases when users specified the option --base64-output=DECODE-ROWS to print out a statement representation of row events in MySQL binary logs just to get nothing. Reason for this is obvious: option --base64-output=DECODE-ROWS does not convert row events into its string representation, this is the job of the option -- verbose. But why users […]
Like any good, thus lazy, engineer I don’t like to start things manually. Creating directories, configuration files, specify paths, ports via command line is too boring. I wrote already how I survive in case when I need to start MySQL server (here). There is also the MySQL Sandbox which can be used for the same […]
In version MySQL 5.7.7 Oracle presented a new promising feature: optimizer hints. However it did not publish any documentation about the hints. The only note which I found in the user manual about the hints is: It is now possible to provide hints to the optimizer by including /*+ … */ comments following the SELECT, […]