MySQL Bugs: #3705: Index Pre-cache failed for table with different index block sizes

Bug #3705	Index Pre-cache failed for table with different index block sizes
Submitted:	10 May 2004 23:54	Modified:	7 Feb 2006 1:28
Reporter:	Haitao Jiang	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S4 (Feature request)
Version:	5.0 4.1	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
One nice feature of MySQL 4.1.1 is that user can pre-load or pre-cache the key or idnexes into memory cache. However, with current implementation, it is not very useful (my opinion). 

I have a table with full-text and other indexes. I can not control the index block size, so there are indexes of 512 index block size, some with 1024 index block size (full-text indexes). Currently this feature requires that all indexes are of the same block size. Thus I can not pre-cache the indexes at present time. 

So, either
- user can force the index block size to be the same
or 
- key pre-cache feature can take indexes of different block size
or 
- users can pre-load certain subset of all indexes of a table

Above are all missing from the MySQL 4.1.1.

How to repeat:
Easy. Just create a table with different columns and different indexes. Then try to pre-cache the keys. It will fail.

Suggested fix:
See above.

A status update on this: it's a desired non-urgent improvement so it's not likely to be implemented in the next 12 months or so. No ETA beyond that.

The fix for bug 17332 removes this limitation of the LOAD INDEX INTO CACHE statement unless the IGNORE LEAVES modifier is used. It's currently planned to include this in 5.1 and then add to 5.0 after some production experience with it.

The fix for bug 17332 that removes this limitation of the LOAD INDEX INTO CACHE statement unless the IGNORE LEAVES modifier is used was released originally in 5.1.19 and has now been put into 5.0.87.

This leaves only the work of making it work if IGNORE LEAVES is specified still to do. That is being handled as a feature request, no timeline for when it will happen.