MySQL Manual Version 5.0.20-MySQL Optimization (2) (1) (2)_PHP Tutorial

There is at most one matching row in the table, which will be read at the beginning of the query. Since there is only one row of records, the field value in that row can be treated as a constant value for the rest of the optimization process. Const tables are very fast to query because they only need to be read once! const is used when there are fixed value comparisons with PRIMARY KEY or UNIQUE indexes. In the following queries, tbl_name is a 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

A row of records will be read from this table to match the previous one The records read from the table are combined. Unlike const types, this is the best connection type. It is used when all parts of the index are used for joins and the index is of type PRIMARY KEY or UNIQUE. eq_ref can be used to retrieve fields when doing "=" comparisons. The compared value can be a fixed value or an expression. The fields in the table can be used in the expression, and they have been prepared before reading the table. In the following examples, MySQL uses the eq_ref connection to process ref_table:

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;

ref

All records in this table that match the search value will be fetched and combined with the records fetched from the previous table. ref is used when the joiner uses the leftmost prefix of the key or when the key is not a PRIMARY KEY or UNIQUE index (in other words, the joiner cannot get only one record based on the key value). This is a good connection type when only a few matching records are queried based on key values. ref can also be used when retrieval fields are compared using the = operator. In the following examples, MySQL will use ref to process ref_table:

http://www.bkjia.com/PHPjc/630972.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/630972.htmlTechArticleThere is at most one row of matching records in the table, which will be read at the beginning of the query. Since there is only one row of records, the field values ​​of this row of records can be...