Need tip on searching through multiple fields [mysql optimization]

Question

I have a table with products and each product has: brand, collection, name. I want users to search through the products but I would like to search all three fields for those searchwords because the user probably won't always know if their searchword is for brand, collection or name. A user can easily be searching for 'searchname searchbrand' and then I would like to return the records that have brand as searchname and searchbrand but also the names that contain anything like searchname or searchbrand.

I can only come up with are queries that I doubt will perform at all. What would be best to address this? Create an extra field in the table in which I combine brand, collection and name to one field and then search through that? Or create new table with only one field that is a combination of brand, collection and name and search through that?

Gabrie

Simon · Accepted Answer · 2013-10-06 09:40:57Z

1

It looks like what you need is a full text search: http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

SELECT * FROM products WHERE MATCH (brand, collection, name) AGAINST ('+searchname +searchbrand' IN BOOLEAN MODE)

For a better performance, you still could create a separate table with one field containing a combination of brand, collection and name (don't forget the full text index).

SELECT * FROM products_search WHERE MATCH (combination) AGAINST ('+searchname +searchbrand' IN BOOLEAN MODE)

edited Oct 6, 2013 at 9:40

answered Oct 6, 2013 at 9:31

Simon

3261 silver badge13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gabrie Over a year ago

sigh was it that simple? Thank you, going to try this.

Gabrie Over a year ago

Decided to go for your 2nd suggestion and create a separate table with just one field and full text index. Thanks

Deepak Rai · Accepted Answer · 2013-10-06 09:34:36Z

1

MySQL provides FULLTEXT, please refer to this link to know more on this

http://www.petefreitag.com/item/477.c

answered Oct 6, 2013 at 9:34

Deepak Rai

2,2113 gold badges24 silver badges39 bronze badges

Comments

Baconator507 · Accepted Answer · 2013-10-06 09:34:01Z

I think you should look into full text search.

In postgresql, what you would do is create a column of type tsvector which would consist of broken down version of the other columns. It will take care of stemming the word.

alter table TABLE add column tsv_description tsvector;

Then you need to populate it.

UPDATE TABLE SET tsv_description =      (to_tsvector('pg_catalog.english',coalesce(searchname, '')) ||      (to_tsvector('pg_catalog.english',coalesce(searchbrand, '')) ||      to_tsvector('pg_catalog.english',coalesce(othercol, ''));

Then you can query using to_tsquery like so:

select * from TABLE where tsvdescription @@ plainto_tsquery('pg_catalog.english','my super full text query');

You can also convert the columns into tsvectors on the fly but its slower.

select * from TABLE where to_tsvector(searchname) || to_tsvector(searcbrand) @@ plainto_tsquery('pg_catalog.english','tocino');

Collectives™ on Stack Overflow

Need tip on searching through multiple fields [mysql optimization]

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related