FullText Searching: The Dilemma

[Max Kanat-Alexander]

On Tue, 26 Jun 2007 10:39:32 +0100 Gervase Markham <gerv at mozilla.org>
wrote:
> 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?

	No. I talked to some folks at MySQLConf. Anyhow, "near future"
would mean 5.2 or later, and we couldn't require that for quite some
time.

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

	Yes.

	We already do--longdescs is still MyISAM in 3.1. (We update it
outside of transactions.)

> 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?

	Not really. That's still table-locking, and table-locking is
what I'm trying to get out of with transactions.

> 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.

	That's the most commonly-suggested solution, certainly.

	-Max
-- 
http://www.everythingsolved.com/
Competent, Friendly Bugzilla Services. And Everything Else, too.