| Bug #28357 | The metadata of memory storage engine cause excessive cost for index_merge | ||
|---|---|---|---|
| Submitted: | 10 May 2007 16:06 | Modified: | 12 Jul 2007 6:47 |
| Reporter: | Georgi Kodinov | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.1-BK | OS: | Any |
| Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
[10 May 2007 16:22]
Sveta Smirnova
Thank you for the report. Verified as described.
[16 May 2007 14:24]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/26833 ChangeSet@1.2517, 2007-05-16 17:24:10+03:00, gkodinov@magare.gmz +3 -0 Bug #28357: The heap storage engine's metadata cause the optimizer to calculate prohibitively high cost for index_merge. This effects that index_merge access method is not considered for queries that would otherwise benefit from it. Fixed by seting block_size to UINT_MAX to represent the fact that the heap engine does no disk access.
[12 Jul 2007 6:47]
Sergey Petrunya
During analysis of this bug we've discovered BUG#29740 (wrong query results for HEAP + index_merge). That bug was fixed by disabling ROR scans for HEAP and RBTREE indexes. After BUG#29740, the problematic cost calculations are never performed. They are also not performed for the reason that HEAP table indexes do not have HA_KEYREAD_ONLY flag, and so get_index_only_read_time() (or its 5.0 equivalent) are never invoked. Considering the above, setting this bug to "Not a bug".
Description: The heap storage engine's metadata cause the optimizer to calculate prohibitively high cost for index_merge. This effects that index_merge access method is not considered for queries that would otherwise benefit from it. How to repeat: create table t1_isam (pk int primary key, key1 int not null, key2 int not null, key (key1), key (key2)); set autocommit=0; let $1=10000; --disable_query_log while ($1) { eval insert into t1_isam values ($1, $1 / 100, $1 / 100); dec $1; } --enable_query_log set autocommit=1; create table t1_mem (pk int primary key, key1 int not null, key2 int not null, key (key1), key (key2)) engine=memory; insert into t1_mem select * from t1_isam; #Should use index_merge explain select pk from t1_isam where key1 = 10 and key2 = 10; #Should use the same access method as t1_isam explain select pk from t1_mem where key1 = 10 and key2 = 10; Suggested fix: set stats.block_size to UINT_MAX in the heap's engine constructor. Rationale: block_size is used in index cost calculations. It is used to get the number of disk seeks required to retrieve a number of index tuples. See ha_myisammrg::info for more details. The handler code initializes that to 0. For the heap engine there will be no disk seeks at all so block_size must be a very high number to represent that fact.