spetrunia

MariaDB [test]> EXPLAIN SELECT *
    -> FROM t1a LEFT JOIN t2a ON (b1 = a2)
    -> WHERE (b1, b1) IN (SELECT a3, b3 FROM t3a);
+------+--------------+-------------+------+---------------+------+---------+-------------+------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-------------+------+---------------+------+---------+-------------+------+-------------------------------------------------+
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 3 | |
| 1 | PRIMARY | t1a | ref | b1 | b1 | 2 | test.t3a.a3 | 1 | Using index |
| 1 | PRIMARY | t2a | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Using join buffer (flat, BNL join) |
| 2 | MATERIALIZED | t3a | ALL | NULL | NULL | NULL | NULL | 3 | Using where |

spetrunia

  Breakpoint 3, create_ref_for_key (join=0x7fff2c026dc8, j=0x7fff2c02cd78, org_keyuse=0x7fff2c027890, allow_full_scan=true, used_tables=4611686018427387911) at /home/psergey/dev2/5.5/sql/sql_select.cc:7820
(gdb) wher
  #0  create_ref_for_key (join=0x7fff2c026dc8, j=0x7fff2c02cd78, org_keyuse=0x7fff2c027890, allow_full_scan=true, used_tables=4611686018427387911) at /home/psergey/dev2/5.5/sql/sql_select.cc:7820
  #1  0x0000000000648f18 in get_best_combination (join=0x7fff2c026dc8) at /home/psergey/dev2/5.5/sql/sql_select.cc:7623
  #2  0x000000000063ff6a in make_join_statistics (join=0x7fff2c026dc8, tables_list=..., conds=0x7fff2c029d88, keyuse_array=0x7fff2c0270c8) at /home/psergey/dev2/5.5/sql/sql_select.cc:3725
  #3  0x000000000063757b in JOIN::optimize (this=0x7fff2c026dc8) at /home/psergey/dev2/5.5/sql/sql_select.cc:1204
  #4  0x000000000063dcad in mysql_select (thd=0xa649390, rref_pointer_array=0xa64c0f0, tables=0x7fff2c006738, wild_num=0, fields=..., conds=0x7fff2c025ee0, og_num=0, order=0x0, group=0x0, h

spetrunia

a# 2013-01-16T00:12:58 -------------------------------
# 2013-01-16T00:12:58 Configuration
# 2013-01-16T00:12:58   debug_server => ['']
# 2013-01-16T00:12:58   dsn => ['dbi:mysql:host=127.0.0.1:port=3307:user=root:database=test']
# 2013-01-16T00:12:58   duration => 300
# 2013-01-16T00:12:58   gendata => leveldb-bug-1.zz
# 2013-01-16T00:12:58   generator => FromGrammar
# 2013-01-16T00:12:58   grammar => leveldb-bug-1.yy
# 2013-01-16T00:12:58   queries => 100000000
# 2013-01-16T00:12:58   reporters => ['']

spetrunia

==1341== Invalid read of size 1
==1341==    at 0x8A8523D: my_utf8_uni (ctype-utf8.c:5282)
==1341==    by 0x8A85BF5: my_strnncoll_utf8 (ctype-utf8.c:5674)
==1341==    by 0x86ADE65: hashcmp (hash.c:359)
==1341==    by 0x86ADC65: my_hash_first_from_hash_value (hash.c:275)
==1341==    by 0x86ADBAC: my_hash_first (hash.c:248)
==1341==    by 0x86ADABD: my_hash_search (hash.c:216)
==1341==    by 0x86E5854: Table_ddl_manager::find(unsigned char*, unsigned int) (ldb_datadic.cc:566)
==1341==    by 0x86E4BB8: Primary_key_comparator::Compare(leveldb::Slice const&, leveldb::Slice const&) const (ha_leveldb.cc:154)
==1341==    by 0x86E0BAF: Primary_key_comparator::compare_keys(unsigned char const*, unsigned int, unsigned char const*, unsigned int) (ha_leveldb.cc:182)

spetrunia

et

spetrunia

+----+------+-----------------+-------+---------+------+-------+------------------+
| Id | User | Host            | db    | Command | Time | State | Info             |
+----+------+-----------------+-------+---------+------+-------+------------------+
|  9 | root | localhost       | NULL  | Query   |    0 | init  | show processlist |
|  8 | root | localhost:54620 | test4 | Sleep   |    0 |       | NULL             |
|  7 | root | localhost:54619 | test4 | Sleep   |    0 |       | NULL             |
|  6 | root | localhost:54618 | test4 | Sleep   |    0 |       | NULL             |
|  5 | root | localhost:54617 | test4 | Sleep   |    0 |       | NULL             |
+----+------+-----------------+-------+---------+------+-------+------------------+
5 rows in set (0.01 sec)

spetrunia

=== modified file 'sql/opt_subselect.cc'
--- sql/opt_subselect.cc  2013-01-07 19:21:05 +0000
+++ sql/opt_subselect.cc	2013-01-28 07:25:49 +0000
@@ -2542,6 +2542,12 @@ void advance_sj_state(JOIN *join, table_
           /* Mark strategy as used */ 
           (*strategy)->mark_used();
           pos->sj_strategy= sj_strategy;
+
+          if (sj_strategy= SJ_OPT_MATERIALIZE)
+            join->sj_materialized_lookup_tables |= handled_fanout;

spetrunia

=== modified file 'sql/opt_subselect.cc'
--- sql/opt_subselect.cc  2013-01-07 19:21:05 +0000
+++ sql/opt_subselect.cc	2013-01-28 07:36:17 +0000
@@ -2542,6 +2542,12 @@ void advance_sj_state(JOIN *join, table_
           /* Mark strategy as used */ 
           (*strategy)->mark_used();
           pos->sj_strategy= sj_strategy;
+
+          if (sj_strategy == SJ_OPT_MATERIALIZE)
+            join->sj_materialized_lookup_tables |= handled_fanout;

spetrunia

  === modified file 'sql/opt_subselect.cc'
--- sql/opt_subselect.cc  2013-01-07 19:21:05 +0000
+++ sql/opt_subselect.cc	2013-01-28 07:38:00 +0000
@@ -2542,6 +2542,12 @@ void advance_sj_state(JOIN *join, table_
           /* Mark strategy as used */ 
           (*strategy)->mark_used();
           pos->sj_strategy= sj_strategy;
+
+          if (sj_strategy == SJ_OPT_MATERIALIZE)
+            join->sj_materialized_lookup_tables |= handled_fanout;

spetrunia

LevelDB is an open source on-disk key-value store and is based on concepts from
Google's BigTable. It uses log-structured storage, data compression, and aims 
to achieve high performance for update-heavy workloads. LevelDB is the database
that is currently embedded in Chrome, the web browser from Google.

Commonly used MySQL storage engines (like MyISAM or InnoDB) use B-Tree indexes
and in-place updates. These are proven approaches to storage, but they have 
inherent limitations on number of updates per second and achievable 
compression ratio. With recent changes in hardware characteristics, these
limitations become a bottleneck.