Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.0.9
-
None
Description
Compare to default settings we have
join_cache_level = 6
mrr_buffer_size = 96M
tmp_table_size = 96M
max_heap_table_size = 96M
This is the correct result :
Without touching optimizer switch :
SELECT task_id,field FROM ym_admin_db.ym_tasks_options WHERE task_id IN (SELECT t |
sk_id FROM ym_admin_db.ym_tasks WHERE NOT(type IN (8,11) ) ) ORDER BY task_id; |
+---------+-----------------------------------+
|
| task_id | field |
|
+---------+-----------------------------------+
|
| 1 | RejectFilesOnPassBinlist |
|
| 1 | Title |
|
| 1 | PassBinlistForRejectTest |
|
| 1 | DataPath |
|
….
|
+---------+-----------------------------------+
|
39 rows in set (0.01 sec)
|
|
|
+------+-------------+------------------+-------+----------------------+---------------------+---------+------------------------------+------+-----------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref
|
| rows | Extra |
|
+------+-------------+------------------+-------+----------------------+---------------------+---------+------------------------------+------+-----------------------------------------------------------+
|
| 1 | PRIMARY | ym_tasks | index | PRIMARY,ymtasks_type | ymtasks_type | 2 | NULL
|
| 1 | Using where; Using index; Using temporary; Using filesort |
|
| 1 | PRIMARY | ym_tasks_options | ref | ymtasksoptions_task | ymtasksoptions_task | 4 | ym_admin_db.ym_tasks.task_id | 47 | |
|
+------+-------------+------------------+-------+----------------------+---------------------+---------+------------------------------+------+-----------------------------------------------------------+
|
This is the wrong result with mrr=on
SELECT task_id,field FROM ym_admin_db.ym_tasks_options WHERE task_id IN (SELECT task_id FROM ym_admin_db.ym_tasks WHERE NOT(type IN |
(8,11) ) ) ORDER BY task_id; |
+---------+-------+
|
| task_id | field |
|
+---------+-------+
|
| 1 | Title |
|
+---------+-------+
|
1 row in set (0.00 sec)
|
|
|
+------+-------------+------------------+-------+----------------------+---------------------+---------+----
|
------------------------+------+--------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref
|
| rows | Extra |
|
+------+-------------+------------------+-------+----------------------+---------------------+---------+----
|
------------------------+------+--------------------------------------------------------------------+
|
| 1 | PRIMARY | ym_tasks | index | PRIMARY,ymtasks_type | ymtasks_type | 2 | NUL
|
| 1 | Using where; Using index; Using temporary; Using filesort |
|
| 1 | PRIMARY | ym_tasks_options | ref | ymtasksoptions_task | ymtasksoptions_task | 4 | ym_
|
min_db.ym_tasks.task_id | 47 | Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan |
|
+------+-------------+------------------+-------+----------------------+---------------------+---------+----
|
------------------------+------+--------------------------------------------------------------------+
|
CREATE TABLE `ym_tasks` ( |
`task_id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
`node_id` int(11) DEFAULT NULL, |
`user_id` int(11) DEFAULT NULL, |
`group_id` int(11) DEFAULT NULL, |
`database_id` int(11) DEFAULT NULL, |
`name` varchar(256) NOT NULL DEFAULT '', |
`type` tinyint(3) DEFAULT NULL, |
`enabled` tinyint(3) NOT NULL DEFAULT '1', |
`permisions` mediumint(8) NOT NULL DEFAULT '0', |
`creation_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', |
`expiration_date` datetime DEFAULT NULL, |
`last_update` datetime DEFAULT NULL, |
`mutex` varchar(256) DEFAULT NULL, |
PRIMARY KEY (`task_id`), |
KEY `ymtasks_database` (`database_id`), |
KEY `ymtasks_node` (`node_id`), |
KEY `ymtasks_group` (`group_id`), |
KEY `ymtasks_user` (`user_id`), |
KEY `ymtasks_lastupdate` (`last_update`), |
KEY `ymtasks_mutex` (`mutex`), |
KEY `ymtasks_type` (`type`) |
) ENGINE=TokuDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 `COMPRESSION`=TOKUDB_LZMA; |
CREATE TABLE `ym_tasks_options` ( |
`task_id` int(10) unsigned NOT NULL DEFAULT '0', |
`field` varchar(256) NOT NULL DEFAULT '', |
`value` blob,
|
KEY `ymtasksoptions_task` (`task_id`), |
KEY `ymtasksoptions_field` (`field`) |
) ENGINE=TokuDB DEFAULT CHARSET=latin1 `COMPRESSION`=TOKUDB_LZMA; |