CODEWITHSUNDEEP
mysqlsqlperformancesearch
Gabrie
Gabrie

Reputation: 579

Need tip on searching through multiple fields [mysql optimization]

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

Upvotes: 1

Views: 204

Answers (3)

Simon
Simon

Reputation: 326

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)

Upvotes: 1

Deepak Rai
Deepak Rai

Reputation: 2203

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

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

Upvotes: 1

Baconator507
Baconator507

Reputation: 1757

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');

Upvotes: 0

Related Questions