JJUGBMNa

1drop table if exists linear_hash_algo_test;
2create table linear_hash_algo_test (i int not null auto_increment primary key , p int);
3insert into linear_hash_algo_test values ();
4insert into linear_hash_algo_test (i) select null from linear_hash_algo_test; insert into linear_hash_algo_test (i) select null from linear_hash_algo_test; insert into linear_hash_algo_test (i) select null from linear_hash_algo_test; insert into linear_hash_algo_test (i) select null from linear_hash_algo_test; insert into linear_hash_algo_test (i) select null from linear_hash_algo_test; insert into linear_hash_algo_test (i) select null from linear_hash_algo_test; insert into linear_hash_algo_test (i) select null from linear_hash_algo_test; insert into linear_hash_algo_test (i) select null from linear_hash_algo_test; insert into linear_hash_algo_test (i) select null from linear_hash_algo_test; insert into linear_hash_algo_test (i) select null from linear_hash_algo_test;
5insert into linear_hash_algo_test (i) select null from linear_hash_algo_test; insert into linear_hash_algo_test (i) select null from linear_hash_algo_test; insert into linear_hash_algo_test (i) select null from linear_hash_algo_test; insert into linear_hash_algo_test (i) select null from linear_hash_algo_test;
6
7delimiter //
8drop function if exists linear_hash//
9create function linear_hash ( k int, num int ) returns int begin
10  declare v int;
11  declare n int;
12  select POWER(2, CEILING(LOG(2, num))) into v;
13  select k & (v - 1) into n;
14#  insert into debug values (v, n);
15  WHILE n >= num DO
16    select ceil(v/2) into v;
17    select n & (v - 1) into n;
18  END WHILE;
19  return n;
20end
21//
22delimiter ;
23
24update linear_hash_algo_test set p = linear_hash(i, 13);
25select p, count(*) from linear_hash_algo_test group by (p);
26
27...
28
29+------+----------+
30| p    | count(*) |
31+------+----------+
32|    0 |     1024 |
33|    1 |     1025 |
34|    2 |     1025 |
35|    3 |     1025 |
36|    4 |     1025 |
37|    5 |     2047 |
38|    6 |     2048 |
39|    7 |     2048 |
40|    8 |     1024 |
41|    9 |     1024 |
42|   10 |     1023 |
43|   11 |     1023 |
44|   12 |     1023 |
45+------+----------+
4613 rows in set (0.01 sec)