On Tue, Nov 9, 2010 at 7:47 PM, Justin Swanhart <greenlion@gmail.com> 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@avoinelama.fi +358-40-5697354 www.openlife.cc