MySQL Bugs: #43528: Column Filtering Interface

Bug #43528	Column Filtering Interface
Submitted:	10 Mar 2009 6:37	Modified:	12 Mar 2009 17:32
Reporter:	Mikiya Okuno	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Storage Engine API	Severity:	S4 (Feature request)
Version:	any	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Current storage engine interface is implemented so that it fetches or stores data per whole row. Such a design is good from the simplicity point of view, but it limits some flexibility like below if a column filtering interface exists otherwise:

o We can save a lot of bandwidth, as we don't have to pull/push the unrelated column over the wire. It's mostly useful for remote engines such as NDB, FEDERATED or DB2.
o It enables column oriented stores. or engines that may store parts of the row in different places. It's mostly useful for DWH or BI use.

How to repeat:
n/a

Suggested fix:
Add a new interface to specify bitmaps which indicates columns in interest. Then, a call sequence when reading rows will be like below:

handler::store_lock
handler::external_lock
handler::info
handler::rnd_init
handler::set_filter <--- a new filter interface
handler::extra
handler::rnd_next
handler::rnd_next
handler::rnd_next
handler::rnd_next
handler::rnd_next
handler::rnd_next
handler::rnd_next
handler::rnd_next
handler::rnd_next
handler::extra
handler::external_lock
handler::extra

I suppose that a handler instance will have a bitmap to indicate which columns are in interest, just like it has a bitmap which indicates NULL values.

IMO using a filter for lookup operation using PK or UNIQUE is costly. But it will pay-off when scan, range or multiple row update operations where only few columns are fetched/updated.

Whether a filter is used or not is left for storage engine implementations. In other words, a filter is set by an optimizer, and a storage engine can refer to the filter, and the storage engine may make use of a filter or may ignore it depending on its implementation. A storage engine can set NULL to unrelated columns when a filter is set.

I missed a few words:

o A storage engine can set NULL to unrelated columns if a filter is set when reading from a table.
o A storage engine can ignore unrelated columns if a filter is set when updating a table.

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

See in 5.1 TABLE::read_set and TABLE::write_set, and how they are used in, for example, NDB and InnoDB.