The idea being to move the handler to be a cursor on a table, with
actions not pertaining that to reside in StorageEngine (e.g. DDL).
That sounds like an interesting Idea. This would though involve quite a
few changes. Would the curser then still be row based? Or might you
convert to a more column oriented way?
We had a more column oriented API in NDB, and found that going to
NdbRecord (very much row based) got us a pretty big performance boost.
Or are you thinking for column based engines?
Multiple things. At first of course at column store oriented engines.
The problem with this is that the MySQL core is strongly row based and
would not take any column oriented optimizations into account even if
the engine is column oriented. So any column store would actually need
to bring their own optimizer and partially operators with them. The
Idea would be to have a curser on the table (as proposed by you) but
when scrolling over the data set to have two different access methods.
One column oriented and the other row oriented. With both of these
options it would be possible to access complete rows or have the SE it
self filter out the unnecessary columns. Maybe the access methods could
be controllable by the capabilities of the SE. This would give the
optimizer multiple possibilities to access the data on a row with
different costs. For column oriented stores it would be cheaper to read
over the column API and for row based it would read over the row API.
To compensate the different APIs you can define different
transformation operators. Also this way specific selections can be
pushed down to the SE very easily--SELECT a,b FROM t would only read
columns a and b from the table t. Instead of reading the complete row
of t (maybe consisting of N+1 columns). The performance degradation of
the API to NDB is possibly due to the fact that MySQL would read the
complete row from a column oriented interface (speculation only. Don't
know the work on this topic in NDB). There has been quite a bit of
publications on the SIGMOD '09 that column stores are very suitable for
analytics as well as OLTP. So maybe this might be an interesting trend?
Also having the different access methods modularizes the DBMS a lot
more --> more plugins :) wich is in the sense of Drizzle.