maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

Hi Henrik,

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.

Regards,

--Justin
On Tue, Nov 9, 2010 at 1:00 AM, Henrik Ingo <henrik.ingo@xxxxxxxxxxxxx> wrote:
> On Tue, Nov 9, 2010 at 5:03 AM, Federico Razzoli <federico_raz@xxxxxxxx> wrote:
>> Hello
>>
>> I've read in the documentation that any legal SQL expression SHOULD be allowed
>> in the Virtual Columns definition.
>
> That opening statement (on http://kb.askmonty.org/v/virtual-columns)
> is misleading in many ways and I've commented on the article but it is
> not yet fixed.
>  - subqueries or anything that depends on data outside the row is not possible
>  - only deterministic functions are possible
>  - UDF and stored functions are not possible
>
>
> henrik
>
> --
> henrik.ingo@xxxxxxxxxxxxx
> +358-40-5697354
> www.openlife.cc
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>