287169 - Need to be able to do fulltext searches on longdescs.thetext even with most tables using InnoDB

Status: RESOLVED DUPLICATE of bug 399370

(Bugzilla :: Creating/Changing Bugs, enhancement)

Description

[Max Kanat-Alexander]

We need to have a MyISAM table for fulltext searches, but InnoDB tables for
transactions.

longdescs.thetext is one of our only fulltext fields, so it needs its own table.

I was thinking that it would become comments_fulltext.comment.

It actually should be relatively easy to move, though the checksetup migration
may take a while.

Comment 1

[Frédéric Buclin]

InnoDB tables would also strongly simplify the code as we wouldn't have to worry about all tables depending on another table (and sanitycheck.cgi would become useless as cascading would do the job for us). Table locks could be removed too, IIRC, as locks are automatically done at the row level instead of at the entire table. One side-effect is that these InnoDB tables are a bit slower than MyISAM tables.

Comment 2

[Max Kanat-Alexander]

We would still have to lock certain tables, I believe, based on how MySQL's transactions work, in order to get unique IDs.

But yes, it would be nice to be able to use InnoDB tables for certain things. We should test it, though--some people complain about InnoDB tables having problems that MyISAM tables don't have.

Comment 3

[Gunnar Wagenknecht]

FWIW, there is a text-search plug-in for MySQL. Although this would be tough to integrate in a database abstract manner.

http://sphinxsearch.com/

Comment 4

[Frédéric Buclin]

Max, isn't this bug fixed by bug 399370?

Comment 5

[Max Kanat-Alexander]

Yeah, to the point that it's a total duplicate.