What exactly is the concept of const type in explain in Mysql, and how is it different from eq_ref?

< H2 > const: normally, if you place a primary key after where as a conditional query, the mysql optimizer can turn the query optimization into a constant. As for how and when to convert, it depends on the optimizer. < / H2 >

what is the difference between this and eq_ref?

Mysql
Mar.12,2021

in a nutshell, const is read directly by pressing the primary key or unique key, and eq_ref is used in the case of join table query, by pressing the primary key or unique key of the join table.

the following is translated from official file :

const
the table has at most one matching row, which is read at the beginning of the query. Because there is only one row, the values of the columns in that row can be treated as constants by the rest of the optimizer. Const tables are very fast because they are read only once.

const is used to compare all parts of the Primary key or unique index with constant values. In the following query, tbl_name can be used as the const table: 

SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name
  WHERE primary_key_part1=1 AND primary_key_part2=2;

eq_ref

reads a row composed of each row in this table and in the associated table. This is the best join type except for the system and const types. This value is used when the join uses all parts of the index and the index is a primary key or the only non-NULL index.
eq_ref can be used for index columns that are compared using the = operator. The comparison value can be a constant or an expression of a column in a table that was read before using this table. In the following example, MySQL can use eq_ref connection (join) ref_table to handle: 

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

all of the following parse the meaning of each attribute of explain in detail:

clipboard.png

:
id:
select_type:

  • SIMPLE:UNION
  • :PRIMARY
  • SELECTWHERE:SUBQUERY

table:
type:
clipboard.png

from left to right, performance changes from poor to good

  1. ALL: scan the whole table
  2. index: scans all index trees
  3. range: scans partial indexes and scans the range of indexes. Scanning of the index starts at a certain point and returns rows that match the range of values. It is common in queries such as between, <, >, etc.
  4. ref: lookups using non-unique indexes or non-unique index prefixes
    ( difference between eq_ref and const: )
  5. eq_ref: unique index scan, with only one record in the table matching for each index key. Common for primary key or unique index scanning
  6. const, system: has at most one matching row in a single table, which can be queried very quickly, such as by primary key or unique index. System is a special case of the const type, using system when the query table has only one row.
  7. NULL: can get results directly without accessing tables or indexes, such as select 1 from test where 1 .

possible_keys: indicates the index that may be used in the query. If it is empty, there is no relevant index. To improve performance at this point, check the WHERE clause to see if some fields are referenced, or check that the fields are not suitable for the index

key: shows the index that MySQL actually decides to use. If no index is selected, it is NULL

key_len: use the length of the index field

Note: the value displayed by key_len is the maximum possible length of the index field, not the actual length used, that is, the key_len is calculated based on the table definition, not retrieved within the table.

ref: shows which field or constant is used with key

rows: this number indicates how much data mysql has to traverse before it can be found. It means that MySQL estimates the number of rows needed to find the required records based on table statistics and index selection, which may be inaccurate on innodb

.

Extra: description and description of the execution. Contains additional information that is not suitable for display in other columns but is very important.

  1. Using index: means to use an index. If only Using index, indicates that he did not query the data table, only the index table is used to complete the query. This is called an override index.
  2. Using where: represents a conditional query, and Using where occurs if you don't read all the data in the table, or if you can get all the data you need through the index alone.

Previous: BootstraoTable dynamic editing cell

Next: How to determine the date element in python requests.post?

css mysql arrays josn react html typescript webpack npm sass R objective-c .net sql-server jquery python-3.x angularjs django angular excel regex iphone ajax linux xml pandas vba spring database wordpress string wpf xcode windows bash postgresql oracle multithreading eclipse list firebase algorithm macos forms image scala visual-studio azure bootstrap spring-boot react-native python-2.7 docker performance function winforms matlab powershell apache dataframe api sqlite numpy rest shell selenium flutter dart maven loops qt swing android-studio csv express file class tensorflow sorting codeigniter perl
MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1e4987a-44d4e.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1e4987a-44d4e.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?