Real search indexing

[dantleech]

What options are there for supporting a real search indexing system internally without relying on another service (such as Lucene) ?

[lsmith77]

we could add an inverted index to support some pre filtering for certain queries (http://en.m.wikipedia.org/wiki/Inverted_index). another option could be trying to leverage Zend Search Lucene for this.

A totally different approach could be to try and leverage information about specific node types to store required fields in a more normalized form that supports indexing. ie. if a node type requires a "foo" property, then make it somehow possible to tell doctrine dbal to create a table for this node type, store the data for foo in a column there and add an index, so that whenever an SQL2 query is run searching on "foo", then it doesn't need to use an XPath statement to filter on that property.

[wachterjohannes]

@dantleech and i found a solution to fulltext match in mysql (http://dev.mysql.com/doc/refman/5.1/de/fulltext-search.html)

mysql> SELECT id, body, MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root') AS score
    -> FROM articles WHERE MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root');
+----+-------------------------------------+-----------------+
| id | body                                | score           |
+----+-------------------------------------+-----------------+
|  4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 |
|  6 | When configured properly, MySQL ... | 1.3114095926285 |
+----+-------------------------------------+-----------------+
2 rows in set (0.00 sec)

[hacfi]

@wachterjohannes Doesn’t work with InnoDB though...just MyISAM.

Which queries/search options does Doctrine provide (if any)?

[lsmith77]

Since MySQL 5.6 InnoDB has support for fulltext indexes. overall I have not been too impressed with the performance of RDBMS native fulltext indexing solutions. Of course PostgreSQL also has a solution.

[dantleech]

I like the idea of adding abstract support for search engines to Jackalope. We could have a adapters at the jackalope level for native PHP Lucine, Elastic Search, etc

[dbu]

lets see for 2.0: https://github.com/Jackalope2/jackalope/wiki/Query-Adapters