1.2.2 PostgreSQL
1.2.2.1 S6 Statement
Review the query execution plan, and the subquery is optimized (materialized so that the subquery results are cached, and the subquery is executed only once).
postgres=# EXPLAIN SELECT * from T3 WHERE b3 >= "Any" (select B1 from T1);
Nested Loop Semi Join (cost=0.00..41707.39 rows=680 width=12)
Join Filter: (t3.b3 >= t1.b1)
-> Seq Scan on t3 (cost=0.00..30.40 rows=2040 width=12)
-> materialize (cost=0.00..40.60 rows=2040 width=4)
-> Seq Scan on T1 (cost=0.00..30.40 rows=2040 width=4)
1.2.2.2 S7 Statement
View the query execution plan, and the subquery is optimized (with a semi-connection).
postgres=# EXPLAIN SELECT * from T3 WHERE b3 >= "Any" (select A1 from T1);
Nested Loop Semi Join (cost=0.15..377.82 rows=680 width=12)
-> Seq Scan on t3 (cost=0.00..30.40 rows=2040 width=12)
-> Index Scan using T1_a1_key on T1 (cost=0.15..12.08 rows=680 width=4)
Index Cond: (A1 <= t3.b3)
1.2.2.3 S8 Statement
View the query execution plan, and the subquery is optimized (with a semi-connection).
postgres=# EXPLAIN SELECT * from T3 WHERE b3 <= SOME (select A1 from T1);
Nested Loop Semi Join (cost=0.15..377.82 rows=680 width=12)
-> Seq Scan on t3 (cost=0.00..30.40 rows=2040 width=12)
-> Index Scan using T1_a1_key on T1 (cost=0.15..12.08 rows=680 width=4)
Index Cond: (A1 >= t3.b3)
1.2.2.4 S9 Statement
View the query execution plan, and the subquery is optimized.
postgres=# EXPLAIN SELECT * from t3 WHERE b3 = SOME (select A1 from T1);
Hash Semi Join (cost=55.90..103.00 rows=1020 width=12)
Hash Cond: (t3.b3 = t1.a1)
-> Seq Scan on t3 (cost=0.00..30.40 rows=2040 width=12)
-> Hash (cost=30.40..30.40 rows=2040 width=4)
-> Seq Scan on T1 (cost=0.00..30.40 rows=2040 width=4)
1.2.2.5 S10 Statement
View the query execution plan and the subquery has not been optimized.
postgres=# EXPLAIN SELECT * from T3 WHERE b3 <= All (select A1 from T1);
Seq Scan on t3 (cost=0.00..46649.50 rows=1020 width=12)
Filter: (Subplan 1)
Subplan 1
-> materialize (cost=0.00..40.60 rows=2040 width=4)
-> Seq Scan on T1 (cost=0.00..30.40 rows=2040 width=4)
1.2.2.6 S11 Statement
View the query execution plan, and the subquery is optimized (with a semi-connection).
View the query execution plan and the subquery has not been optimized.
postgres=# EXPLAIN SELECT * from T3 where B3 <= all (select A1 from T1 where a3=a1);
Seq Scan on t3 (cost=0.00..8527.00 rows=1020 width=12)
Filter: (Subplan 1)
Subplan 1
-> Index Scan using T1_a1_key on T1 (cost=0.15..8.17 Rows=1 width=4)
Index Cond: (a1 = t3.a3)
1.2.3 MySQL
1.2.3.1 S6 Statement
View the query execution plan and the subquery has not been optimized.
Mysql> EXPLAIN SELECT * from T3 WHERE b3 >= "Any" (select B1 from T1);
+----+-------------+-------+------+------+-------------+
| ID | Select_type | Table | Type | Key | Extra |
+----+-------------+-------+------+------+-------------+
| 1 | PRIMARY | T3 | All | NULL | Using where |
| 2 | subquery | T1 | All | NULL | NULL |
+----+-------------+-------+------+------+-------------+
2 rows in Set (0.04 sec)
1.2.3.2 S7 Statement
Viewing the query execution plan, subqueries are not optimized (but note that "Select tables optimized Away" indicates that subqueries are optimized with unique techniques, but not regular subqueries are Oberra optimized).
Mysql> EXPLAIN SELECT * from T3 WHERE b3 >= "Any" (select A1 from T1);
+----+-------------+-------+------+------+------------------------------+
| ID | Select_type | Table | Type | Key | Extra |
+----+-------------+-------+------+------+------------------------------+
| 1 | PRIMARY | T3 | All | NULL | Using where |
| 2 | subquery | NULL | NULL | NULL | Select Tables Optimized Away |
+----+-------------+-------+------+------+------------------------------+
2 rows in Set (0.00 sec)
1.2.3.3 S8 Statement
View the query execution plan, and the subquery is optimized (with a semi-connection).
Mysql> EXPLAIN SELECT * from T3 WHERE b3 <= SOME (select A1 from T1);
+----+-------------+-------+------+------+------------------------------+
| ID | Select_type | Table | Type | Key | Extra |
+----+-------------+-------+------+------+------------------------------+
| 1 | PRIMARY | T3 | All | NULL | Using where |
| 2 | subquery | NULL | NULL | NULL | Select Tables Optimized Away |
+----+-------------+-------+------+-----+------------------------------+
2 rows in Set (0.00 sec)
1.2.3.4 S9 Statement
View the query execution plan, and the subquery is optimized.
Mysql> EXPLAIN SELECT * from t3 WHERE b3 = SOME (select A1 from T1);
+----+-------------+-------+-------+------+----------------------------------------------------+
| ID | Select_type | Table | Type | Key | Extra |
+----+-------------+-------+-------+------+----------------------------------------------------+
| 1 | Simple | T1 | Index | A1 | Using Index |
| 1 | Simple | T3 | All | NULL | The Using where; Using Join buffer (block Nested Loop) |
+----+-------------+-------+-------+------+----------------------------------------------------+
2 rows in Set (0.00 sec)
1.2.3.5 S10 Statement
View the query execution plan and the subquery has not been optimized.
Mysql> EXPLAIN SELECT * from T3 WHERE b3 <= All (select A1 from T1);
+----+-------------+-------+-------+------+-------------+
| ID | Select_type | Table | Type | Key | Extra |
+----+-------------+-------+-------+------+-------------+
| 1 | PRIMARY | T3 | All | NULL | Using where |
| 2 | subquery | T1 | Index | A1 | Using Index |
+----+-------------+-------+-------+------+-------------+
2 rows in Set (0.00 sec)
1.2.3.6 S11 Statement
View the query execution plan and the subquery has not been optimized.
Mysql> EXPLAIN SELECT * from T3 where B3 <= all (select A1 from T1 where a3=a1);
+----+--------------------+-------+------+------+--------------------------+
| ID | Select_type | Table | Type | Key | Extra |
+----+--------------------+-------+------+------+--------------------------+
| 1 | PRIMARY | T3 | All | NULL | Using where |
| 2 | DEPENDENT subquery | T1 | Ref | A1 | The Using where; Using Index |
+----+--------------------+-------+------+------+--------------------------+
2 rows in Set (0.00 sec)
1.2.4 Contrast
We copy the original SQL statement to make it easy to read.
S6:select * from T3 WHERE b3 >= "any" (SELECT B1 from T1);
S7:select * from T3 WHERE b3 >= "Any" (SELECT A1 from T1);
S8:select * from T3 WHERE b3 <= SOME (SELECT A1 from T1);
S9:select * from t3 WHERE B3 = SOME (SELECT a1 from T1);
S10:select * from T3 WHERE b3 <= All (SELECT A1 from T1);
S11:select * from T3 where B3 <= all (SELECT A1 from T1 where a3=a1);
Then compare the following:
SQL statement
Statement features
Toprowdb
PostgreSQL
Mysql
S6
Non-correlated subqueries, >= any operations, B1 normal columns
Join optimization
Semi Join optimization + materialized (unrelated subqueries)
has not been optimized
S7
Non-correlated subquery, >= any operation, A1 index key
Join optimization
Semi Join optimization + index Scan
Specific optimizations
S8
Non-correlated subqueries, <= SOME operations, A1 index keys
Join optimization
Semi Join optimization + index Scan
S9
Non-correlated subqueries, = SOME Operations, A1 index keys
has not been optimized
Semi Join Optimization
Be optimized
S10
Non-correlated subquery, <= All operation, A1 index key
has not been optimized
has not been optimized
Specific optimizations
S11
Related subquery, <= all operation, A1 index key, Condition A3=A1 as index key
Semi Join Optimization
has not been optimized
Specific optimizations
Analysis:
Q from the overall view, for the Any/some/all type of subqueries, PostgreSQL, MySQL, toprowdb have their own characteristics, the MySQL surface does not pull the subquery appears weaker, but the internal enabled index on the subquery on the target column for aggregation operations (using the index to The maximum minimum, compared to S6 and S7, greatly accelerates the execution speed, which is the meaning of the expression "specific optimizations" in the table above
Q Contrast S8 and S9, can be seen, toprowdb to S9 did not provide optimization, and PostgreSQL, MySQL can optimize, this toprowdb need to work hard
Q contrast S11, toprowdb and MySQL are stronger than PostgreSQL
In the first part of the subquery optimization, we found that MySQL's in subquery optimization technology is higher than other databases, and this article is heavy, EXISTS type of subquery, MySQL is weaker than PostgreSQL, toprowdb, and Any/some/all type of subqueries are almost Ping, so we can think of the three sub-query optimization ability.
1.3 Is there another type of subquery?
The first few sections, see a variety of various subqueries, a wide range of varieties appear, the content and form of subqueries is very colorful. However, these are not yet covered by all types, such as not in, not EXISTS, various any (=any, >any, >=any, <any, <=any) and type any SOME, various all Wait a minute.