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

views:

answers:

+2 Q:

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

Hi there!

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?

+2 A:

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.

Pekka 2010-08-12 10:43:19

Is that applicable to multiple tables? I mean, 5 or 6 fields are in the same table, but then there's a couple of fields taken from external tables, and I need to look for in that fields as well.Thanks!

Arthur 2010-08-12 12:27:08

@Arthur nope, I don't think you can make an index expand across multiple tables.

Pekka 2010-08-12 12:29:02

@Pekka Ops, sorry to hear that. Should I go back to OR %like%?I'm very discouraged about this...

Arthur 2010-08-12 12:32:00

@Arthur why not put fulltext indexes on every table, and stick with `match against`? It's bound to be better than `LIKE` in any case.

Pekka 2010-08-12 12:35:01

@Pekka Because just a few of those columns are var/char/text... what about a index wrapping all the searcheable fields in the table?

Arthur 2010-08-12 12:41:25

ansaurus

tags:

views:

answers:

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

related questions