Mysql Explain Ref Null

EXPLAIN works with SELECT, DELETE, INSERT , REPLACE, and MySQL can use a ref_or_null join to process ref_table: SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL; See Section 8. Reading values follows similar rules. 00 Extra: Using index ***** 2. row ***** id: 2 select_type: SUBQUERY table: t2 type: index possible_keys: a key: a key_len: 5 ref. If you run EXPLAIN , such conditions typically only cause the type column to be show ref_or_null. If you are looking for examples that demonstrate how to use the C API, take a look at these clients. In this article. この時点ではまだ普通のような気がしますが、とりあえず、一番基本的な項目がどこに. mysqlのexplainを使用することで、mysqlがクエリを実行する方法についての実行計画を知ることができます。 この実行計画によってクエリやインデックス最適化を行うことができます。 データ準備. These function receive the result of EXPLAIN or EXPLAIN EXTENDED, and return the parsed result as array reference that contains hash reference. It is included in the mysqlclient library and allows C programs to access a database. You might not know this, but MySQL can dramatically change your query before it actually executes it. It is not uncommon for an application to use WHERE conditions like this: This particular one comes from the real world, some years ago. It used the index on (b) instead. Right-click the report in the Navigation Pane, and then click Layout View on the shortcut menu. mysql> EXPLAIN SELECT * FROM sales\G ***** 1. mysql explain时,possible_keys有索引,但是key为null没有使用索引. The C API code is distributed with MySQL. 大部分情况下利用索引排序更快,所以一般这时. The explain_type option. ref: A non unique index or prefix of an unique index is used to find the rows. a FROM t2) FROM t1\G ***** 1. MariaDB/MySQL IS NULL conditions and indexes. The EXPLAIN statement provides information about how MySQL executes statements. MariaDB/MySQL IS NULL conditions and indexes. The EXPLAIN statement provides information about how MySQL executes statements. This means that each record should be read and evaluated. 7缺省就会输出 filtered ),它指返回结果的行占需要读到的行 (rows列的值)的百分比。. It is included in the mysqlclient library and allows C programs to access a database. ref_or_null: Like 'ref' but in addition another search for the 'null' value is done if the first value was not found. この時点ではまだ普通のような気がしますが、とりあえず、一番基本的な項目がどこに. Many of the clients in the MySQL source distribution are written in C. It used the index on (b) instead. When you issue a query, the MySQL Query Optimizer tries to devise an optimal plan for query execution. a IN (SELECT t2. EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements. 00 sec) mysql> SHOW WARNINGS\G ***** 1. index_merge. 标签 mysql explain 字段 解释 栏目 MySQL 繁體版. The C API code is distributed with MySQL. a FROM t2) FROM t1\G ***** 1. 注:都是用uid字段做关联. Cloud Firestore is a flexible, scalable database for mobile, web, and server development from Firebase and Google Cloud. mysql explain时,possible_keys有索引,但是key为null没有使用索引. In the following examples, MySQL can use a ref_or_null join to process ref_table:. student_id = g. mysql> EXPLAIN SELECT COUNT(*) FROM t WHERE (a = 2 OR a IS NULL) AND (b = 5 OR b IS NULL) \G ***** 1. Compare: mysql> explain select count(*) from Region where contains(`geometry`, point(13. 执行Mysql的explain extended的输出会比单纯的 explain多一列 filtered (MySQL 5. Duplicates. 00 Extra: Using index ***** 2. On the Create tab, in the Queries group, click Query Design. EXPLAIN works with SELECT, DELETE, INSERT , REPLACE, and MySQL can use a ref_or_null join to process ref_table: SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL; See Section 8. Reversada=1,-1,1)*IFNULL(ctamov. When you set up a database (at least in MS SQL Server) you can flag a field as allowing NULL values and which default values to take. Using where无需多说,就是使用了where筛选条件。. MariaDB/MySQL IS NULL conditions and indexes. 按说 filtered 是个非常有用的值,因为对于join操作,前. The optimizer chooses an index based on the estimated cost to do the least amount of work, not what a human considers the right order. In MySQL, EXPLAIN can be used in front of a query beginning with SELECT, INSERT, DELETE, REPLACE, and UPDATE. Mysql-Explain(七):输出列-ref、rows简介演示 在上一篇《Mysql-Explain(六):输出列-possiable_keys、key、key_len》文章中,我们通过具体的sql实例介绍了possiable_keys、key、key_len在不同值下所代表的意义,接下来我们开始介绍另外两个输出列:ef、rows。. 00 Extra: NULL 1 row in set, 1 warning (0. APPLIES TO: Azure Database for MySQL - Single Server Azure Database for MySQL - Flexible Server EXPLAIN is a handy tool to optimize queries. You might not know this, but MySQL can dramatically change your query before it actually executes it. When you issue a query, the MySQL Query Optimizer tries to devise an optimal plan for query execution. The EXPLAIN statement provides information about how MySQL executes statements. MySQL C API. It is not uncommon for an application to use WHERE conditions like this: This particular one comes from the real world, some years ago. 00 sec) mysql> SHOW WARNINGS\G ***** 1. I can force the key choice using FORCE INDEX (and the query becomes efficient), but I don't want to "force" all my database programs to worry about what I call my indexes and whether they need to force an index, etc. This is the first article in a series of posts. 43 Extra: Using where; Using index 1 row in set, 1 warning (0. EXPLAIN is one of. mysql ref 为 null _ mysql explain 22222 system > const > eq_ ref > ref > fulltext > ref _o MySQL 性能分析及 explain 用法的知识是本文我们主要要介绍的内容,接下来就让我们通过一些实际的例子来介绍这一过程,希望能够对您有所帮助。. explain关键字可以模拟MySQL优化器执行SQL语句,可以很好的分析SQL语句或表结构的性能瓶颈。 explain的用途 1. mysql> explain select * from trace_sp_info; 这种需求只能遍历全表. ref_or_null works by first doing a read on the reference key, and then a separate search for rows with a NULL key value. 执行Mysql的explain extended的输出会比单纯的 explain多一列 filtered (MySQL 5. ref The ref column shows which columns or constants are used with the key to select rows from the table. 本題に入る前に、今回の解説で利用するテーブルとデータを作成します。. ref: different fromeq_ref,refIndicates that if a non unique index is used, many qualified rows will be found. That is, MySQL explains how it would process the statement, including information about how tables are joined and in which order. Duplicates. Here is an example of extended EXPLAIN output: mysql> EXPLAIN SELECT t1. A NULL is output as the NULL parameter string and is not quoted, while a non-NULL value matching the NULL parameter string is quoted. MySQL::Explain::Parser is the parser for result of EXPLAIN of MySQL. row ***** id: 1 select_type: PRIMARY table: t1 type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 4 filtered: 100. 使用explain 语句去查看分析结果如 explain. If you are looking for examples that demonstrate how to use the C API, take a look at these clients. この時点ではまだ普通のような気がしますが、とりあえず、一番基本的な項目がどこに. ref_or_null: this is the same as ref but also contains rows with a NULL value from the column. The first could not use the right index, because ref_or_null cannot be used on multiple columns. ref_or_null: this is the same as ref but also contains rows with a NULL value from the column. Many of the clients in the MySQL source distribution are written in C. That is, MySQL explains how it would process the statement, including information about how tables are joined and in which order. a FROM t2) FROM t1\G ***** 1. row ***** id: 1 select_type: PRIMARY table: t1 type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 4 filtered: 100. Click the text box that displays the rich text field, and then, on the Format tab, in the Font group, click the formatting style that you want to apply. 00 sec) mysql> SHOW WARNINGS\G ***** 1. You can retrieve the information in various formats using the explain_type option. ref_or_null: Like 'ref' but in addition another search for the 'null' value is done if the first value was not found. In this example, MySQL has to make a choice between the possible indexes as listed in possible_keys. 15, “IS NULL Optimization”. ref_or_null这种连接类型是这样的 ref,但除此之外,MySQL还会对包含NULL值的行进行额外搜索。此连接类型优化最常用于解析子查询。在以下示例中,MySQL可以使用 ref_or_null联接来处理ref_table:. system: The table has 0 or 1 rows. The SQL DELETE statement is a used to delete one or more records from a table. If you look through people's DB structures, you'll see that a lot of people allow NULL values in their database. Cloud Firestore is a flexible, scalable database for mobile, web, and server development from Firebase and Google Cloud. row ***** id: 1 select_type: SIMPLE table: Region type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 7469 Extra: Using where mysql> explain select count(*) from Region where contains(`geometry`, point(13. The reason is that single-table UPDATE/DELETE statements go through only a subset of the optimizations that SELECT and multi-table UPDATE/DELETE statements go through. Now if there were a situation where one person has multiple addresses, this design would be a problem. mysql> EXPLAIN SELECT COUNT(*) FROM t WHERE (a = 2 OR a IS NULL) AND (b = 5 OR b IS NULL) \G ***** 1. Here is an example of extended EXPLAIN output: mysql> EXPLAIN SELECT t1. If you run EXPLAIN , such conditions typically only cause the type column to be show ref_or_null. In MySQL, EXPLAIN can be used in front of a query beginning with SELECT, INSERT, DELETE, REPLACE, and UPDATE. 0)) = 1 \G ***** 1. mysqlのexplainを使用することで、mysqlがクエリを実行する方法についての実行計画を知ることができます。 この実行計画によってクエリやインデックス最適化を行うことができます。 データ準備. The length is NULL if the key is NULL. The optimizer chooses an index based on the estimated cost to do the least amount of work, not what a human considers the right order. この時点ではまだ普通のような気がしますが、とりあえず、一番基本的な項目がどこに. row ***** id: 1 select_type: PRIMARY table: t1 type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 4 filtered: 100. Why does lean_users show NULL in the ref column? Because this table is leading in the join and you don't filter on any indexed fields. If you look through people's DB structures, you'll see that a lot of people allow NULL values in their database. In this article. Whereas, without the check on NULL , it will be ref. While many people are familiar with the MySQL EXPLAIN command, fewer people are familiar with "explain extended" which was added in MySQL 4. Reversada=1,-1,1)*IFNULL(ctamov. a IN (SELECT t2. EXPLAIN is one of. mysql> CREATE TABLE company_details (contact_id SMALLINT NOT NULL PRIMARY KEY, CompanyName CHAR(25), Designation CHAR(15)); Note: Here we assume that one person will have only one email address. unique_subquery. EXPLAIN can work with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements. ref_or_null works by first doing a read on the reference key, and then a separate search for rows with a NULL key value. The optimizer chooses an index based on the estimated cost to do the least amount of work, not what a human considers the right order. The first could not use the right index, because ref_or_null cannot be used on multiple columns. 发布时间: 2020-04-09 18:51:12 来源: 51CTO 阅读: 370 作者: 樱桃mayue 栏目: MySQL数据库. Thank you for the bug report. Tags: conclusion , condition , Indexes Recommended Today. This SQL tutorial explains how to use the SQL DELETE statement with syntax, examples, and practice exercises. index_merge. If you look through people's DB structures, you'll see that a lot of people allow NULL values in their database. mysql> EXPLAIN SELECT t1. Reversada=1,-1,1)*IFNULL(ctamov. a FROM t2) FROM t1\G ***** 1. Testing with Indexes – Part I EXPLAINed mysql> explain select count(*) from students s left join grades g on (s. 00 Extra: NULL 1 row in set, 1 warning (0. Reversada=1,-1,1)*IFNULL(ctamov. It is not uncommon for an application to use WHERE conditions like this: This particular one comes from the real world, some years ago. 15, “IS NULL Optimization”. You can see information about the plan by prefixing the query with EXPLAIN. If you run EXPLAIN , such conditions typically only cause the type column to be show ref_or_null. NRO_CUENTA , (IF(aut. The EXPLAIN statement provides information about how MySQL executes statements. a IN (SELECT t2. For example, with the default settings, a NULL is written as an unquoted empty string, while an empty string data value is written with double quotes (""). EXPLAIN statement can be used to get information about how SQL statements are executed. The explain_type option. If you’re using the MySQL command line, the tee command will redirect all console output to a. If you run EXPLAIN , such conditions typically only cause the type column to be show ref_or_null. EXPLAIN is one of. Using where无需多说,就是使用了where筛选条件。. a IN (SELECT t2. Good if the prefix doesn't match many rows. All three tables appear in the query design workspace, joined on the appropriate fields. The length is NULL if the key is NULL. ref The ref column shows which columns or constants are used with the key to select rows from the table. The problem is there is a "possible_key" but no key is chosen. row ***** id: 2 select_type: SUBQUERY table: t2 type: index possible_keys: a key: a key_len: 5 ref: NULL. A better alternative is to output the result to a text file and copy the EXPLAIN table from there. Reading values follows similar rules. Set formatting styles for a text box that displays a rich text field. APPLIES TO: Azure Database for MySQL - Single Server Azure Database for MySQL - Flexible Server EXPLAIN is a handy tool to optimize queries. The following output shows an example of the execution of an EXPLAIN statement. 15, “IS NULL Optimization”. 試しに、 MySQL のExplainを実行してみるために、実践 SQL に書いてあるように数十件の店舗情報が入ったテーブル (shops)を用意した上で、以下の SQL を実行してみます。. system: The table has 0 or 1 rows. row ***** id: 1 select_type: SIMPLE table: Region type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 7469 Extra: Using where mysql> explain select count(*) from Region where contains(`geometry`, point(13. The optimizer chooses an index based on the estimated cost to do the least amount of work, not what a human considers the right order. MariaDB/MySQL IS NULL conditions and indexes. mysql> EXPLAIN SELECT COUNT(*) FROM t WHERE (a = 2 OR a IS NULL) AND (b = 5 OR b IS NULL) \G ***** 1. a FROM t2) FROM t1\G ***** 1. Using where无需多说,就是使用了where筛选条件。. row ***** id: 1 select_type: SIMPLE table: t partitions: NULL type: ref_or_null possible_keys: idx_b,idx_a_b key: idx_b key_len: 10 ref: NULL rows: 1815359 filtered: 11. We can use the index cardinality to confirm the likely reason for this decision. 表的读取顺序如何 2. EXPLAIN statement can be used to get information about how SQL statements are executed. : mysql> EXPLAIN SELECT COUNT(*) FROM t WHERE (a = 2 OR a IS NULL) AND (b = 5 OR b IS NULL) \G ***** 1. In MySQL, EXPLAIN can be used in front of a query beginning with SELECT, INSERT, DELETE, REPLACE, and UPDATE. EXPLAIN is one of. Testing with Indexes – Part I EXPLAINed mysql> explain select count(*) from students s left join grades g on (s. 0)) = 1 \G ***** 1. 5、ref_or_null ref_or_null This connection type is similar to ref except that: MySQL Additional search will include NULL Row of values. 注:都是用uid字段做关联. 5、ref_or_null ref_or_null This connection type is similar to ref except that: MySQL Additional search will include NULL Row of values. index_merge. The concept of Nulls enforces the 3-valued-logic in SQL, which is a concrete implementation of the general 3-valued logic. ref_or_null: this is the same as ref but also contains rows with a NULL value from the column. mysqlのexplainを使用することで、mysqlがクエリを実行する方法についての実行計画を知ることができます。 この実行計画によってクエリやインデックス最適化を行うことができます。 データ準備. The optimizer chooses an index based on the estimated cost to do the least amount of work, not what a human considers the right order. student_id is nullG ***** 1. In the following examples, MySQL can use a ref_or_null join to process ref_table: SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL; See Section 8. ANALYZE TABLE doesn't help. mysql> explain select * from City where District = 'California' and population > 10000G ***** 1. mysql> EXPLAIN SELECT t1. ImporteCuotas,0. ref: A non unique index or prefix of an unique index is used to find the rows. a IN (SELECT t2. system: The table has 0 or 1 rows. The concept of Nulls enforces the 3-valued-logic in SQL, which is a concrete implementation of the general 3-valued logic. Reversada=1,-1,1)*IFNULL(ctamov. In this article. 按说 filtered 是个非常有用的值,因为对于join操作,前. While many people are familiar with the MySQL EXPLAIN command, fewer people are familiar with "explain extended" which was added in MySQL 4. In MySQL, EXPLAIN can be used in front of a query beginning with SELECT, INSERT, DELETE, REPLACE, and UPDATE. Right-click the report in the Navigation Pane, and then click Layout View on the shortcut menu. a IN (SELECT t2. When EXPLAIN is used with an explainable statement, MySQL displays information from the optimizer about the statement execution plan. Now if there were a situation where one person has multiple addresses, this design would be a problem. APPLIES TO: Azure Database for MySQL - Single Server Azure Database for MySQL - Flexible Server EXPLAIN is a handy tool to optimize queries. index_merge. When you issue a query, the MySQL Query Optimizer tries to devise an optimal plan for query execution. 0)) = 1 \G ***** 1. 5、ref_or_null ref_or_null This connection type is similar to ref except that: MySQL Additional search will include NULL Row of values. mysql> EXPLAIN ANALYZE FORMAT = TREE SELECT * FROM EMPLOYEE INNER JOIN CONTACT ON CONTACT. 为什么Mysql explain extended中的filtered列值总是100%. In the following examples, MySQL can use a ref_or_null join to process ref_table: SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL; See Section 8. It used the index on (b) instead. : mysql> EXPLAIN SELECT COUNT(*) FROM t WHERE (a = 2 OR a IS NULL) AND (b = 5 OR b IS NULL) \G ***** 1. You can retrieve the information in various formats using the explain_type option. 15, “IS NULL Optimization”. MySQL::Explain::Parser is the parser for result of EXPLAIN of MySQL. 00 Extra: Using index ***** 2. This module provides parse () and parse_vertical () function. This means that each record should be read and evaluated. 00 sec) mysql> SHOW WARNINGS\G ***** 1. Why does lean_users show NULL in the ref column? This causes my query to use a temporary table and a filesort later (when I've added more joins) 14:45:21 (60) > EXPLAIN select * from users u. mysql> CREATE TABLE company_details (contact_id SMALLINT NOT NULL PRIMARY KEY, CompanyName CHAR(25), Designation CHAR(15)); Note: Here we assume that one person will have only one email address. On the Create tab, in the Queries group, click Query Design. ref_or_null works by first doing a read on the reference key, and then a separate search for rows with a NULL key value. 注:都是用uid字段做关联. A better alternative is to output the result to a text file and copy the EXPLAIN table from there. Reversada=1,-1,1)*IFNULL(ctamov. delimiter // drop procedure if exists produce_data // create procedure produce_data() begin declare i int default 0 ; drop table if exists a; drop table if exists b; drop table if exists c; create table a(id int not null,name varchar (32)) ; create table. BrunoS database performance explain mysql performance performance-hub performance-tutorials. Here is an example:. In this article. NULL:MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。 实例. Right-click the report in the Navigation Pane, and then click Layout View on the shortcut menu. This SQL tutorial explains how to use the SQL DELETE statement with syntax, examples, and practice exercises. ref_or_null这种连接类型是这样的 ref,但除此之外,MySQL还会对包含NULL值的行进行额外搜索。此连接类型优化最常用于解析子查询。在以下示例中,MySQL可以使用 ref_or_null联接来处理ref_table:. : mysql> EXPLAIN SELECT COUNT(*) FROM t WHERE (a = 2 OR a IS NULL) AND (b = 5 OR b IS NULL) \G ***** 1. row ***** Level: Note Code: 1003. row ***** id: 2 select_type: SUBQUERY table: t2 type: index possible_keys: a key: a key_len: 5 ref: NULL. Testing with Indexes – Part I EXPLAINed mysql> explain select count(*) from students s left join grades g on (s. row ***** Level: Note Code: 1003. You can see information about the plan by prefixing the query with EXPLAIN. mysql> EXPLAIN SELECT * FROM sales\G ***** 1. NRO_CUENTA , (IF(aut. Many of the clients in the MySQL source distribution are written in C. If you run EXPLAIN , such conditions typically only cause the type column to be show ref_or_null. The length is NULL if the key is NULL. MySQL C API. EXPLAIN is actually correct here - execution of the statement uses 'range' access, not 'ref' or 'const'. In this article. 00 Extra: Using index ***** 2. ref_or_null. mysqlのexplainを使用することで、mysqlがクエリを実行する方法についての実行計画を知ることができます。 この実行計画によってクエリやインデックス最適化を行うことができます。 データ準備. Right-click the report in the Navigation Pane, and then click Layout View on the shortcut menu. In the following examples, MySQL can use a ref_or_null join to process ref_table:. It used the index on (b) instead. EXPLAIN EXTENDED …can show you what the MySQL optimizer does to your query. 試しに、 MySQL のExplainを実行してみるために、実践 SQL に書いてあるように数十件の店舗情報が入ったテーブル (shops)を用意した上で、以下の SQL を実行してみます。. The C API code is distributed with MySQL. この時点ではまだ普通のような気がしますが、とりあえず、一番基本的な項目がどこに. Reversada=1,-1,1)*IFNULL(ctamov. All three tables appear in the query design workspace, joined on the appropriate fields. The following output shows an example of the execution of an EXPLAIN statement. On the Create tab, in the Queries group, click Query Design. A NULL is output as the NULL parameter string and is not quoted, while a non-NULL value matching the NULL parameter string is quoted. ref: different fromeq_ref,refIndicates that if a non unique index is used, many qualified rows will be found. Set formatting styles for a text box that displays a rich text field. a IN (SELECT t2. This module provides parse () and parse_vertical () function. For example, with the default settings, a NULL is written as an unquoted empty string, while an empty string data value is written with double quotes (""). row ***** id: 1 select_type: SIMPLE table: t partitions: NULL type: ref_or_null possible_keys: idx_b,idx_a_b key. 5、ref_or_null ref_or_null This connection type is similar to ref except that: MySQL Additional search will include NULL Row of values. Why does lean_users show NULL in the ref column? This causes my query to use a temporary table and a filesort later (when I've added more joins) 14:45:21 (60) > EXPLAIN select * from users u. EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements. Double-click the two tables that contain the data you want to include in your query and also the junction table that links them, and then click Close. Note that this tells us how many parts of a multi-part key MySQL will actually use. The reason is that single-table UPDATE/DELETE statements go through only a subset of the optimizations that SELECT and multi-table UPDATE/DELETE statements go through. 43 Extra: Using where; Using index 1 row in set, 1 warning (0. 表的读取顺序如何 2. a IN (SELECT t2. ref The ref column shows which columns or constants are used with the key to select rows from the table. It is not uncommon for an application to use WHERE conditions like this: This particular one comes from the real world, some years ago. 00 Extra: Using index ***** 2. ref_or_null. Testing with Indexes – Part I EXPLAINed mysql> explain select count(*) from students s left join grades g on (s. 为什么Mysql explain extended中的filtered列值总是100%. delimiter // drop procedure if exists produce_data // create procedure produce_data() begin declare i int default 0 ; drop table if exists a; drop table if exists b; drop table if exists c; create table a(id int not null,name varchar (32)) ; create table. mysql ref 为 null _ mysql explain 22222 system > const > eq_ ref > ref > fulltext > ref _o MySQL 性能分析及 explain 用法的知识是本文我们主要要介绍的内容,接下来就让我们通过一些实际的例子来介绍这一过程,希望能够对您有所帮助。. ref_or_null: Like 'ref' but in addition another search for the 'null' value is done if the first value was not found. If you’re using the MySQL command line, the tee command will redirect all console output to a. 场景: 有三个表,表2连接表3查询很快,几毫秒;但是表2连接查询表4很慢,耗时几分钟。. mysql> CREATE TABLE company_details (contact_id SMALLINT NOT NULL PRIMARY KEY, CompanyName CHAR(25), Designation CHAR(15)); Note: Here we assume that one person will have only one email address. 00 Extra: Using index 2 rows in set, 1 warning (0. The first could not use the right index, because ref_or_null cannot be used on multiple columns. You can retrieve the information in various formats using the explain_type option. Compare: mysql> explain select count(*) from Region where contains(`geometry`, point(13. On the Create tab, in the Queries group, click Query Design. ref_or_null: Like 'ref' but in addition another search for the 'null' value is done if the first value was not found. row ***** Level: Note Code: 1003. ref_or_null, 和ref类似,但是还要进行一次查询找到NULL的数据。 index_merge, 对于单表查询(无法跨表合并)用到了多个索引的情况,每个索引都可能返回一个结果,Mysql会对结果进行取并集、交集,这就是索引合并了。. In this example, MySQL has to make a choice between the possible indexes as listed in possible_keys. The concept of Nulls enforces the 3-valued-logic in SQL, which is a concrete implementation of the general 3-valued logic. id = EMPLOYEE. student_id and test_id=2) where g. You might not know this, but MySQL can dramatically change your query before it actually executes it. mysql> explain select * from trace_sp_info; 这种需求只能遍历全表. If you run EXPLAIN , such conditions typically only cause the type column to be show ref_or_null. 0)) AS ImporteTotalCuotas , (IF(aut. Cloud Firestore is a flexible, scalable database for mobile, web, and server development from Firebase and Google Cloud. The key_len column indicates the length of the key that MySQL decided to use. For example, with the default settings, a NULL is written as an unquoted empty string, while an empty string data value is written with double quotes (""). Here is an example of extended EXPLAIN output: mysql> EXPLAIN SELECT t1. ref: different fromeq_ref,refIndicates that if a non unique index is used, many qualified rows will be found. この時点ではまだ普通のような気がしますが、とりあえず、一番基本的な項目がどこに. The Null marker indicates the absence of a value, and is distinct from a value of 0 for an integer column or an empty string for a text column. MySQL对于IS NULL运算符执行相同的优化方式与等于(=)运算符相同。 例如,MySQL在使用IS NULL运算符搜索NULL时使用索引,如以下查询所示: SELECT customerNumber, salesRepEmployeeNumber FROM customers WHERE salesRepEmployeeNumber IS NULL; 查看EXPLAIN查询过程:. 使用explain 语句去查看分析结果如 explain. mysqlのexplainを使用することで、mysqlがクエリを実行する方法についての実行計画を知ることができます。 この実行計画によってクエリやインデックス最適化を行うことができます。 データ準備. The length is NULL if the key is NULL. You can retrieve the information in various formats using the explain_type option. Why does lean_users show NULL in the ref column? This causes my query to use a temporary table and a filesort later (when I've added more joins) 14:45:21 (60) > EXPLAIN select * from users u. row ***** id: 2 select_type: SUBQUERY table: t2 type: index possible_keys: a key: a key_len: 5 ref: NULL rows: 3 filtered: 100. a IN (SELECT t2. row ***** id: 2 select_type: SUBQUERY table: t2 type: index possible_keys: a key: a key_len: 5 ref. On the Create tab, in the Queries group, click Query Design. Thank you for the bug report. In the following examples, MySQL can use a ref_or_null join to process ref_table: SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL; See Section 8. row ***** id: 1 select_type: PRIMARY table: t1 type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 4 filtered: 100. When you issue a query, the MySQL Query Optimizer tries to devise an optimal plan for query execution. The length is NULL if the key is NULL. NULL:MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。 实例. row ***** id: 1 select_type: SIMPLE table: s type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 50000 Extra: Using index ***** 2. Set formatting styles for a text box that displays a rich text field. MariaDB/MySQL IS NULL conditions and indexes. This join type optimization is used most often in resolving subqueries. 大部分情况下利用索引排序更快,所以一般这时. row ***** id: 1 select_type: SIMPLE table: sales partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 9991 filtered: 100. Reversada=1,-1,1)*IFNULL(ctamov. This means that each record should be read and evaluated. MySql学习笔记(八):explain之extra. The optimizer chooses an index based on the estimated cost to do the least amount of work, not what a human considers the right order. 7缺省就会输出 filtered ),它指返回结果的行占需要读到的行 (rows列的值)的百分比。. row ***** id: 2 select_type: SUBQUERY table: t2 type: index possible_keys: a key: a key_len: 5 ref. index_merge. 43 Extra: Using where; Using index 1 row in set, 1 warning (0. EXPLAIN is one of. On the Create tab, in the Queries group, click Query Design. a IN (SELECT t2. 执行Mysql的explain extended的输出会比单纯的 explain多一列 filtered (MySQL 5. If you look through people's DB structures, you'll see that a lot of people allow NULL values in their database. 标签 mysql explain 字段 解释 栏目 MySQL 繁體版. Here is an example:. 使用explain 语句去查看分析结果如 explain. mysql> explain select * from trace_sp_info; 这种需求只能遍历全表. Thank you for the bug report. If you’re using the MySQL command line, the tee command will redirect all console output to a. The concept of Nulls enforces the 3-valued-logic in SQL, which is a concrete implementation of the general 3-valued logic. system: The table has 0 or 1 rows. row ***** id: 1 select_type: SIMPLE table: s type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 50000 Extra: Using index ***** 2. Why does lean_users show NULL in the ref column? Because this table is leading in the join and you don't filter on any indexed fields. student_id = g. It is not uncommon for an application to use WHERE conditions like this: This particular one comes from the real world, some years ago. ImporteCuotas,0. In this article. MySQL IS NULL优化. 场景: 有三个表,表2连接表3查询很快,几毫秒;但是表2连接查询表4很慢,耗时几分钟。. Using where无需多说,就是使用了where筛选条件。. mysql> explain select * from trace_sp_info; 这种需求只能遍历全表. 5、ref_or_null ref_or_null This connection type is similar to ref except that: MySQL Additional search will include NULL Row of values. index_merge. If you are looking for examples that demonstrate how to use the C API, take a look at these clients. MySQL C API. The reason is that single-table UPDATE/DELETE statements go through only a subset of the optimizations that SELECT and multi-table UPDATE/DELETE statements go through. In this example, MySQL has to make a choice between the possible indexes as listed in possible_keys. 00 sec) mysql> SHOW WARNINGS\G ***** 1. MySQL IS NULL优化. Reversada=1,-1,1)*IFNULL(ctamov. mysql> EXPLAIN SELECT t1. When EXPLAIN is used with an explainable statement, MySQL displays information from the optimizer about the statement execution plan. This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values. a IN (SELECT t2. row ***** id: 2 select_type: SUBQUERY table: t2 type: index possible_keys: a key: a key_len: 5 ref: NULL. NULL:MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。 实例. In the following examples, MySQL can use a ref_or_null join to process ref_table: SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL; See Section 8. 43 Extra: Using where; Using index 1 row in set, 1 warning (0. This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values. ref_or_null: this is the same as ref but also contains rows with a NULL value from the column. I'm struggling to find the right index to the query shown below (I have improved it from minutes to 20 seconds but it needs to be faster): explain extended SELECT cta. 大部分情况下利用索引排序更快,所以一般这时. The first could not use the right index, because ref_or_null cannot be used on multiple columns. Why NULL Values Should Not Be Used in a Database Unless Required. Click the text box that displays the rich text field, and then, on the Format tab, in the Font group, click the formatting style that you want to apply. 一种非常常见的表达式组合是:某列等于某个值或为空 (col_name = expr OR col_name IS NULL)。MySQL 同样会优化这条表达式,同时,EXPLAIN 会出现 ref_or_null 的字样。 ref_or_null 的工作方式是,先用索引列对表进行读取,然后另一个查询搜索索引列为 NULL 的记录。这是一个. row ***** Level: Note Code: 1003. EXPLAIN is actually correct here - execution of the statement uses 'range' access, not 'ref' or 'const'. mysqlのexplainを使用することで、mysqlがクエリを実行する方法についての実行計画を知ることができます。 この実行計画によってクエリやインデックス最適化を行うことができます。 データ準備. EXPLAIN works with SELECT, DELETE, INSERT , REPLACE, and MySQL can use a ref_or_null join to process ref_table: SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL; See Section 8. Right-click the report in the Navigation Pane, and then click Layout View on the shortcut menu. We'll first analyze the original query, then attempt to optimize the query and look into the optimized query's execution plan to see what changed and why. EXPLAIN statement can be used to get information about how SQL statements are executed. row ***** id: 1 select_type: PRIMARY table: t1 type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 4 filtered: 100. These function receive the result of EXPLAIN or EXPLAIN EXTENDED, and return the parsed result as array reference that contains hash reference. Duplicates. The EXPLAIN statement provides information about how MySQL executes statements. mysql> EXPLAIN ANALYZE FORMAT = TREE SELECT * FROM EMPLOYEE INNER JOIN CONTACT ON CONTACT. Good if the prefix doesn't match many rows. Click the text box that displays the rich text field, and then, on the Format tab, in the Font group, click the formatting style that you want to apply. This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values. row ***** Level: Note Code: 1003. Set formatting styles for a text box that displays a rich text field. row ***** id: 1 select_type: PRIMARY table: t1 type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 4 filtered: 100. In this article. Testing with Indexes – Part I EXPLAINed mysql> explain select count(*) from students s left join grades g on (s. The concept of Null is the subject of some debates. index_merge. It used the index on (b) instead. APPLIES TO: Azure Database for MySQL - Single Server Azure Database for MySQL - Flexible Server EXPLAIN is a handy tool to optimize queries. ref_or_null, 和ref类似,但是还要进行一次查询找到NULL的数据。 index_merge, 对于单表查询(无法跨表合并)用到了多个索引的情况,每个索引都可能返回一个结果,Mysql会对结果进行取并集、交集,这就是索引合并了。. index_merge. mysql> EXPLAIN SELECT t1. This SQL tutorial explains how to use the SQL DELETE statement with syntax, examples, and practice exercises. Now if there were a situation where one person has multiple addresses, this design would be a problem. EXPLAIN EXTENDED …can show you what the MySQL optimizer does to your query. index_merge. In the following examples, MySQL can use a ref_or_null join to process ref_table: SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL; See Section 8. MySQL对于IS NULL运算符执行相同的优化方式与等于(=)运算符相同。 例如,MySQL在使用IS NULL运算符搜索NULL时使用索引,如以下查询所示: SELECT customerNumber, salesRepEmployeeNumber FROM customers WHERE salesRepEmployeeNumber IS NULL; 查看EXPLAIN查询过程:. a FROM t2) FROM t1\G ***** 1. The reason is that single-table UPDATE/DELETE statements go through only a subset of the optimizations that SELECT and multi-table UPDATE/DELETE statements go through. EXPLAIN works with SELECT, DELETE, INSERT , REPLACE, and MySQL can use a ref_or_null join to process ref_table: SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL; See Section 8. The explain_type option. row ***** Level: Note Code: 1003. ANALYZE TABLE doesn't help. If you’re using the MySQL command line, the tee command will redirect all console output to a. A better alternative is to output the result to a text file and copy the EXPLAIN table from there. a IN (SELECT t2. The EXPLAIN statement provides information about how MySQL executes statements. MySQL有两种方式可以生成有序的结果,通过排序操作或者使用索引,当Extra中出现了Using filesort 说明MySQL使用了后者,但注意虽然叫filesort但并不是说明就是用了文件来进行排序,只要可能排序都是在内存里完成的。. student_id = g. mysql> EXPLAIN SELECT t1. You might not know this, but MySQL can dramatically change your query before it actually executes it. This join type optimization is used most often in resolving subqueries. ImporteCuotas,0. index_merge. The SQL DELETE statement is a used to delete one or more records from a table. unique_subquery. It used the index on (b) instead. A better alternative is to output the result to a text file and copy the EXPLAIN table from there. ref_or_null works by first doing a read on the reference key, and then a separate search for rows with a NULL key value. This is the first article in a series of posts. Note that this tells us how many parts of a multi-part key MySQL will actually use. MySQL C API. student_id is nullG ***** 1. mysql ref 为 null _ mysql explain 22222 system > const > eq_ ref > ref > fulltext > ref _o MySQL 性能分析及 explain 用法的知识是本文我们主要要介绍的内容,接下来就让我们通过一些实际的例子来介绍这一过程,希望能够对您有所帮助。. mysql> EXPLAIN SELECT t1. extra主要有是那种情况:Using index、Using filesort、Using temporary、Using where. 00 Extra: NULL 1 row in set, 1 warning (0. 0)) = 1 \G ***** 1. a IN (SELECT t2. Like Firebase Realtime Database, it keeps your data in sync across client apps through realtime listeners and offers offline support for mobile and web so you can build responsive apps that work regardless of network latency or Internet connectivity. On the Create tab, in the Queries group, click Query Design. NULL:MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。 实例. row ***** id: 1 select_type: SIMPLE table: s type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 50000 Extra: Using index ***** 2. 00 sec) mysql> SHOW WARNINGS\G ***** 1. 以前、 mysqlを高速化する10の方法 という投稿で 「explainの見方についてはいずれ解説しようと思う」 と書いてしまったので、今日はその公約? を果たそうと思う。 mysqlのチューニングで最も大切なのは、クエリとスキーマの最適化である。スキーマの設計は一度決めてしまうとそのテーブルを. Now if there were a situation where one person has multiple addresses, this design would be a problem. row ***** Level: Note Code: 1003. mysql> EXPLAIN ANALYZE FORMAT = TREE SELECT * FROM EMPLOYEE INNER JOIN CONTACT ON CONTACT. ref_or_null, 和ref类似,但是还要进行一次查询找到NULL的数据。 index_merge, 对于单表查询(无法跨表合并)用到了多个索引的情况,每个索引都可能返回一个结果,Mysql会对结果进行取并集、交集,这就是索引合并了。. EXPLAIN is one of. row ***** id: 1 select_type: PRIMARY table: t1 type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 4 filtered: 100. When EXPLAIN is used with an explainable statement, MySQL displays information from the optimizer about the statement execution plan. 标签 mysql explain 字段 解释 栏目 MySQL 繁體版. APPLIES TO: Azure Database for MySQL - Single Server Azure Database for MySQL - Flexible Server EXPLAIN is a handy tool to optimize queries. In this article. row ***** id: 1 select_type: SIMPLE table: t partitions: NULL type: ref_or_null possible_keys: idx_b,idx_a_b key: idx_b key_len: 10 ref: NULL rows: 1815359 filtered: 11. ref_or_null works by first doing a read on the reference key, and then a separate search for rows with a NULL key value. 00 Extra: Using index ***** 2. You can retrieve the information in various formats using the explain_type option. 43 Extra: Using where; Using index 1 row in set, 1 warning (0. The length is NULL if the key is NULL. It used the index on (b) instead. In this article. 以前、 mysqlを高速化する10の方法 という投稿で 「explainの見方についてはいずれ解説しようと思う」 と書いてしまったので、今日はその公約? を果たそうと思う。 mysqlのチューニングで最も大切なのは、クエリとスキーマの最適化である。スキーマの設計は一度決めてしまうとそのテーブルを. row ***** Level: Note Code: 1003. row ***** id: 2 select_type: SUBQUERY table: t2 type: index possible_keys: a key: a key_len: 5 ref: NULL. This SQL tutorial explains how to use the SQL DELETE statement with syntax, examples, and practice exercises. row ***** id: 1 select_type: SIMPLE table: Region type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 7469 Extra: Using where mysql> explain select count(*) from Region where contains(`geometry`, point(13. We'll first analyze the original query, then attempt to optimize the query and look into the optimized query's execution plan to see what changed and why. A NULL is output as the NULL parameter string and is not quoted, while a non-NULL value matching the NULL parameter string is quoted. The concept of Null is the subject of some debates. I'm struggling to find the right index to the query shown below (I have improved it from minutes to 20 seconds but it needs to be faster): explain extended SELECT cta. For a simple query, it would look like the following:. The first could not use the right index, because ref_or_null cannot be used on multiple columns. A better alternative is to output the result to a text file and copy the EXPLAIN table from there. ref_or_null. row ***** id: 1 select_type: PRIMARY table: t1 type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 4 filtered: 100. ref_or_null这种连接类型是这样的 ref,但除此之外,MySQL还会对包含NULL值的行进行额外搜索。此连接类型优化最常用于解析子查询。在以下示例中,MySQL可以使用 ref_or_null联接来处理ref_table:. a IN (SELECT t2. mysql ref 为 null _ mysql explain 22222 system > const > eq_ ref > ref > fulltext > ref _o MySQL 性能分析及 explain 用法的知识是本文我们主要要介绍的内容,接下来就让我们通过一些实际的例子来介绍这一过程,希望能够对您有所帮助。. If you run EXPLAIN , such conditions typically only cause the type column to be show ref_or_null. The key_len column indicates the length of the key that MySQL decided to use. This module treat SQL's NULL as Perl's undef. student_id and test_id=2) where g. This is the first article in a series of posts. 0)) AS ImporteTotalConsumos , (IF(aut. Reversada=1,-1,1)*IFNULL(ctamov. mysql ref 为 null _ mysql explain 22222 system > const > eq_ ref > ref > fulltext > ref _o MySQL 性能分析及 explain 用法的知识是本文我们主要要介绍的内容,接下来就让我们通过一些实际的例子来介绍这一过程,希望能够对您有所帮助。. ref_or_null这种连接类型是这样的 ref,但除此之外,MySQL还会对包含NULL值的行进行额外搜索。此连接类型优化最常用于解析子查询。在以下示例中,MySQL可以使用 ref_or_null联接来处理ref_table:. EXPLAIN statement can be used to get information about how SQL statements are executed. On the Create tab, in the Queries group, click Query Design. In this article. This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values. These function receive the result of EXPLAIN or EXPLAIN EXTENDED, and return the parsed result as array reference that contains hash reference. If you look through people's DB structures, you'll see that a lot of people allow NULL values in their database. APPLIES TO: Azure Database for MySQL - Single Server Azure Database for MySQL - Flexible Server EXPLAIN is a handy tool to optimize queries. 执行Mysql的explain extended的输出会比单纯的 explain多一列 filtered (MySQL 5. Whereas, without the check on NULL , it will be ref. NULL:MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。 实例. For a simple query, it would look like the following:. EXPLAIN can work with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements. If you’re using the MySQL command line, the tee command will redirect all console output to a. 43 Extra: Using where; Using index 1 row in set, 1 warning (0. MySQL对于IS NULL运算符执行相同的优化方式与等于(=)运算符相同。 例如,MySQL在使用IS NULL运算符搜索NULL时使用索引,如以下查询所示: SELECT customerNumber, salesRepEmployeeNumber FROM customers WHERE salesRepEmployeeNumber IS NULL; 查看EXPLAIN查询过程:. This is the first article in a series of posts. ANALYZE TABLE doesn't help. row ***** id: 1 select_type: SIMPLE table: t partitions: NULL type: ref_or_null possible_keys: idx_b,idx_a_b key: idx_b key_len: 10 ref: NULL rows: 1815359 filtered: 11. row ***** id: 1 select_type: SIMPLE table: Region type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 7469 Extra: Using where mysql> explain select count(*) from Region where contains(`geometry`, point(13. In this article. The EXPLAIN statement provides information about how MySQL executes statements. 大部分情况下利用索引排序更快,所以一般这时. The Null marker indicates the absence of a value, and is distinct from a value of 0 for an integer column or an empty string for a text column. Why does lean_users show NULL in the ref column? Because this table is leading in the join and you don't filter on any indexed fields. This join type optimization is used most often in resolving subqueries. It used the index on (b) instead. Tags: conclusion , condition , Indexes Recommended Today. Like Firebase Realtime Database, it keeps your data in sync across client apps through realtime listeners and offers offline support for mobile and web so you can build responsive apps that work regardless of network latency or Internet connectivity. The problem is there is a "possible_key" but no key is chosen. The reason is that single-table UPDATE/DELETE statements go through only a subset of the optimizations that SELECT and multi-table UPDATE/DELETE statements go through. index_merge. ImporteCuotas,0. row ***** id: 1 select_type: PRIMARY table: t1 type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 4 filtered: 100. 00 sec) mysql> SHOW WARNINGS\G ***** 1. In MySQL, EXPLAIN can be used in front of a query beginning with SELECT, INSERT, DELETE, REPLACE, and UPDATE. Note that this tells us how many parts of a multi-part key MySQL will actually use. NULL:MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。 实例. This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values. row ***** id: 1 select_type: SIMPLE table: t partitions: NULL type: ref_or_null possible_keys: idx_b,idx_a_b key. ref: different fromeq_ref,refIndicates that if a non unique index is used, many qualified rows will be found. You might not know this, but MySQL can dramatically change your query before it actually executes it. MySQL有两种方式可以生成有序的结果,通过排序操作或者使用索引,当Extra中出现了Using filesort 说明MySQL使用了后者,但注意虽然叫filesort但并不是说明就是用了文件来进行排序,只要可能排序都是在内存里完成的。. 5、ref_or_null ref_or_null This connection type is similar to ref except that: MySQL Additional search will include NULL Row of values. The concept of Nulls enforces the 3-valued-logic in SQL, which is a concrete implementation of the general 3-valued logic. a IN (SELECT t2. 0)) AS ImporteTotalConsumos , (IF(aut. : mysql> EXPLAIN SELECT COUNT(*) FROM t WHERE (a = 2 OR a IS NULL) AND (b = 5 OR b IS NULL) \G ***** 1. If you look through people's DB structures, you'll see that a lot of people allow NULL values in their database. It used the index on (b) instead. Click the text box that displays the rich text field, and then, on the Format tab, in the Font group, click the formatting style that you want to apply. row ***** id: 1 select_type: SIMPLE table: t partitions: NULL type: ref_or_null possible_keys: idx_b,idx_a_b key: idx_b key_len: 10 ref: NULL rows: 1815359 filtered: 11. If you’re using the MySQL command line, the tee command will redirect all console output to a. For example, with the default settings, a NULL is written as an unquoted empty string, while an empty string data value is written with double quotes (""). This join type optimization is used most often in resolving subqueries. This SQL tutorial explains how to use the SQL DELETE statement with syntax, examples, and practice exercises. Duplicates. If you run EXPLAIN , such conditions typically only cause the type column to be show ref_or_null. When you issue a query, the MySQL Query Optimizer tries to devise an optimal plan for query execution. MySql学习笔记(八):explain之extra. ref: A non unique index or prefix of an unique index is used to find the rows. Now if there were a situation where one person has multiple addresses, this design would be a problem. a FROM t2) FROM t1\G ***** 1. 00 Extra: Using index ***** 2. Duplicates. row ***** id: 1 select_type: PRIMARY table: t1 type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 4 filtered: 100. A better alternative is to output the result to a text file and copy the EXPLAIN table from there. mysql> EXPLAIN SELECT * FROM sales\G ***** 1. index_merge. This join type optimization is used most often in resolving subqueries. The key_len column indicates the length of the key that MySQL decided to use. This module treat SQL's NULL as Perl's undef. ref: different fromeq_ref,refIndicates that if a non unique index is used, many qualified rows will be found.