MySQL Search Algorithm

Question

I somewhere read earlier about this, and failed to find it this time.

I have a table with 4 columns as follows:

+----+------+-------+-------------+
| id | Name | Title | Description |
+----+------+-------+-------------+

Now I read that we can combine the Name, Title and Description in such a way that you can search later on and output will be sorted based on the text repeat.

For eg: If I search for "Who is James"

It will search for it in Name, Title and Description and will return the rows based on Existence of this string Partially or fully within the columns.

Please help!!!

har2vey · Accepted Answer · 2014-03-05 08:17:51Z

2

A better approach is to use the full text search feature from MySQL, a little example in your case would be:

//Alter the table to add full text index
ALTER TABLE user ADD FULLTEXT keyword (name, title, description)

//Perform the query like this
SELECT * FROM user MATCH (name, title, description) AGAINST 
('Who is james'  IN NATURAL LANGUAGE MODE)

For further reference please refer to the documentation:

https://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

answered Mar 5, 2014 at 8:17

har2vey

6766 silver badges19 bronze badges

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

Comments

avisheks · Accepted Answer · 2014-03-05 08:18:08Z

1

You can take advantage of MySQL MyISAM Full TEXT Searching methodology. Add the columns you want to add support full text searching like

ALTER TABLE mytable ADD FULLTEXT(Name,Title,Description);

now search any query on these columns order by best matched strings like:

        SELECT *,
            MATCH(Name,Title,Description) AGAINST('$keyword') AS score
        FROM mytable
        WHERE MATCH(Name,Title,Description) AGAINST('$keyword')
        ORDER BY score DESC

answered Mar 5, 2014 at 8:18

avisheks

1,1801 gold badge11 silver badges28 bronze badges

Comments

Amnon · Accepted Answer · 2014-03-05 07:58:43Z

0

If I understood the question correctly, the most obvious (and probably slowest) method would be to use LIKE, for example:

SELECT * FROM mytable WHERE Name LIKE '%James%' OR Title LIKE '%James%' OR Description LIKE '%James%'

answered Mar 5, 2014 at 7:58

Amnon

2,9082 gold badges24 silver badges22 bronze badges

Collectives™ on Stack Overflow

MySQL Search Algorithm

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related