[TYPO3-dev] indexed search and Mysql FULL TEXT index

[Martin Kutschker]

Dmitry Dulepov [typo3] schrieb:
> Hi!
> 
> Martin Kutschker wrote:
>> * Ideally the indexed search would be able to determine if it's tables 
>> are stored in a Mysql server.
> 
> I have code example for it. It is for tt_news but easy to adopt to 
> anything else. Here it is:
> 
> =======================
> $useFTS = false;
> if (gettype($GLOBALS['TYPO3_DB']->link) == 'mysql resource') {
>     // See if can use FTS
>     $res = $GLOBALS['TYPO3_DB']->sql_query('SHOW CREATE TABLE tt_news');
>     $row = $GLOBALS['TYPO3_DB']->sql_fetch_row($res);
>     $GLOBALS['TYPO3_DB']->sql_free_result($res);
>     $createInfo = strtolower($row[1]);
>     $useFTS = (strpos($createInfo, 'engine=myisam') && 
> strpos($createInfo, 'fulltext'));
> }
> if ($useFTS) {
>     // Rebuild query completely
>     $where = ' AND MATCH(title,bodytext) AGAINST (' .
>         $GLOBALS['TYPO3_DB']->fullQuoteStr($sw) . ' IN BOOLEAN MODE)';
> }

Thanx. I didn't think it was hard to do even without digging up the 
mentioned patch. BTW, I didn't mean to try to detect if the Mysql table 
had a full text index (I'd simply change the ext_tables.sql!), but that 
even if DBAL is loaded to check if it's tables are on a Mysql server.

> I doubt it would help much. Many indexed search performance problems 
> come from how it indexes, not only from how it searches.

To quote myself:

"I think there are better data structures for the search but at least 
[...] "

I meant that a real search index making use of Mysql's (or another DBs) 
full text index would use different data structures, but still we can 
probably speed up certain queries.

All "LIKE 'begin%'" queries are - more or less - ok. But you have to 
convince your users that this is a meaningful default ;-)

I think that the typical "LIKE '%part%'" query could benefit if it used 
the full text index instead of the regular index.

Masi