Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Won't Fix
-
None
-
None
-
None
Description
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
Attachments
Issue Links
- relates to
-
MDEV-431 Cassandra storage engine
-
- Closed
-