Modified string search in Mysql Table

Question

I have a string query to search (assuming "this is my first query" as an example).

And I am also having a table which consists of (id, title, rating, ... ) attributes.

What I want is to find out the search results of my string query which match with "title" attribute (probable or exact both).

But what if complete text i.e. "this is my first query" is not there then there will be no results if I do like

SELECT * FROM test WHERE title LIKE '%$query%';

What I am trying to think next is to fire another query with lesser character this time.. i.e. I will fire the same query using search string "this is my first quer" (y is truncated) and so on until I get my desired no. of results.

But my problem is:

This is very costly operation
I want search data to be sorted in order of descending value of rating ( another field in "test" table)

Please help me out how can I proceed with this ?

Andrej · Accepted Answer · 2011-08-22 17:36:11Z

1

Add index on this field and run query in loop. I don't like it.
Use fulltext searching http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.htm in mysql.
Use standalone searching server like Sphinx http://sphinxsearch.com/

answered Aug 22, 2011 at 17:36

Andrej

7,5041 gold badge22 silver badges22 bronze badges

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

1 Comment

Kevindra Over a year ago

as I am running my website on cpanel rightnow.. I cant use standalone searching servers.. But Mysql Fulltext search seems exactly what I wanted. :)

Collectives™ on Stack Overflow

Modified string search in Mysql Table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related