342856 - Fulltext search on MySQL should optionally support Sphinx

Status: RESOLVED WONTFIX

(Bugzilla :: Database, enhancement, P3)

Description

[Gunnar Wagenknecht]

User-Agent:       Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.4) Gecko/20060508 Firefox/1.5.0.4
Build Identifier: 

See $subject. I just wanted to record this here. It might be worthwhile to investigate supporting this plug-in for full text searches in bugs and attachments.

Reproducible: Always

Comment 1

[Max Kanat-Alexander]

Could you describe the plugin a bit here and what it's advantages over traditional FULLTEXT indexes are?

Comment 2

[Gunnar Wagenknecht]

AFAIK the traditional FULLTEXT search only works on MyISAM tables. 

From http://sphinxsearch.com/features.html:
* supports MySQL natively (MyISAM and InnoDB tables are both supported)

But one requirement is that it requires MySQL 5 to work natively. So this might not be an option for Bugzilla in the near future or only as a separate Bugzilla extension.

Comment 3

[timellis]

I am tasked with owning Sphinx at Mozilla. We have some Sphinx standalones running, and there shouldn't be an issue with using Sphinx MySQL plugins.

Bugs database is now MySQL 5.0.

Comment 4

[Max Kanat-Alexander]

FWIW, dwm (the Yahoo! Bugzilla maintainer) has told me that MySQL seems to perform fine even with IN clauses that contain thousands of bug ids, so we could in fact call out to Sphinx and get a list of bug ids, and then insert that into the query for advanced search.

However, relevance sorting would be tricky.

Comment 5

[Sheeri Cabral [:sheeri]]

Since Tim Ellis is no longer here.....I guess this falls to me. :D

Right now we're in the middle of a data center move, and right after that we're going to be upgrading Bugzilla to MySQL 5.1, so the timing might be right to do after that (although I'd like to upgrade again to 5.5 after that....and I'd like to upgrade to MariaDB 5.5 which has the sphinx storage engine built in, so we should maybe revisit this then? I'm hoping this will all be done by mid-July, but I kind of have a crazy travel schedule this summer so it's possible it won't be).

Sphinx is pretty awesome.

Comment 6

[Sheeri Cabral [:sheeri]]

Is this something we still want to do? we're on MySQL 5.5 now! (and in MySQL 5.6, InnoDB supports fulltext!!!)

Comment 7

[Sheeri Cabral [:sheeri]]

:glob - do you have any idea if Bugzilla supporting FULLTEXT search in either MySQL (MyISAM or InnoDB) or sphinx plugin is in the roadmap?

Comment 8

[Robin H. Johnson [:robbat2]]

:sheeri - Did anything happen on this for the Mozilla deploy of Bugzilla? Gentoo is faced with a similar problem of needing to get out of MyISAM.

Comment 9

[Sheeri Cabral [:sheeri]]

What we did with Bugzilla was convert to InnoDB, which does full-text search in version 5.6.

Comment 10

[Robin H. Johnson [:robbat2]]

:sheeri - So 5.6 works fine? that's a perfectly viable option here then. Any extra patching needed?

Comment 11

[Sheeri Cabral [:sheeri]]

Nope; all the 5.6 queries and such should work. Of course, I recommend doing it on dev, then stage, then production. If you want to work out a time where you can have a DBA in a call or an IRC channel, just let us know! (#db on irc.mozilla.org is open to the public)

Comment 12

[Frédéric Buclin]

Shouldn't this bug be WONTFIX'ed as MySQL 5.6 supports fulltext searches with InnoDB tables? IMO, we should fix bug 868869 instead.

Comment 13

[Dave Miller [:justdave]]

I agree here.  MySQL is easy enough to obtain in packaged format on all affected platforms that a newer version of MySQL than what ships on the OS doesn't concern me much.  Let's do bug 868869 instead.