|
psergey wrote:
For the record: this is happening because Table Elimination uses Field::part_of_key to check whether a field is part of an index.
This is not correct, Field::part_of_key bit is set if the field is a part of an index, and the index is covering.
This means I should fix table elimination. I consider all this low-priority, though.
I have 3 tables (Cassandra, InnoDB, MyISAM) with the same structure and contents (10K rows, load file is attached), and run the same query.
Tables:
mysql> show create table sbtest \G
|
*************************** 1. row ***************************
|
Table: sbtest
|
Create Table: CREATE TABLE `sbtest` (
|
`rowkey` int(10) unsigned NOT NULL,
|
`k` int(10) unsigned NOT NULL DEFAULT '0',
|
`c` char(120) NOT NULL DEFAULT '',
|
`pad` char(60) NOT NULL DEFAULT '',
|
PRIMARY KEY (`rowkey`)
|
) ENGINE=CASSANDRA DEFAULT CHARSET=latin1 `thrift_host`='localhost' `keyspace`='sysbench' `column_family`='sbtest'
|
1 row in set (0.00 sec)
|
|
mysql> show create table sbtest_inno \G
|
*************************** 1. row ***************************
|
Table: sbtest_inno
|
Create Table: CREATE TABLE `sbtest_inno` (
|
`rowkey` int(10) unsigned NOT NULL,
|
`k` int(10) unsigned NOT NULL DEFAULT '0',
|
`c` char(120) NOT NULL DEFAULT '',
|
`pad` char(60) NOT NULL DEFAULT '',
|
PRIMARY KEY (`rowkey`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
1 row in set (0.00 sec)
|
|
mysql> show create table sbtest_myisam \G
|
*************************** 1. row ***************************
|
Table: sbtest_myisam
|
Create Table: CREATE TABLE `sbtest_myisam` (
|
`rowkey` int(10) unsigned NOT NULL,
|
`k` int(10) unsigned NOT NULL DEFAULT '0',
|
`c` char(120) NOT NULL DEFAULT '',
|
`pad` char(60) NOT NULL DEFAULT '',
|
PRIMARY KEY (`rowkey`)
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1
|
1 row in set (0.00 sec)
|
|
EXPLAINs:
mysql> explain select s.k from sbtest s left join sbtest t using (rowkey) where s.rowkey>100 and s.rowkey < 120;
|
+------+-------------+-------+--------+---------------+---------+---------+---------------+------+-----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+---------------+---------+---------+---------------+------+-----------------------------------------------------+
|
| 1 | SIMPLE | s | ALL | PRIMARY | NULL | NULL | NULL | 1000 | Using where |
|
| 1 | SIMPLE | t | eq_ref | PRIMARY | PRIMARY | 4 | test.s.rowkey | 1 | Using join buffer (flat, BKAH join); multiget_slice |
|
+------+-------------+-------+--------+---------------+---------+---------+---------------+------+-----------------------------------------------------+
|
2 rows in set (0.00 sec)
|
|
mysql> explain select s.k from sbtest_inno s left join sbtest_inno t using (rowkey) where s.rowkey>100 and s.rowkey < 120;
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|
| 1 | SIMPLE | s | range | PRIMARY | PRIMARY | 4 | NULL | 17 | Using where |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|
1 row in set (0.00 sec)
|
|
mysql> explain select s.k from sbtest_myisam s left join sbtest_myisam t using (rowkey) where s.rowkey>100 and s.rowkey < 120;
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
|
| 1 | SIMPLE | s | range | PRIMARY | PRIMARY | 4 | NULL | 27 | Using index condition |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
|
1 row in set (0.01 sec)
|
Non-default join_cache_level:
mysql> select @@join_cache_level;
|
+--------------------+
|
| @@join_cache_level |
|
+--------------------+
|
| 8 |
|
+--------------------+
|
Default optimizer_switch:
mysql> select @@optimizer_switch;
|
@@optimizer_switch
|
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off
|
Cassandra structures:
CREATE KEYSPACE sysbench WITH strategy_class = 'SimpleStrategy'
|
AND strategy_options:replication_factor = '1';
|
|
USE sysbench;
|
|
CREATE TABLE sbtest (
|
id bigint PRIMARY KEY,
|
c text,
|
k bigint,
|
pad text
|
) WITH
|
comment='' AND
|
caching='KEYS_ONLY' AND
|
read_repair_chance=0.100000 AND
|
gc_grace_seconds=864000 AND
|
replicate_on_write='true' AND
|
compaction_strategy_class='SizeTieredCompactionStrategy' AND
|
compression_parameters:sstable_compression='SnappyCompressor';
|
bzr revno: 3495
|
sbtest_myisam.txt