Hi serg, Yes, I had tried that. I had tried many combinations. I have updated the test however on github code: https://github.com/igniting/server/tree/selfTuningOptimizer. Regards Anshu On Wed, Jun 11, 2014 at 1:13 PM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Anshu!
On Jun 11, Anshu Avinash wrote:
Hi serg,
I have attached the updated diff. I'm still unable to observe the effect introduced by the cost factors. Maybe I just need to study the usage of scan_time() and read_time() in greater detail.
Ok, see one suggestion below. If that won't help - push your changes to github, I'll try myself.
diff --git a/mysql-test/r/costmodel.result b/mysql-test/r/costmodel.result new file mode 100644 index 0000000..6668758 --- /dev/null +++ b/mysql-test/r/costmodel.result @@ -0,0 +1,20 @@ +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (a int auto_increment primary key, b int); +INSERT INTO t1(b) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12), +(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25); +EXPLAIN +SELECT * FROM t1 +WHERE a > 21; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 5 Using index condition +use mysql; +UPDATE optimizer_cost_factors +SET const_value=2.0 +WHERE const_name='SCAN_TIME_FACTOR'; +use test; +EXPLAIN +SELECT * FROM t1 +WHERE a > 21; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 5 Using index condition +DROP TABLE t1;
See? Your first EXPLAIN already shows type=range. And then you increase the cost of the table scan. Of course, nothing can happen anymore.
Try to make the first explain to show type=ALL (which means table scan), then increase the cost of the table scan and see how optimizer will switch to type=range.
Regards, Sergei