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

[Martin Kutschker]

Dmitry Dulepov [typo3] schrieb:
> Hi!
> 
> Martin Kutschker wrote:
>> 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.
> 
> There is a little problem with ext_tables.sql. Using FULLTEXT means 
> forcing MyISAM. This has to be taken into account in ext_tables. But it 
> means worse performance in FE when index tables are used for simulaneous 
> indexing & querying.

You cannot have both :-)

>> 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 ;-)
> 
> MySQL can optimize 'whatever%'. But it cannot do anything about 
> '%whatever%' or '%whatever' :(

Exactly. But what should it do with '%whatever%' or '%whatever'? 
Traditional indices cannot cope with that.

>> I think that the typical "LIKE '%part%'" query could benefit if it 
>> used the full text index instead of the regular index.
> 
> Fully agree. But you still cannot get rid of indexing and querying at 
> the same time, which is disaster with MyISAM.

Make the indexing delayed. Spool the reindex requests and run them once 
a hour/day/... If you have much space on your hard disks you can even 
try to work with to copies of the index tables. While the one table is 
busy with reindexing (can even be done on another machine!) you query 
the other one. After the indexing is done you start querying the other 
table and reindex your old one.

Masi