FullText Searching: The Dilemma

[Gervase Markham]

Max Kanat-Alexander wrote:
> 	In MySQL, InnoDB tables don't support FULLTEXT indexes. But
> MyISAM tables don't support transactions.

Are either of these facts going to change in the near future?

Just to be clear: different tables in the same database can use 
different storage engines?

Can we support transactions on a MyISAM table by having a shadow InnoDB 
table with 1:1 row correspondence but no content, whose rows we lock 
before accessing the real table and unlock afterwards?

This is like the bugs/bugs_more plan except that it doesn't require a 
split; bugs_shadow_locking would exist on MySQL only, and could go away 
in the future if need be without changing much code.

Another random thought: would it be possible to mirror the full text 
fields into a second MyISAM "cache" table? Again, this avoids the 
restructuring needed for a split. But perhaps it wouldn't make it any 
easier to write the search code.

> 	So, for 3.0 we removed bugs.short_desc from the FULLTEXT
> searching and now we just use a normal LIKE search. However, you can
> see that that makes large installations like bugzilla.mozilla.org very
> slow.

Only large installations? Can we fix this (thinking laterally) by 
throwing more hardware at the b.m.o. problem?

> 	I've looked into using an external fulltext engine, like
> Lucene or something similar. The advantage is that fulltext would be the
> same across every DB, and we could rank based on all comments combined.

It's Yet Another Dependency, of course. And it doesn't seem very good 
architecturally, for the reasons you mention.

Gerv