maria-discuss team mailing list archive

[Henrik Ingo <henrik.ingo@xxxxxxxxxxxxx>]

On Tue, Nov 9, 2010 at 7:47 PM, Justin Swanhart <greenlion@xxxxxxxxx> wrote:
> This is kind of unrelated, but it seems like a good thread to ask the
> question in.
>
> Are there any plans to make an indexed virtual column work
> automatically as an index for the table?  Other databases support
> 'functional' indexing, that is, you can index the contents of a
> function call and the if you use that function call in the where
> clause, then the index is automatically used.
>
> For example:
> select * from some_table where UPPER(some_col) = 'ABC'
>
> In order to avoid a FTS you could create an indexed persistent virtual
> column for UPPER(some_col) and modify your SQL to use  it.  It would,
> however, be much nicer if the optimizer could detect a virtual column
> which matches the function call and use the index automatically if one
> exists on the virtual column, or scan the virtual column if it
> doesn't.
>
> Likely this could be done in the same phase as 'constant replacement'.
>  If an expression exactly matches a virtual column, replace the
> expression with a reference to the virtual column.  If the virtual
> column is indexed, then the index should get automatically used,
> otherwise it will get scanned.

I'm not aware of additional work being done related to this right now.

I understand what you suggest, but let's face it, this would just be a
convenience feature for those that are too lazy to rewrite their
queries to actually use the virtual column :-) Even if I'm not a
minimalist when it comes to features, I think what you suggest is
probably stretching the MySQL tradition of being simple and
straightforward.

henrik

-- 
henrik.ingo@xxxxxxxxxxxxx
+358-40-5697354
www.openlife.cc