Full text search with MODx

Extract of MySQL manual:

By default, the MATCH() function performs a natural language search for a string against a text collection. A collection is a set of one or more columns included in a FULLTEXT index. The search string is given as the argument to AGAINST(). For each row in the table, MATCH() returns a relevance value; that is, a similarity measure between the search string and the text in that row in the columns named in the MATCH() list.

I did some tests regarding the full text search with ajaxSearch and my first conclusions is that the correct form of the sql statement should looks like:(muraille de chine = china wall)

But for the moment the fulltext index define on modx_site_content is:
and for modx_site_tmplvar_contentvalues it doesn’t exist a FULLTEXT KEY. The same for modx_jot_content regarding the content field (some guys asks for a search in the jot comments as in the TVs)

So I have some questions ...
How have been defined the FULLTEXT Key of Modx tables and does it possible to plan some changes ?
For example to add the fields longtitle and introtext in the index of modx_site_content
And create a new one for the modx_site_tmplvar_contentvalues ?

And obviously how could we evaluate the impacts of these changes on the database volume ?

Any ideas & remarks ? Feed backs about these points are welcome

IMHO, searching should be extracted to a separate indexing table or repository of some sort. I’ve already done a proof of concept with Lucene, but for most people Lucene will not be a possibility, so I believe we need to find a native MODx solution to this which can be plugged in and exchanged with other search indexing tools.

The problem is, you can’t add all the fields we want to the existing full-text indexes due to key length problems, not to mention the degradation of performance that would also result. And even then, search only works on content stored statically in these fields. As soon as placeholders, snippets, embedded chunks, and TV’s come into play, we have another search problem.

My goal is to remove the full-text index completely soon, but we have to have a viable alternative before that can be done, and I believe the best solution is an external index (i.e. a separate table that gets updated when content fields are modified). Unfortunately, this is not trivial.

Thanks Jason for this feedback.

I’ve already done a proof of concept with Lucene, but for most people Lucene will not be a possibility, so I believe we need to find a native MODx solution to this which can be plugged in and exchanged with other search indexing tools.

Why for most people Lucene will not be a possibility ? Is it because the Zend framework is based on Php 5 ?
Otherwise, I understand the constraints regarding the placeholders, snippets, embedded chunks, and TV’s. But may be the solution is to indexe content pages after the parsing of these dynamic outputs.

Lucene excepted, does it exist some other php search indexing tools ?

Lucene excepted, does it exist some other php search indexing tools ?

I’ve used this in the past (PHP/MySQL based): http://www.sphider.eu/

I was actually using Solr for my proof of concept, not just Lucene, which is a lot more advanced than the PHP Zend_Search_Lucene tool, which simply uses a Lucene-compatible search index, but is in no way a fully featured Lucene engine. Until recently, you couldn’t even use UTF-8 content with it and yes it is PHP 5 only, but, it is still a potential solution to this problem for some.

Otherwise, I think we need to come up with a proprietary solution, perhaps just using a separate table with records that store indexed data in a similar way to Lucene (store a field name and reference to the page it appears on, along with the final, parsed content). With simple full-text indexes to allow us to search in a similar manner to the current search tools (i.e. using MySQL full-text search capabilities).