MySQL experts ... help! (alternative to FULLTEXT searching)

[MagSafe]

Hi all,

So I've been trying to do a FULLTEXT search on my website using MySQL on multiple tables (which I've been told you can't do), see here (http://forums.devshed.com/mysql-help-4/help-with-fulltext-search-across-multiple-tables-600619.html). So I was wondering if any of you SQL experts out there know of a way I can get round this?

You'll be able to see my database tables/columns in the post I've linked to above. I know you can use "LIKE", but there must be a better way to retrieve great results when searching through a MySQL database.

Really hope someone on here can help :)

Thanks!

[chucker]

Some ideas:


Search each table, then UNION the results together
Create a view that does this, then search that
For performance reasons, create a single table that you copy all relevant (to-be-searched) values to, then search that

[RobUSVI]

I switched over to using http://www.sphinxsearch.com/ because of excessive database induced lag on my site when people were searching. (Free/Opensource)

[Brad]

I switched over to using http://www.sphinxsearch.com/ because of excessive database induced lag on my site when people were searching. (Free/Opensource)

A similar powerful search application that we use at my day job is Lucene (http://lucene.apache.org/java/docs/) (free/oss Apache project).

[MagSafe]

Thanks for the suggestions :)

I was going to start using Sphinx until I realised my GoDaddy package doesn't support it. I'll hopefully be upgrading to a decent virtual server package in a few months so what I have now will have to do unfortunately.

Thanks again for the replies :)