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 : [email protected]
Unsubscribe : https://launchpad.net/~ourdelta-developers
More help : https://help.launchpad.net/ListHelp
