--- On Thu, 23 Oct 2008, Arjen Lentz <arjen@xxxxxxxxxxxxxxxx> wrote:
On 23/10/2008, at 4:10 AM, Andrey Zhakov wrote:
MATCH is a complex feature itself. IMO, mixing up MATCH with virtual
columns is over-complication. I want to keep things as simple as
possible but if you know how a potential customer could benefit from
that (I intentionally added the TODO comment to raise a discussion)
then please let me know..
Hehe - smart fella ;-)
Can you describe what a MATCH-based virtual column is/does?
MATCH-based virtual columns are virtual columns whose function
expression contains the keyword MATCH.
E.g. For table articles in http://dev.mysql.com/doc/refman/5.1/en/fulltext-natural-language.html
a vcol might be created as follows:
vcol_name VIRTUAL DOUBLE AS (match(title,body) against ('database'
in natural language mode))
MATCH returns the DOUBLE-type relevance of the found record
according to the searched result.
In general virtual columns can be used to:
1) Simplify frequently used SELECT's
So instead of
select title,body,
match(title,body) against ('database' in natural language
mode) as relevance
from articles
where match (title,body) against ('database' in natural language
mode)
having relevance > 0.5
you could write
select title,body,vcol_field
from articles
where vcol_field > 0.5
2) Define a functional index.
3) Use them instead of BEFORE INSERT triggers to speed up inserts
4) Use them instead of BEFORE INSERT triggers to ensure the values
are always re-calculated when the vcol expression is altered.
5) Any other?..
Honestly I am not sure about any potential use case for MATCH-based
virtual columns.
I also think that the effort to re-enable MATCH in my patch in not
that big so I would not like to spend too much time on discussions...
Cheers,
Andrey.
