FullText Searching: The Dilemma
gerv at mozilla.org
Tue Jun 26 09:39:32 UTC 2007
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
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.
More information about the developers