Re: [Ourdelta-developers] Virtual columns patch

[Arjen Lentz]

Hi Andrey

On 23/10/2008, at 7:23 PM, Andrey Zhakov wrote:
--- On Thu, 23 Oct 2008, Arjen Lentz <[EMAIL PROTECTED]> 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.


Well, the story on FULLTEXT itself is:
 - it's MyISAM only where most people now use InnoDB for serious  
production.
 - FULLTEXT indexes do not scale either in terms of insertion, or  
searching when the dataset grows.

So frankly, for the real world *I* don't care much for FULLTEXT any  
more, and people are moving over to Sphinx accessing it directly from  
their app. The OurDelta builds of course have the Sphinx storage  
engine so you can join onto the results again - with any engine.


Regards,
Arjen.
--
Arjen Lentz, Director @ Open Query (http://openquery.com.au)
Training and Expertise for MySQL in Australia and New Zealand

OurDelta: free enhanced builds for MySQL @ http://ourdelta.org





_______________________________________________
Mailing list: https://launchpad.net/~ourdelta-developers
Post to     : ourdelta-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~ourdelta-developers
More help   : https://help.launchpad.net/ListHelp