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@avoinelama.fi> wrote:
On Tue, Nov 9, 2010 at 5:03 AM, Federico Razzoli <federico_raz@yahoo.it> 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@avoinelama.fi +358-40-5697354 www.openlife.cc
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp