summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--heap/_check.c23
-rw-r--r--heap/hp_clear.c1
-rw-r--r--heap/hp_create.c1
-rw-r--r--heap/hp_delete.c8
-rw-r--r--heap/hp_hash.c20
-rw-r--r--heap/hp_write.c25
-rw-r--r--include/heap.h7
-rw-r--r--mysql-test/r/heap.result8
-rw-r--r--mysql-test/r/heap_hash.result144
-rw-r--r--mysql-test/r/myisam.result2
-rw-r--r--mysql-test/t/heap_hash.test104
-rw-r--r--sql/ha_heap.cc56
-rw-r--r--sql/ha_heap.h10
-rw-r--r--sql/structs.h7
14 files changed, 386 insertions, 30 deletions
diff --git a/heap/_check.c b/heap/_check.c
index 233cb8cb0c5..a745aee48bf 100644
--- a/heap/_check.c
+++ b/heap/_check.c
@@ -102,9 +102,11 @@ static int check_one_key(HP_KEYDEF *keydef, uint keynr, ulong records,
int error;
uint i,found,max_links,seek,links;
uint rec_link; /* Only used with debugging */
+ uint hash_buckets_found;
HASH_INFO *hash_info;
error=0;
+ hash_buckets_found= 0;
for (i=found=max_links=seek=0 ; i < records ; i++)
{
hash_info=hp_find_hash(&keydef->block,i);
@@ -128,21 +130,32 @@ static int check_one_key(HP_KEYDEF *keydef, uint keynr, ulong records,
found++;
}
if (links > max_links) max_links=links;
+ hash_buckets_found++;
}
}
if (found != records)
{
- DBUG_PRINT("error",("Found %ld of %ld records"));
+ DBUG_PRINT("error",("Found %ld of %ld records", found, records));
+ error=1;
+ }
+ if (keydef->hash_buckets != hash_buckets_found)
+ {
+ DBUG_PRINT("error",("Found %ld buckets, stats shows %ld buckets",
+ hash_buckets_found, keydef->hash_buckets));
error=1;
}
DBUG_PRINT("info",
- ("records: %ld seeks: %d max links: %d hitrate: %.2f",
+ ("records: %ld seeks: %d max links: %d hitrate: %.2f "
+ "buckets: %d",
records,seek,max_links,
- (float) seek / (float) (records ? records : 1)));
+ (float) seek / (float) (records ? records : 1),
+ hash_buckets_found));
if (print_status)
- printf("Key: %d records: %ld seeks: %d max links: %d hitrate: %.2f\n",
+ printf("Key: %d records: %ld seeks: %d max links: %d "
+ "hitrate: %.2f buckets: %d\n",
keynr, records, seek, max_links,
- (float) seek / (float) (records ? records : 1));
+ (float) seek / (float) (records ? records : 1),
+ hash_buckets_found);
return error;
}
diff --git a/heap/hp_clear.c b/heap/hp_clear.c
index 4440344f990..596d71ebe9c 100644
--- a/heap/hp_clear.c
+++ b/heap/hp_clear.c
@@ -97,6 +97,7 @@ void hp_clear_keys(HP_SHARE *info)
VOID(hp_free_level(block,block->levels,block->root,(byte*) 0));
block->levels=0;
block->last_allocated=0;
+ keyinfo->hash_buckets= 0;
}
}
info->index_length=0;
diff --git a/heap/hp_create.c b/heap/hp_create.c
index 02725576c8f..d1783118c0d 100644
--- a/heap/hp_create.c
+++ b/heap/hp_create.c
@@ -128,6 +128,7 @@ int heap_create(const char *name, uint keys, HP_KEYDEF *keydef,
max_records);
keyinfo->delete_key= hp_delete_key;
keyinfo->write_key= hp_write_key;
+ keyinfo->hash_buckets= 0;
}
}
share->min_records= min_records;
diff --git a/heap/hp_delete.c b/heap/hp_delete.c
index c918cf37f05..a89fe49f495 100644
--- a/heap/hp_delete.c
+++ b/heap/hp_delete.c
@@ -151,6 +151,8 @@ int hp_delete_key(HP_INFO *info, register HP_KEYDEF *keyinfo,
pos->ptr_to_rec=empty->ptr_to_rec;
pos->next_key=empty->next_key;
}
+ else
+ keyinfo->hash_buckets--;
if (empty == lastpos) /* deleted last hash key */
DBUG_RETURN (0);
@@ -187,7 +189,11 @@ int hp_delete_key(HP_INFO *info, register HP_KEYDEF *keyinfo,
}
pos3= pos; /* Link pos->next after lastpos */
}
- else pos3= 0; /* Different positions merge */
+ else
+ {
+ pos3= 0; /* Different positions merge */
+ keyinfo->hash_buckets--;
+ }
empty[0]=lastpos[0];
hp_movelink(pos3, empty, pos->next_key);
diff --git a/heap/hp_hash.c b/heap/hp_hash.c
index 71eecc8bdf2..2108765cc7f 100644
--- a/heap/hp_hash.c
+++ b/heap/hp_hash.c
@@ -196,7 +196,18 @@ byte *hp_search_next(HP_INFO *info, HP_KEYDEF *keyinfo, const byte *key,
}
- /* Calculate pos according to keys */
+/*
+ Calculate position number for hash value.
+ SYNOPSIS
+ hp_mask()
+ hashnr Hash value
+ buffmax Value such that
+ 2^(n-1) < maxlength <= 2^n = buffmax
+ maxlength
+
+ RETURN
+ Array index, in [0..maxlength)
+*/
ulong hp_mask(ulong hashnr, ulong buffmax, ulong maxlength)
{
@@ -205,7 +216,12 @@ ulong hp_mask(ulong hashnr, ulong buffmax, ulong maxlength)
}
- /* Change link from pos to new_link */
+/*
+ Change
+ next_link -> ... -> X -> pos
+ to
+ next_link -> ... -> X -> newlink
+*/
void hp_movelink(HASH_INFO *pos, HASH_INFO *next_link, HASH_INFO *newlink)
{
diff --git a/heap/hp_write.c b/heap/hp_write.c
index 3b0ec76d616..853360976bf 100644
--- a/heap/hp_write.c
+++ b/heap/hp_write.c
@@ -36,7 +36,7 @@ int heap_write(HP_INFO *info, const byte *record)
byte *pos;
HP_SHARE *share=info->s;
DBUG_ENTER("heap_write");
-
+ printf("heap_write\n");
#ifndef DBUG_OFF
if (info->mode & O_RDONLY)
{
@@ -180,15 +180,31 @@ int hp_write_key(HP_INFO *info, HP_KEYDEF *keyinfo,
DBUG_RETURN(-1); /* No more memory */
halfbuff= (long) share->blength >> 1;
pos= hp_find_hash(&keyinfo->block,(first_index=share->records-halfbuff));
-
+
+ /*
+ We're about to add one more hash position, with hash_mask=#records.
+ Entries that should be relocated to that position are currently members
+ of the list that starts at #first_index position.
+ At #first_index position there may be either:
+ a) A list of items with hash_mask=first_index. The list contains
+ 1) entries that should be relocated to the list that starts at new
+ position we're adding
+ 2) entries that should be left in the list starting at #first_index
+ position
+ or
+ b) An entry with hashnr != first_index. We don't need to move it.
+ */
if (pos != empty) /* If some records */
{
do
{
hashnr = hp_rec_hashnr(keyinfo, pos->ptr_to_rec);
if (flag == 0) /* First loop; Check if ok */
+ {
+ /* Bail out if we're dealing with case b) from above comment */
if (hp_mask(hashnr, share->blength, share->records) != first_index)
break;
+ }
if (!(hashnr & halfbuff))
{ /* Key will not move */
if (!(flag & LOWFIND))
@@ -245,6 +261,9 @@ int hp_write_key(HP_INFO *info, HP_KEYDEF *keyinfo,
}
}
while ((pos=pos->next_key));
+
+ if ((flag & (LOWFIND | HIGHFIND)) == (LOWFIND | HIGHFIND))
+ keyinfo->hash_buckets++;
if ((flag & (LOWFIND | LOWUSED)) == LOWFIND)
{
@@ -265,6 +284,7 @@ int hp_write_key(HP_INFO *info, HP_KEYDEF *keyinfo,
{
pos->ptr_to_rec=recpos;
pos->next_key=0;
+ keyinfo->hash_buckets++;
}
else
{
@@ -280,6 +300,7 @@ int hp_write_key(HP_INFO *info, HP_KEYDEF *keyinfo,
}
else
{
+ keyinfo->hash_buckets++;
pos->ptr_to_rec=recpos;
pos->next_key=0;
hp_movelink(pos, gpos, empty);
diff --git a/include/heap.h b/include/heap.h
index 63f2abbabc7..e3e7f228421 100644
--- a/include/heap.h
+++ b/include/heap.h
@@ -87,6 +87,11 @@ typedef struct st_hp_keydef /* Key definition with open */
uint8 algorithm; /* HASH / BTREE */
HA_KEYSEG *seg;
HP_BLOCK block; /* Where keys are saved */
+ /*
+ Number of buckets used in hash table. Used only to provide
+ #records estimates for heap key scans.
+ */
+ ha_rows hash_buckets;
TREE rb_tree;
int (*write_key)(struct st_heap_info *info, struct st_hp_keydef *keyinfo,
const byte *record, byte *recpos);
@@ -102,7 +107,7 @@ typedef struct st_heap_share
ulong min_records,max_records; /* Params to open */
ulong data_length,index_length;
uint records; /* records */
- uint blength;
+ uint blength; /* records rounded up to 2^n */
uint deleted; /* Deleted records in database */
uint reclength; /* Length of one record */
uint changed;
diff --git a/mysql-test/r/heap.result b/mysql-test/r/heap.result
index c49c9abb368..f4ee11fe1a3 100644
--- a/mysql-test/r/heap.result
+++ b/mysql-test/r/heap.result
@@ -4,7 +4,7 @@ insert into t1 values(1,1),(2,2),(3,3),(4,4);
delete from t1 where a=1 or a=0;
show keys from t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
-t1 0 PRIMARY 1 a NULL NULL NULL NULL HASH
+t1 0 PRIMARY 1 a NULL 3 NULL NULL HASH
select * from t1;
a b
2 2
@@ -169,7 +169,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL btn NULL NULL NULL 11 Using where
explain select * from t1 where btn="a" and new_col="a";
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref btn btn 11 const,const 10 Using where
+1 SIMPLE t1 ref btn btn 11 const,const 2 Using where
drop table t1;
CREATE TABLE t1 (
a int default NULL,
@@ -182,7 +182,7 @@ SELECT * FROM t1 WHERE a=NULL;
a b
explain SELECT * FROM t1 WHERE a IS NULL;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a a 5 const 10 Using where
+1 SIMPLE t1 ref a a 5 const 1 Using where
SELECT * FROM t1 WHERE a<=>NULL;
a b
NULL 99
@@ -204,7 +204,7 @@ key a (a)
INSERT INTO t1 VALUES (10), (10), (10);
EXPLAIN SELECT * FROM t1 WHERE a=10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a a 5 const 10 Using where
+1 SIMPLE t1 ref a a 5 const 3 Using where
SELECT * FROM t1 WHERE a=10;
a
10
diff --git a/mysql-test/r/heap_hash.result b/mysql-test/r/heap_hash.result
index 7affbf788fb..29287b23745 100644
--- a/mysql-test/r/heap_hash.result
+++ b/mysql-test/r/heap_hash.result
@@ -1,10 +1,10 @@
-drop table if exists t1;
+drop table if exists t1,t2;
create table t1 (a int not null,b int not null, primary key using HASH (a)) engine=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100;
insert into t1 values(1,1),(2,2),(3,3),(4,4);
delete from t1 where a=1 or a=0;
show keys from t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
-t1 0 PRIMARY 1 a NULL NULL NULL NULL HASH
+t1 0 PRIMARY 1 a NULL 3 NULL NULL HASH
select * from t1;
a b
2 2
@@ -169,7 +169,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL btn NULL NULL NULL 11 Using where
explain select * from t1 where btn="a" and new_col="a";
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref btn btn 11 const,const 10 Using where
+1 SIMPLE t1 ref btn btn 11 const,const 2 Using where
drop table t1;
CREATE TABLE t1 (
a int default NULL,
@@ -182,7 +182,7 @@ SELECT * FROM t1 WHERE a=NULL;
a b
explain SELECT * FROM t1 WHERE a IS NULL;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a a 5 const 10 Using where
+1 SIMPLE t1 ref a a 5 const 1 Using where
SELECT * FROM t1 WHERE a<=>NULL;
a b
NULL 99
@@ -203,3 +203,139 @@ DELETE from t1 where a < 100;
SELECT * from t1;
a
DROP TABLE t1;
+create table t1
+(
+a char(8) not null,
+b char(20) not null,
+c int not null,
+key (a)
+) engine=heap;
+insert into t1 values ('aaaa', 'prefill-hash=5',0);
+insert into t1 values ('aaab', 'prefill-hash=0',0);
+insert into t1 values ('aaac', 'prefill-hash=7',0);
+insert into t1 values ('aaad', 'prefill-hash=2',0);
+insert into t1 values ('aaae', 'prefill-hash=1',0);
+insert into t1 values ('aaaf', 'prefill-hash=4',0);
+insert into t1 values ('aaag', 'prefill-hash=3',0);
+insert into t1 values ('aaah', 'prefill-hash=6',0);
+explain select * from t1 where a='aaaa';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a a 8 const 1 Using where
+explain select * from t1 where a='aaab';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a a 8 const 1 Using where
+explain select * from t1 where a='aaac';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a a 8 const 1 Using where
+explain select * from t1 where a='aaad';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a a 8 const 1 Using where
+insert into t1 select * from t1;
+explain select * from t1 where a='aaaa';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a a 8 const 2 Using where
+explain select * from t1 where a='aaab';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a a 8 const 2 Using where
+explain select * from t1 where a='aaac';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a a 8 const 2 Using where
+explain select * from t1 where a='aaad';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a a 8 const 2 Using where
+create table t2 as select * from t1;
+delete from t1;
+insert into t1 select * from t2;
+explain select * from t1 where a='aaaa';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a a 8 const 2 Using where
+explain select * from t1 where a='aaab';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a a 8 const 2 Using where
+explain select * from t1 where a='aaac';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a a 8 const 2 Using where
+explain select * from t1 where a='aaad';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a a 8 const 2 Using where
+drop table t1, t2;
+create table t1 (
+id int unsigned not null primary key auto_increment,
+name varchar(20) not null,
+index heap_idx(name),
+index btree_idx using btree(name)
+) engine=heap;
+create table t2 (
+id int unsigned not null primary key auto_increment,
+name varchar(20) not null,
+index btree_idx using btree(name),
+index heap_idx(name)
+) engine=heap;
+insert into t1 (name) values ('Matt'), ('Lilu'), ('Corbin'), ('Carly'),
+('Suzy'), ('Hoppy'), ('Burrito'), ('Mimi'), ('Sherry'), ('Ben'), ('Phil'),
+('Emily'), ('Mike');
+insert into t2 select * from t1;
+explain select * from t1 where name='matt';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref heap_idx,btree_idx heap_idx 20 const 1 Using where
+explain select * from t2 where name='matt';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref btree_idx,heap_idx btree_idx 20 const 1 Using where
+explain select * from t1 where name='Lilu';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref heap_idx,btree_idx heap_idx 20 const 1 Using where
+explain select * from t2 where name='Lilu';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref btree_idx,heap_idx btree_idx 20 const 1 Using where
+explain select * from t1 where name='Phil';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref heap_idx,btree_idx heap_idx 20 const 1 Using where
+explain select * from t2 where name='Phil';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref btree_idx,heap_idx btree_idx 20 const 1 Using where
+explain select * from t1 where name='Lilu';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref heap_idx,btree_idx heap_idx 20 const 1 Using where
+explain select * from t2 where name='Lilu';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref btree_idx,heap_idx btree_idx 20 const 1 Using where
+insert into t1 (name) select name from t2;
+insert into t1 (name) select name from t2;
+insert into t1 (name) select name from t2;
+insert into t1 (name) select name from t2;
+insert into t1 (name) select name from t2;
+insert into t1 (name) select name from t2;
+select count(*) from t1 where name='Matt';
+count(*)
+7
+explain select * from t1 ignore index (btree_idx) where name='matt';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref heap_idx heap_idx 20 const 7 Using where
+show index from t1;
+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
+t1 0 PRIMARY 1 id NULL 91 NULL NULL HASH
+t1 1 heap_idx 1 name NULL 15 NULL NULL HASH
+t1 1 btree_idx 1 name A NULL NULL NULL BTREE
+flush tables;
+show index from t1;
+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
+t1 0 PRIMARY 1 id NULL 91 NULL NULL HASH
+t1 1 heap_idx 1 name NULL 13 NULL NULL HASH
+t1 1 btree_idx 1 name A NULL NULL NULL BTREE
+create table t3
+(
+a varchar(20) not null,
+b varchar(20) not null,
+key (a,b)
+) engine=heap;
+insert into t3 select name, name from t1;
+show index from t3;
+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
+t3 1 a 1 a NULL NULL NULL NULL HASH
+t3 1 a 2 b NULL 15 NULL NULL HASH
+flush tables;
+show index from t3;
+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
+t3 1 a 1 a NULL NULL NULL NULL HASH
+t3 1 a 2 b NULL 13 NULL NULL HASH
+drop table t1,t2;
diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result
index 26dcce43d08..31b14f9b822 100644
--- a/mysql-test/r/myisam.result
+++ b/mysql-test/r/myisam.result
@@ -543,7 +543,7 @@ Warnings:
Note 1031 Table storage engine for 't1' doesn't have this option
show keys from t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
-t1 1 a 1 a NULL NULL NULL NULL YES HASH
+t1 1 a 1 a NULL 1000 NULL NULL YES HASH
drop table t1,t2;
create table t1 ( a tinytext, b char(1), index idx (a(1),b) );
insert into t1 values (null,''), (null,'');
diff --git a/mysql-test/t/heap_hash.test b/mysql-test/t/heap_hash.test
index 412b6f2e705..df233633840 100644
--- a/mysql-test/t/heap_hash.test
+++ b/mysql-test/t/heap_hash.test
@@ -3,7 +3,7 @@
#
--disable_warnings
-drop table if exists t1;
+drop table if exists t1,t2;
--enable_warnings
create table t1 (a int not null,b int not null, primary key using HASH (a)) engine=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100;
@@ -141,3 +141,105 @@ INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
DELETE from t1 where a < 100;
SELECT * from t1;
DROP TABLE t1;
+
+
+#
+# Hash index # records estimate test
+#
+create table t1
+(
+ a char(8) not null,
+ b char(20) not null,
+ c int not null,
+ key (a)
+) engine=heap;
+
+insert into t1 values ('aaaa', 'prefill-hash=5',0);
+insert into t1 values ('aaab', 'prefill-hash=0',0);
+insert into t1 values ('aaac', 'prefill-hash=7',0);
+insert into t1 values ('aaad', 'prefill-hash=2',0);
+insert into t1 values ('aaae', 'prefill-hash=1',0);
+insert into t1 values ('aaaf', 'prefill-hash=4',0);
+insert into t1 values ('aaag', 'prefill-hash=3',0);
+insert into t1 values ('aaah', 'prefill-hash=6',0);
+
+explain select * from t1 where a='aaaa';
+explain select * from t1 where a='aaab';
+explain select * from t1 where a='aaac';
+explain select * from t1 where a='aaad';
+insert into t1 select * from t1;
+explain select * from t1 where a='aaaa';
+explain select * from t1 where a='aaab';
+explain select * from t1 where a='aaac';
+explain select * from t1 where a='aaad';
+
+# Check if delete_all_rows() updates #hash_buckets
+create table t2 as select * from t1;
+delete from t1;
+insert into t1 select * from t2;
+explain select * from t1 where a='aaaa';
+explain select * from t1 where a='aaab';
+explain select * from t1 where a='aaac';
+explain select * from t1 where a='aaad';
+drop table t1, t2;
+
+
+# Btree and hash index use costs.
+create table t1 (
+ id int unsigned not null primary key auto_increment,
+ name varchar(20) not null,
+ index heap_idx(name),
+ index btree_idx using btree(name)
+) engine=heap;
+
+create table t2 (
+ id int unsigned not null primary key auto_increment,
+ name varchar(20) not null,
+ index btree_idx using btree(name),
+ index heap_idx(name)
+) engine=heap;
+
+insert into t1 (name) values ('Matt'), ('Lilu'), ('Corbin'), ('Carly'),
+ ('Suzy'), ('Hoppy'), ('Burrito'), ('Mimi'), ('Sherry'), ('Ben'), ('Phil'),
+ ('Emily'), ('Mike');
+insert into t2 select * from t1;
+
+explain select * from t1 where name='matt';
+explain select * from t2 where name='matt';
+
+explain select * from t1 where name='Lilu';
+explain select * from t2 where name='Lilu';
+
+explain select * from t1 where name='Phil';
+explain select * from t2 where name='Phil';
+
+explain select * from t1 where name='Lilu';
+explain select * from t2 where name='Lilu';
+
+insert into t1 (name) select name from t2;
+insert into t1 (name) select name from t2;
+insert into t1 (name) select name from t2;
+insert into t1 (name) select name from t2;
+insert into t1 (name) select name from t2;
+insert into t1 (name) select name from t2;
+
+select count(*) from t1 where name='Matt';
+explain select * from t1 ignore index (btree_idx) where name='matt';
+show index from t1;
+
+flush tables;
+show index from t1;
+
+create table t3
+(
+ a varchar(20) not null,
+ b varchar(20) not null,
+ key (a,b)
+) engine=heap;
+insert into t3 select name, name from t1;
+show index from t3;
+flush tables;
+show index from t3;
+
+drop table t1,t2;
+
diff --git a/sql/ha_heap.cc b/sql/ha_heap.cc
index d7327362286..7643037e24f 100644
--- a/sql/ha_heap.cc
+++ b/sql/ha_heap.cc
@@ -30,6 +30,12 @@
const char **ha_heap::bas_ext() const
{ static const char *ext[1]= { NullS }; return ext; }
+/*
+ Hash index statistics is updated (copied from HP_KEYDEF::hash_buckets to
+ rec_per_key) after 1/HEAP_STATS_UPDATE_THRESHOLD records have been inserted/
+ updated/deleted. delete_all_rows() and table flush cause immediate update.
+*/
+#define HEAP_STATS_UPDATE_THRESHOLD 10
int ha_heap::open(const char *name, int mode, uint test_if_locked)
{
@@ -48,6 +54,8 @@ int ha_heap::open(const char *name, int mode, uint test_if_locked)
{
/* Initialize variables for the opened table */
set_keys_for_scanning();
+ if (table->tmp_table == NO_TMP_TABLE)
+ update_key_stats();
}
return (file ? 0 : 1);
}
@@ -84,28 +92,59 @@ void ha_heap::set_keys_for_scanning(void)
}
}
+void ha_heap::update_key_stats()
+{
+ printf("update_key_stats\n");
+ for (uint i= 0; i < table->keys; i++)
+ {
+ KEY *key=table->key_info+i;
+ if (key->algorithm != HA_KEY_ALG_BTREE)
+ {
+ ha_rows hash_buckets= file->s->keydef[i].hash_buckets;
+ key->rec_per_key[key->key_parts-1]=
+ hash_buckets ? file->s->records/hash_buckets : 0;
+ }
+ }
+ records_changed= 0;
+}
+
int ha_heap::write_row(byte * buf)
{
+ int res;
statistic_increment(ha_write_count,&LOCK_status);
if (table->timestamp_default_now)
update_timestamp(buf+table->timestamp_default_now-1);
if (table->next_number_field && buf == table->record[0])
update_auto_increment();
- return heap_write(file,buf);
+ res= heap_write(file,buf);
+ if (!res && table->tmp_table == NO_TMP_TABLE &&
+ ++records_changed*HEAP_STATS_UPDATE_THRESHOLD > file->s->records)
+ update_key_stats();
+ return res;
}
int ha_heap::update_row(const byte * old_data, byte * new_data)
{
+ int res;
statistic_increment(ha_update_count,&LOCK_status);
if (table->timestamp_on_update_now)
update_timestamp(new_data+table->timestamp_on_update_now-1);
- return heap_update(file,old_data,new_data);
+ res= heap_update(file,old_data,new_data);
+ if (!res && table->tmp_table == NO_TMP_TABLE &&
+ ++records_changed*HEAP_STATS_UPDATE_THRESHOLD > file->s->records)
+ update_key_stats();
+ return res;
}
int ha_heap::delete_row(const byte * buf)
{
+ int res;
statistic_increment(ha_delete_count,&LOCK_status);
- return heap_delete(file,buf);
+ res= heap_delete(file,buf);
+ if (!res && table->tmp_table == NO_TMP_TABLE &&
+ ++records_changed*HEAP_STATS_UPDATE_THRESHOLD > file->s->records)
+ update_key_stats();
+ return res;
}
int ha_heap::index_read(byte * buf, const byte * key, uint key_len,
@@ -227,6 +266,8 @@ int ha_heap::extra(enum ha_extra_function operation)
int ha_heap::delete_all_rows()
{
heap_clear(file);
+ if (table->tmp_table == NO_TMP_TABLE)
+ update_key_stats();
return 0;
}
@@ -383,7 +424,14 @@ ha_rows ha_heap::records_in_range(uint inx, key_range *min_key,
min_key->flag != HA_READ_KEY_EXACT ||
max_key->flag != HA_READ_AFTER_KEY)
return HA_POS_ERROR; // Can only use exact keys
- return 10; // Good guess
+ else
+ {
+ ha_rows records= file->s->records;
+ if (!records)
+ return 0;
+ ha_rows res= records / file->s->keydef[inx].hash_buckets;
+ return res ? res : 1;
+ }
}
diff --git a/sql/ha_heap.h b/sql/ha_heap.h
index 9ca6b9b76b6..f36e9f31c55 100644
--- a/sql/ha_heap.h
+++ b/sql/ha_heap.h
@@ -27,9 +27,10 @@ class ha_heap: public handler
{
HP_INFO *file;
key_map btree_keys;
-
- public:
- ha_heap(TABLE *table): handler(table), file(0) {}
+ /* number of records changed since last statistics update */
+ uint records_changed;
+public:
+ ha_heap(TABLE *table): handler(table), file(0), records_changed(0) {}
~ha_heap() {}
const char *table_type() const { return "HEAP"; }
const char *index_type(uint inx)
@@ -91,5 +92,6 @@ class ha_heap: public handler
THR_LOCK_DATA **store_lock(THD *thd, THR_LOCK_DATA **to,
enum thr_lock_type lock_type);
-
+private:
+ void update_key_stats();
};
diff --git a/sql/structs.h b/sql/structs.h
index c30d85f59cb..846b3400fab 100644
--- a/sql/structs.h
+++ b/sql/structs.h
@@ -89,7 +89,12 @@ typedef struct st_key {
enum ha_key_alg algorithm;
KEY_PART_INFO *key_part;
char *name; /* Name of key */
- ulong *rec_per_key; /* Key part distribution */
+ /*
+ Array of AVG(#records with the same field value) for 1st ... Nth key part.
+ 0 means 'not known'.
+ For temporary heap tables this member is NULL.
+ */
+ ulong *rec_per_key;
union {
int bdb_return_if_eq;
} handler;
View Lorry Controller Status