Details
-
Type:
Task
-
Status: Open
-
Priority:
Major
-
Resolution: Unresolved
-
Fix Version/s: 10.1
-
Component/s: None
-
Labels:None
Description
In 5.3 derived_merge was passing the CONVERT_IF_BIGGER_TO_BLOB and would force the optimizer not to produce on disk temporary table . It stopped working in 5.5
I have documented the issue here :
http://varokism.blogspot.fr/2011/12/back-on-disk-temporary-tables.html
At that time no test case have been produce and merging may have break this improvement.
Gliffy Diagrams
Attachments
Issue Links
- is blocked by
-
-
- Stalled
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
I have explored why MySQL/MariaDB has CONVERT_IF_BIGGER_TO_BLOB constant.
The reason is: temporary tables use "Fixed" row format. This means that a temporary table with this definition
CREATE TABLE tmp (col1 varchar(3000) CHARACTER SET utf8);
will use 3000* max_char_len(utf8) = 3000* 3= 9000 bytes for each row. Dynamic row format (and blobs will use dynamic row format) is much more economical.
One may ask a question: why can't we use a heap temporary table with a dynamic row format? The answer for this is not yet clear for me. I'll investigate.
It turns out, MariaDB doesn't support row_format=dynamic for heap tables.
Percona supports row_format=dynamic as of 5.5. However, their create_tmp_table() code is not aware of that, so Percona Server will still create an on-disk table for the provided example.
In my opinion, this cannot be fixed in MariaDB 5.5. It is quite risky to make changes in create_tmp_table() code. We could consider this as a feature for MariaDB 10.0.
Hi Sergei,
Should we convert it into a task and target for 10.1?
Personally, I would like to see this implemented, so I think converting it into a task is a good idea. 
Hi Stephane,
I've tried to investigate the issue. I created this testcase:
CREATE TABLE `login2` (
`user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_name` varchar(50) DEFAULT NULL,
`user_email` varchar(2000) CHARACTER SET utf8 DEFAULT NULL,
`created` datetime DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=102048 DEFAULT CHARSET=latin1;
insert into login2 values (100002, 'user-100002', 'email-100002', now());
insert into login2 values (100003, 'user-100003', 'email-100003', now());
insert into login2 values (100004, 'user-100004', 'email-100004', now());
insert into login2 values (100007, 'user-100007', 'email-100007', now());
insert into login2 values (100008, 'user-100008', 'email-100008', now());
select * from (
select
user_id,
created,
(select user_email from login2 b where b.user_id=a.user_id ) as mail
from
login2 a
where
a.user_id IN (100005, 100002, 100004, 100003)
) as a order by created;
and ran it.
Then, I replaced varchar(2000) with varchar(200) and ran it again. In both cases, the EXPLAIN was
-----
-----------------------------------------------------------------------------------------------------+-----
-----------------------------------------------------------------------------------------------------+-----
-----------------------------------------------------------------------------------------------------+