Description:
I observed a strange performance when running TPC-H.
For the two queries:
Query 10:
select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from CUSTOMER, ORDERS, LINEITEM, NATION where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= date '1993-08-01' and o_orderdate < date '1993-08-01' + interval '3' month and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc limit 20;
Query 11:
select ps_partkey, sum(ps_supplycost * ps_availqty) as value from PARTSUPP, SUPPLIER, NATION where ps_suppkey = s_suppkey and s_nationkey = n_nationkey and n_name = 'MOZAMBIQUE' group by ps_partkey having sum(ps_supplycost * ps_availqty) > ( select sum(ps_supplycost * ps_availqty) * 0.0001000000 from PARTSUPP, SUPPLIER, NATION where ps_suppkey = s_suppkey and s_nationkey = n_nationkey and n_name = 'MOZAMBIQUE' ) order by value desc;
If I directly run the query 11, the execution time is around 1s:
$ time mySQL -uroot -proot tpch < 11.sql
0.00s user 0.00s system 0% cpu 1.277 total
While, if I run query 10 five times, then the execution time of running query 11 is increased 2 times:
$ time mySQL -uroot -proot tpch < 10.sql
0.01s user 0.00s system 0% cpu 12.819 total
$ time mySQL -uroot -proot tpch < 10.sql
0.00s user 0.01s system 0% cpu 6.303 total
$ time mySQL -uroot -proot tpch < 10.sql
0.00s user 0.00s system 0% cpu 5.346 total
$ time mySQL -uroot -proot tpch < 10.sql
0.00s user 0.00s system 0% cpu 4.830 total
$ time mySQL -uroot -proot tpch < 10.sql
0.00s user 0.00s system 0% cpu 4.675 total
$ time mySQL -uroot -proot tpch < 11.sql
0.00s user 0.00s system 0% cpu 2.827 total
From my observation, repeatedly executing the same query should have a shorter execution time, but this one seems unexpected. Even if I continue to run the query 11 five more times, the execution time is still more than 2s. The query plan of executing query 11 has no difference across executions.
How to repeat:
The data is exactly the same as the one used in https://bugs.mysql.com/bug.php?id=116309
