Using explain to see MySQL's execution plan often see impossible WHERE noticed after reading const tables This sentence, meaning that MySQL reads "Const tables" by reading It is not possible to find the result output for this query. For example, the following table and data:
create table t (a int primary key, b int) engine = innodb;
insert into t values(1, 1);
insert into t values(3, 1);
The "impossible where noticed after reading const tables" is output when the "EXPLAIN select * from t where a = 2" is executed.
Do not understand what the so-called "Const tables" mean, to MySQL in the query optimization when it can be found that a query cannot output the results of the feeling is incredible. According to the "traditional" approach in the database, query optimization only accesses schema definitions and statistics, and as far as I know, the various statistics used in the database, such as equidepth, MaxDiff histogram, MCV, the maximum value of the attribute, the minimum value, etc., cannot be accurate enough to assert that there is no "a" in the table above. = 2 "of the record.
Today read MySQL internal manual only finally understand, originally MySQL is not what magic, this impossible WHERE noticed after reading const tables conclusion is not through statistical information made, Instead of actually accessing the data once, we find that there is really no "a = 2" line.
When a table in a query specifies an equivalent condition on a primary key or a non-null unique index, so that at most one hit result is possible (only for that table), MySQL will first find the corresponding record based on this condition before explain. Replace all the properties in the query with the attributes from the table with the actual value of the record. A more complex example is as follows:
The output result of explain select * from t as T1, t as T2 where T1. A = 1 and T2. A = T1. B + 1; is (some output contents are omitted due to the typesetting relationship):
+----+...+-----------------------------------------------------+
| id | ... | Extra |
+----+...+-----------------------------------------------------+
| 1 | ... | Impossible WHERE noticed after reading const tables |
+----+...+-----------------------------------------------------+
MySQL concludes that the above query will not output results as follows:
1, first according to t1.a = 1 conditions to find a record (a);
2. Replace the value of B in the above record with the t1.b in the query, and turn the above query into an equivalent "explain select 1, 1,t2.a, t2.b from T as t2 where t2.a = 1 + 1";
3, the optimizer calculates the value of the constant expression, that is, the calculation of the result is 2;
4, the optimizer according to t2.a = 2 condition search, found no hit record;
5, the optimizer finally interrupted the above query can not output results.
Plainly, this "impossible WHERE noticed after reading const tables" is no longer mysterious. But from this, I feel more like MySQL is a "strange" database, there are many places with the usual practice is not the same. Many databases will take precedence over tables that specify a unique index equivalence condition at the time of the join as the first step in query execution, but as far as I know, MySQL only takes this step forward to the first step in query optimization. It seems like a very delicate question to be able to do this in any case, and there is no difference between the two examples given in this article and the cost of doing this at the time of optimization or execution. But it doesn't seem to hurt to do so.
This can lead to a "strange" phenomenon, that is, explain can sometimes be blocked. For example, "EXPLAIN select * from t where a = 2 lock in Share mode", while another transaction inserts a record of a = 2 without committing, EXPLAIN will be there to wait for the lock.
MySQL Supernatural: Impossible WHERE noticed after reading const tables
