| Bug #4221 | MySQL does not use in memory table when it should | ||
|---|---|---|---|
| Submitted: | 19 Jun 2004 23:18 | Modified: | 2 Sep 2004 13:51 |
| Reporter: | Peter Zaitsev (Basic Quality Contributor) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.0.20 | OS: | Any (all) |
| Assigned to: | Alexey Botchkov | CPU Architecture: | Any |
[2 Sep 2004 13:51]
Alexey Botchkov
Sorry, but the bug system is not the appropriate forum for asking support questions. Your problem is not the result of a bug. For a list of more appropriate places to ask for help using MySQL products, please visit http://www.mysql.com/support/ Thank you for your interest in MySQL. Additional info: You see, temporary tables have to contain items from ORDER BY clause. In this case it's 'bt1.z' (which is a BLOB) to MySQL uses disk table. You can change query to select left(bt.z,1) from bt,bt bt1 order by left(bt1.z, 1); to fix that
Description: As MySQL can't use HEAP temporary tables in case there are BLOBs in select list, customer tried workaround - to use left() to select the short prefix, hoping this would allow MySQL to use heap temporary table, it however does not help. mysql> select left(bt.z,1) from bt,bt bt1 order by bt1.z; +--------------+ | left(bt.z,1) | +--------------+ | a | | b | | a | | b | +--------------+ 4 rows in set (0.01 sec) Status: | Created_tmp_disk_tables | 1 | | Created_tmp_tables | 1 | How to repeat: CREATE TABLE bt ( z blob ) TYPE=MyISAM; -- -- Dumping data for table `bt` -- INSERT INTO bt VALUES ('a'),('b'); select left(bt.z,1) from bt,bt bt1 order by bt1.z;