CODEWITHSUNDEEP
phpmysqlsearch
Arthur
Arthur

Reputation: 33

What's the fastest way to make a multiple field search in MySQL?

I'm trying to achieve a really fast search engine for a MySQL db with several fields.

The problem relies on the fact that I need to match a value (keyword) against several fields, in a %like% approach.

I don't mind relevancy (the order is fixed), just performance. The queries are pretty complicated (lotta JOINS, 3 or 4 tables per query).

At first I thought of MATCH ... AGAINST, but I don't think it's a good idea to make so much FULLTEXT indexes. Maybe with PHP-generated OR LIKE %value%, but... is it really efficent?

Any ideas?

Upvotes: 2

Views: 647

Answers (1)

Pekka
Pekka

Reputation: 449783

A FULLTEXT index - possibly just one spanning over all the columns you will query - is probably your very best bet. I imagine mySQL will be able to apply much better optimizations on this, and be way faster, than a plethora of OR x LIKE y statements.

Upvotes: 2

Related Questions