FullText Searching: The Dilemma

[Gervase Markham]

Bradley Baetz wrote:
> On 26/06/07, Gervase Markham <gerv at mozilla.org> wrote:
>> Are either of these facts going to change in the near future?
> 
> I believe not. Theres the new storage engine (whose name I forget),
> but I wouldn't count on that for production code for a while.

I ask only because MySQL/InnoDB engineers were mumbling in emails about 
full text searching for InnoDB as far back as early '06.

> Yes, but you get into ickyness when you try to use transactions over
> them. It means that bugzilla could never do BEGIN; try something;
> COMMIT and rely on <something> failing doing a rollback, for example.

I can't quite parse that...

>> 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?
> 
> No, because you can't do row-level locking. 

I don't quite follow. Where am I going wrong?

- Lock row 23 of InnoDB table
- Edit row 23 of MyISAM table
- Unlock row 23 of InnoDB table

InnoDB supports row level locking...

> I've implemented something
> similar in the past with GET_LOCK - you GET_LOCK("bz_bugX"). But you
> then have to have everything grab that lock, and buglist.cgi would
> have to lock the whole table since it doesn't know what rows to do in
> advance.

Why does buglist.cgi have to lock anything? All it does is display data. 
You might get some bugs being after one update, and other bugs being 
before it, but that's not the world's scariest thing.

> Anyone know how useful mysql's trigger support is for doing that
> stuff? Especially without having its next key locking get in the way?

I think I breezed past trigger-based solutions to this problem on the 
web. We're not the first people to hit it, of course :-)

> Or, of course, just recommend that large installs use postgres+tsearch? ;)

We could do that. How hard would it be to convert b.m.o.? Are there 
scripts, or do all conversions have to be done by hand? Do we know how 
MoCo IT would feel about supporting Postgres? Do we feel the Postgres 
support is stable enough? Are there other downsides?

Gerv