Closed greenlion closed 4 years ago
Currently, creating an explicit index on c1
will allow the optimizer to use the index without ECP.
Fixed in ecp_enhancement branch.
mysql> select count(*) from t2 where c2 = 'a' and upper(c2) = 'A';
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
mysql> explain analyze select count(*) from t2 where c2 = 'a' and upper(c2) = 'A';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(0) (actual time=1.014..1.014 rows=1 loops=1)
-> Filter: ((t2.c2 = 'a') and (upper(t2.c2) = 'A')) (cost=0.46 rows=1) (actual time=0.979..1.009 rows=3 loops=1)
-> Table scan on t2 (cost=0.46 rows=4) (actual time=0.969..0.996 rows=3 loops=1)
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
-- note disjunctions push to MySQL layer
mysql> explain analyze select count(*) from t2 where c2 = 'a' or upper(c2) = 'B';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(0) (actual time=0.867..0.868 rows=1 loops=1)
-> Filter: ((t2.c2 = 'a') or (upper(t2.c2) = 'B')) (cost=0.46 rows=4) (actual time=0.845..0.862 rows=4 loops=1)
-> Table scan on t2 (cost=0.46 rows=4) (actual time=0.839..0.849 rows=4 loops=1)
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
Conjuctions with functions should push down the constant evaluation portion, and push the remaining portion to the SQL layer for evaluation.