Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Won't Fix
-
Affects Version/s: None
-
Fix Version/s: 10.0.4
-
Component/s: None
-
Labels:None
Description
Sergei Petrunia 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
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-431 Cassandra storage engine
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Closing as "Wont' fix" because currently, there are no plans to fix it. Feel free to re-open if this issue becomes relevant.