When you use a function on a field, performance is affected.The trace.log displays the message "Unoptimized expression near 'substr'".
###Abnormal
mysql> select sum(ce) from all_bak where year(xxxx)=2020;
+------------------+
| sum(ce) |
+------------------+
| 1332849867555.84 |
+------------------+
1 row in set (4.67 sec)
###Normal
mysql> select sum(ce) from all_bak where xxxx between '2020-01-01' and '2020-12-31';
+------------------+
| sum(ce) |
+------------------+
| 1332849867555.84 |
+------------------+
1 row in set (0.00 sec)
###Abnormal
mysql> select substring(a.h_m,1,4) hrd,sum(ce) from all_bak a group by substring(a.h_m,1,4);
+------+-----------------+
| hrd | sum(ce) |
+------+-----------------+
| 7499 | 63816644743.20 |
| 7040 | 47191046107.84 |
| 8341 | 191566748484.48 |
| 7299 | 112803464512.32 |
| 8391 | 73260665137.28 |
| 6129 | 205524450576.96 |
| 7289 | 318319076069.28 |
| 7511 | 320367771924.48 |
+------+-----------------+
8 rows in set (22.44 sec)
###Normal
mysql> select h_m,sum(ce) from all_bak a group by h_m;
+-------+-----------------+
| h_m | sum(ce) |
+-------+-----------------+
| 7499 | 63816644743.20 |
| 7040 | 47191046107.84 |
| 8341 | 191566748484.48 |
| 7299 | 112803464512.32 |
| 8391 | 73260665137.28 |
| 6129 | 205524450576.96 |
| 7289 | 318319076069.28 |
| 7511 | 320367771924.48 |
+-------+-----------------+
8 rows in set (5.45 sec)
Expected behavior
No response
How To Reproduce
No response
Environment
./mysqld --version
./mysqld Ver 5.7.36-StoneDB for Linux on x86_64 (build-)
build information as follow:
Repository address: git@github.com:stoneatom/stonedb.git:stonedb-5.7
Branch name: stonedb-5.7
Last commit ID: 05eb397
Last commit time: Date: Mon Aug 15 14:05:47 2022 +0800
Build time: Date: 2022年 08月 15日 星期一 15:38:53 CST
Are you interested in submitting a PR to solve the problem?
Describe the problem
When you use a function on a field, performance is affected.The trace.log displays the message "Unoptimized expression near 'substr'".
Expected behavior
No response
How To Reproduce
No response
Environment
./mysqld --version
./mysqld Ver 5.7.36-StoneDB for Linux on x86_64 (build-) build information as follow: Repository address: git@github.com:stoneatom/stonedb.git:stonedb-5.7 Branch name: stonedb-5.7 Last commit ID: 05eb397 Last commit time: Date: Mon Aug 15 14:05:47 2022 +0800 Build time: Date: 2022年 08月 15日 星期一 15:38:53 CST
Are you interested in submitting a PR to solve the problem?