diff options
Diffstat (limited to 'storage')
| -rw-r--r-- | storage/connect/mysql-test/connect/r/mysql_index.result | 13 | ||||
| -rw-r--r-- | storage/connect/mysql-test/connect/t/mysql_index.test | 3 | ||||
| -rw-r--r-- | storage/federated/ha_federated.cc | 14 | ||||
| -rw-r--r-- | storage/federated/ha_federated.h | 18 | ||||
| -rw-r--r-- | storage/federatedx/ha_federatedx.cc | 14 | ||||
| -rw-r--r-- | storage/federatedx/ha_federatedx.h | 17 | ||||
| -rw-r--r-- | storage/heap/ha_heap.cc | 4 | ||||
| -rw-r--r-- | storage/heap/ha_heap.h | 13 | ||||
| -rw-r--r-- | storage/innobase/handler/ha_innodb.cc | 22 | ||||
| -rw-r--r-- | storage/innobase/handler/ha_innodb.h | 2 | ||||
| -rw-r--r-- | storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_not_equal.result | 2 | ||||
| -rw-r--r-- | storage/spider/mysql-test/spider/r/partition_mrr.result | 48 | ||||
| -rw-r--r-- | storage/spider/mysql-test/spider/t/partition_mrr.test | 1 |
13 files changed, 120 insertions, 51 deletions
diff --git a/storage/connect/mysql-test/connect/r/mysql_index.result b/storage/connect/mysql-test/connect/r/mysql_index.result index 54acc7be08d..b6c34add632 100644 --- a/storage/connect/mysql-test/connect/r/mysql_index.result +++ b/storage/connect/mysql-test/connect/r/mysql_index.result @@ -7,7 +7,7 @@ msg char(100) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO t1 VALUES(1,'Un'),(3,'Trois'),(5,'Cinq'); -INSERT INTO t1 VALUES(2,'Two'),(4,'Four'),(6,'Six'); +INSERT INTO t1 VALUES(2,'Two'),(4,'Four'),(6,'Six'), (7,'seven'); SELECT * FROM t1; id msg 1 Un @@ -16,6 +16,7 @@ id msg 2 Two 4 Four 6 Six +7 seven # # Make local MYSQL table with indexed id column # @@ -35,6 +36,7 @@ id msg 2 Two 4 Four 6 Six +7 seven SELECT * FROM t2 WHERE id = 3; id msg 3 Trois @@ -49,12 +51,14 @@ SELECT * FROM t2 WHERE id > 4; id msg 5 Cinq 6 Six +7 seven SELECT * FROM t2 WHERE id >= 3; id msg 3 Trois 4 Four 5 Cinq 6 Six +7 seven SELECT * FROM t2 WHERE id < 3; id msg 1 Un @@ -64,6 +68,10 @@ id msg 1 Un 5 Cinq 6 Six +7 seven +explain SELECT * FROM t2 WHERE id <= 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where SELECT * FROM t2 WHERE id <= 3; id msg 1 Un @@ -87,6 +95,7 @@ id msg 4 Four 5 Cinq 6 Six +7 seven UPDATE t2 SET msg = 'Five' WHERE id = 5; Warnings: Note 1105 t1: 1 affected rows @@ -98,6 +107,7 @@ id msg 2 Two 4 Four 6 Six +7 seven DELETE FROM t2 WHERE id = 4; Warnings: Note 1105 t1: 1 affected rows @@ -108,6 +118,7 @@ id msg 5 Five 2 Two 6 Six +7 seven DROP TABLE t2; DROP TABLE t1; # diff --git a/storage/connect/mysql-test/connect/t/mysql_index.test b/storage/connect/mysql-test/connect/t/mysql_index.test index cb4a332cdf8..a70ea3fd6f9 100644 --- a/storage/connect/mysql-test/connect/t/mysql_index.test +++ b/storage/connect/mysql-test/connect/t/mysql_index.test @@ -30,7 +30,7 @@ CREATE TABLE t1 ( PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO t1 VALUES(1,'Un'),(3,'Trois'),(5,'Cinq'); -INSERT INTO t1 VALUES(2,'Two'),(4,'Four'),(6,'Six'); +INSERT INTO t1 VALUES(2,'Two'),(4,'Four'),(6,'Six'), (7,'seven'); SELECT * FROM t1; --echo # @@ -54,6 +54,7 @@ SELECT * FROM t2 WHERE id > 4; SELECT * FROM t2 WHERE id >= 3; SELECT * FROM t2 WHERE id < 3; SELECT * FROM t2 WHERE id < 2 OR id > 4; +explain SELECT * FROM t2 WHERE id <= 3; SELECT * FROM t2 WHERE id <= 3; SELECT * FROM t2 WHERE id BETWEEN 3 AND 5; SELECT * FROM t2 WHERE id > 2 AND id < 6; diff --git a/storage/federated/ha_federated.cc b/storage/federated/ha_federated.cc index faaffa21f6b..76708e80105 100644 --- a/storage/federated/ha_federated.cc +++ b/storage/federated/ha_federated.cc @@ -905,10 +905,18 @@ ha_federated::ha_federated(handlerton *hton, :handler(hton, table_arg), mysql(0), stored_result(0) { + optimizer_cache_cost= 1; trx_next= 0; bzero(&bulk_insert, sizeof(bulk_insert)); } +/* + Federated doesn't need optimizer_cache_cost as everything is one a + remote server and nothing is cached locally +*/ + +void ha_federated::set_optimizer_cache_cost(double cost) +{} /* Convert MySQL result set row to handler internal format @@ -2879,11 +2887,11 @@ int ha_federated::info(uint flag) &error); /* - size of IO operations (This is based on a good guess, no high science - involved) + Size of IO operations. This is used to calculate time to scan a table. + See handler.cc::keyread_time */ if (flag & HA_STATUS_CONST) - stats.block_size= 4096; + stats.block_size= 1500; // Typical size of an TCP packet } diff --git a/storage/federated/ha_federated.h b/storage/federated/ha_federated.h index 0c6285f3ac6..39b78533124 100644 --- a/storage/federated/ha_federated.h +++ b/storage/federated/ha_federated.h @@ -180,22 +180,20 @@ public: The reason for "records * 1000" is that such a large number forces this to use indexes " */ - double scan_time() + virtual double scan_time() { DBUG_PRINT("info", ("records %lu", (ulong) stats.records)); return (double)(stats.records*1000); } - /* - The next method will never be called if you do not implement indexes. - */ - double read_time(uint index, uint ranges, ha_rows rows) + virtual double read_time(uint index, uint ranges, ha_rows rows) + { + return rows2double(rows) + rows2double(ranges); + } + virtual double read_with_rowid(ha_rows rows) { - /* - Per Brian, this number is bugus, but this method must be implemented, - and at a later date, he intends to document this issue for handler code - */ - return (double) rows / 20.0+1; + return rows2double(rows); } + virtual void set_optimizer_cache_cost(double cost); const key_map *keys_to_use_for_scanning() { return &key_map_full; } /* diff --git a/storage/federatedx/ha_federatedx.cc b/storage/federatedx/ha_federatedx.cc index 99513d70950..a6979718124 100644 --- a/storage/federatedx/ha_federatedx.cc +++ b/storage/federatedx/ha_federatedx.cc @@ -841,9 +841,17 @@ ha_federatedx::ha_federatedx(handlerton *hton, :handler(hton, table_arg), txn(0), io(0), stored_result(0) { + optimizer_cache_cost= 1; bzero(&bulk_insert, sizeof(bulk_insert)); } +/* + Federated doesn't need optimizer_cache_cost as everything is one a remote server and + nothing is cached locally +*/ + +void ha_federatedx::set_optimizer_cache_cost(double cost) +{} /* Convert MySQL result set row to handler internal format @@ -3106,11 +3114,11 @@ int ha_federatedx::info(uint flag) if (flag & (HA_STATUS_VARIABLE | HA_STATUS_CONST)) { /* - size of IO operations (This is based on a good guess, no high science - involved) + Size of IO operations. This is used to calculate time to scan a table. + See handler.cc::keyread_time */ if (flag & HA_STATUS_CONST) - stats.block_size= 4096; + stats.block_size= 1500; // Typical size of an TCP packet if ((*iop)->table_metadata(&stats, share->table_name, (uint)share->table_name_length, flag)) diff --git a/storage/federatedx/ha_federatedx.h b/storage/federatedx/ha_federatedx.h index 377af888d79..e1070111585 100644 --- a/storage/federatedx/ha_federatedx.h +++ b/storage/federatedx/ha_federatedx.h @@ -222,7 +222,6 @@ public: virtual int seek_position(FEDERATEDX_IO_RESULT **io_result, const void *ref)=0; virtual void set_thd(void *thd) { } - }; @@ -374,21 +373,19 @@ public: DBUG_PRINT("info", ("records %lu", (ulong) stats.records)); return (double)(stats.records*1000); } - /* - The next method will never be called if you do not implement indexes. - */ double read_time(uint index, uint ranges, ha_rows rows) { - /* - Per Brian, this number is bugus, but this method must be implemented, - and at a later date, he intends to document this issue for handler code - */ - return (double) rows / 20.0+1; + return rows2double(rows) + rows2double(ranges); + } + virtual double read_with_rowid(ha_rows rows) + { + return rows2double(rows); } + virtual void set_optimizer_cache_cost(double cost); const key_map *keys_to_use_for_scanning() { return &key_map_full; } /* - Everything below are methods that we implment in ha_federatedx.cc. + Everything below are methods that we implement in ha_federatedx.cc. Most of these methods are not obligatory, skip them and MySQL will treat them as not implemented diff --git a/storage/heap/ha_heap.cc b/storage/heap/ha_heap.cc index 5f7f0c1efa0..f9b365cf91e 100644 --- a/storage/heap/ha_heap.cc +++ b/storage/heap/ha_heap.cc @@ -73,7 +73,9 @@ static handler *heap_create_handler(handlerton *hton, ha_heap::ha_heap(handlerton *hton, TABLE_SHARE *table_arg) :handler(hton, table_arg), file(0), records_changed(0), key_stat_version(0), internal_table(0) -{} +{ + optimizer_cache_cost= 1.0; +} /* Hash index statistics is updated (copied from HP_KEYDEF::hash_buckets to diff --git a/storage/heap/ha_heap.h b/storage/heap/ha_heap.h index 3a41028c719..fec60ec2dec 100644 --- a/storage/heap/ha_heap.h +++ b/storage/heap/ha_heap.h @@ -68,8 +68,21 @@ public: { return (double) (rows +1)/ 20.0; } double keyread_time(uint index, uint ranges, ha_rows rows) { return (double) (rows + ranges) / 20.0 ; } + double read_with_rowid(ha_rows rows) + { + return (double) rows/ 20.0; + } double avg_io_cost() { return 0.05; } /* 1/20 */ + + /* + Heap doesn't need optimizer_cache_cost as everything is in memory and + it supports all needed _time() functions + */ + void set_optimizer_cache_cost(double cost) + { + optimizer_cache_cost= 1.0; + } int open(const char *name, int mode, uint test_if_locked); int close(void); void set_keys_for_scanning(void); diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 85a4ce90d90..6216d48e4e9 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -14371,6 +14371,28 @@ ha_innobase::read_time( return(ranges + (double) rows / (double) total_rows * time_for_scan); } +/******************************************************************//** +Calculate the time it takes to read a set of rows with primary key. +*/ + +double +ha_innobase::read_with_rowid(ha_rows rows) +{ + ha_rows total_rows; + + /* Assume that the read time is proportional to the scan time for all + rows + at most one seek per range. */ + + double time_for_scan = scan_time(); + + if ((total_rows = estimate_rows_upper_bound()) < rows) { + + return(time_for_scan); + } + + return((double) rows + (double) rows / (double) total_rows * time_for_scan); +} + /*********************************************************************//** Calculates the key number used inside MySQL for an Innobase index. @return the key number used inside MySQL */ diff --git a/storage/innobase/handler/ha_innodb.h b/storage/innobase/handler/ha_innodb.h index 4185f7a68cd..cd1ee7dd0c7 100644 --- a/storage/innobase/handler/ha_innodb.h +++ b/storage/innobase/handler/ha_innodb.h @@ -109,6 +109,8 @@ public: double read_time(uint index, uint ranges, ha_rows rows) override; + double read_with_rowid(ha_rows rows) override; + int delete_all_rows() override; int write_row(const uchar * buf) override; diff --git a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_not_equal.result b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_not_equal.result index a1a123e7d5f..199edf9d758 100644 --- a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_not_equal.result +++ b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_not_equal.result @@ -14,5 +14,5 @@ COUNT(*) 2 SHOW STATUS LIKE 'mroonga_count_skip'; Variable_name Value -Mroonga_count_skip 2 +Mroonga_count_skip 0 DROP TABLE users; diff --git a/storage/spider/mysql-test/spider/r/partition_mrr.result b/storage/spider/mysql-test/spider/r/partition_mrr.result index c7e17698421..215839e1b2d 100644 --- a/storage/spider/mysql-test/spider/r/partition_mrr.result +++ b/storage/spider/mysql-test/spider/r/partition_mrr.result @@ -74,41 +74,43 @@ TRUNCATE TABLE mysql.general_log; connection master_1; SELECT a.pkey FROM tbl_a a, tbl_b b WHERE a.pkey = b.pkey; pkey -4 -5 +0 +1 10 11 +12 +13 +14 +15 16 17 +18 +19 +2 +20 +21 22 23 +24 +25 +26 +27 28 29 -0 -1 +3 +4 +5 6 7 -12 -13 -18 -19 -24 -25 -2 -3 8 9 -14 -15 -20 -21 -26 -27 connection child2_1; SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %'; argument select `pkey` from `auto_test_remote`.`tbl_a` order by `pkey` -select a.id,b.`pkey` from auto_test_remote.tmp_spider_bka_xxxx a,`auto_test_remote`.`tbl_b` b where a.c0 <=> b.`pkey` +select `pkey` from `auto_test_remote`.`tbl_b` order by `pkey` +select `pkey` from `auto_test_remote`.`tbl_b` order by `pkey` +select `pkey` from `auto_test_remote`.`tbl_b` order by `pkey` SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %' SELECT pkey FROM tbl_a ORDER BY pkey ; SELECT pkey FROM tbl_b ORDER BY pkey; @@ -138,7 +140,9 @@ connection child2_2; SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %'; argument select `pkey` from `auto_test_remote2`.`tbl_a` order by `pkey` -select a.id,b.`pkey` from auto_test_remote2.tmp_spider_bka_xxxx a,`auto_test_remote2`.`tbl_b` b where a.c0 <=> b.`pkey` +select `pkey` from `auto_test_remote2`.`tbl_b` order by `pkey` +select `pkey` from `auto_test_remote2`.`tbl_b` order by `pkey` +select `pkey` from `auto_test_remote2`.`tbl_b` order by `pkey` SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %' SELECT pkey FROM tbl_a ORDER BY pkey ; SELECT pkey FROM tbl_b ORDER BY pkey; @@ -168,7 +172,9 @@ connection child2_3; SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %'; argument select `pkey` from `auto_test_remote3`.`tbl_a` order by `pkey` -select a.id,b.`pkey` from auto_test_remote3.tmp_spider_bka_xxxx a,`auto_test_remote3`.`tbl_b` b where a.c0 <=> b.`pkey` +select `pkey` from `auto_test_remote3`.`tbl_b` order by `pkey` +select `pkey` from `auto_test_remote3`.`tbl_b` order by `pkey` +select `pkey` from `auto_test_remote3`.`tbl_b` order by `pkey` SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %' SELECT pkey FROM tbl_a ORDER BY pkey ; SELECT pkey FROM tbl_b ORDER BY pkey; diff --git a/storage/spider/mysql-test/spider/t/partition_mrr.test b/storage/spider/mysql-test/spider/t/partition_mrr.test index 2816d65cadb..36cf811fa79 100644 --- a/storage/spider/mysql-test/spider/t/partition_mrr.test +++ b/storage/spider/mysql-test/spider/t/partition_mrr.test @@ -168,6 +168,7 @@ if ($USE_CHILD_GROUP2) } } --connection master_1 +--sorted_result SELECT a.pkey FROM tbl_a a, tbl_b b WHERE a.pkey = b.pkey; if ($USE_CHILD_GROUP2) { |