MariaDB [tpch1m]> select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'AMERICA' and o_orderdate >= '1993-01-01' and o_orderdate < date_add( '1993-01-01' , interval 1 year) group by n_name order by revenue desc;
|
+-----------+-------------+
|
| n_name | revenue |
|
+-----------+-------------+
|
| PERU | 527161.1575 |
|
| ARGENTINA | 34521.3330 |
|
+-----------+-------------+
|
2 rows in set (0.290 sec)
|
|
MariaDB [tpch1m]> analyze table customer, orders, lineitem,supplier, nation, region
|
-> ;
|
+-----------------+---------+----------+----------+
|
| Table | Op | Msg_type | Msg_text |
|
+-----------------+---------+----------+----------+
|
| tpch1m.customer | analyze | status | OK |
|
| tpch1m.orders | analyze | status | OK |
|
| tpch1m.lineitem | analyze | status | OK |
|
| tpch1m.supplier | analyze | status | OK |
|
| tpch1m.nation | analyze | status | OK |
|
| tpch1m.region | analyze | status | OK |
|
+-----------------+---------+----------+----------+
|
6 rows in set (0.035 sec)
|
|
MariaDB [tpch1m]> select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'AMERICA' and o_orderdate >= '1993-01-01' and o_orderdate < date_add( '1993-01-01' , interval 1 year) group by n_name order by revenue desc;
|
+-----------+-------------+
|
| n_name | revenue |
|
+-----------+-------------+
|
| PERU | 527161.1575 |
|
| ARGENTINA | 34521.3330 |
|
+-----------+-------------+
|
2 rows in set (0.036 sec)
|
Task
Major
MCOL-4713 Optimizer statistics
Build verified: 23.02
engine: a1d89d8f311d8187d3357536a64d77ef6f9c2b8e
server: bf7f6987c8fb7ceda9ae048ada129d11798d4392
buildNo: 6151
Retested the test case in
MCOL-1205MariaDB [tpch1m]> select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'AMERICA' and o_orderdate >= '1993-01-01' and o_orderdate < date_add( '1993-01-01' , interval 1 year) group by n_name order by revenue desc;+-----------+-------------+| n_name | revenue |+-----------+-------------+| PERU | 527161.1575 || ARGENTINA | 34521.3330 |+-----------+-------------+2 rows in set (0.290 sec)MariaDB [tpch1m]> analyze table customer, orders, lineitem,supplier, nation, region-> ;+-----------------+---------+----------+----------+| Table | Op | Msg_type | Msg_text |+-----------------+---------+----------+----------+| tpch1m.customer | analyze | status | OK || tpch1m.orders | analyze | status | OK || tpch1m.lineitem | analyze | status | OK || tpch1m.supplier | analyze | status | OK || tpch1m.nation | analyze | status | OK || tpch1m.region | analyze | status | OK |+-----------------+---------+----------+----------+6 rows in set (0.035 sec)MariaDB [tpch1m]> select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'AMERICA' and o_orderdate >= '1993-01-01' and o_orderdate < date_add( '1993-01-01' , interval 1 year) group by n_name order by revenue desc;+-----------+-------------+| n_name | revenue |+-----------+-------------+| PERU | 527161.1575 || ARGENTINA | 34521.3330 |+-----------+-------------+2 rows in set (0.036 sec)