Full Text Search MySQL

Question

I Know how to use 'full text search MySQL'. What i want to know is how to search text for words . For exmaple :- We have three records in mysql as following

1.) This is a place for programmers .
2.) People visit places for travelling purposes.
3.) programmers visit this place site for coding purposes.

Now When A user searches for 'place for programmers'

Following should be the output

it should output rows that contain all words from the sentence in any order

1.)This is a place for programmers.
2.)programmers visit this place site for coding purposes.

Thank You For Helping .

You can try looking only for the first word, then look for the second in the results, and so forth. — Amirshk, Apr 25 '15 at 07:25
Could you please put your answer in form of a SQL query , that would help a lot — user3578478, Apr 25 '15 at 07:29
actually fulltext search will probably ignore the word 'for', so that wont really work. — pala_, Apr 25 '15 at 07:31
I just did experimented it with twitter and it is producing the desired results ...How do they do it? — user3578478, Apr 25 '15 at 07:34
@user3578478 Welcome to Stack Overflow. Remember to upvote answers that were helpful to you, and mark as the 'accepted answer' the one that helped you the most. — Bill Karwin, Sep 13 '16 at 19:47

score 2 · Answer 1 · edited Sep 13 '16 at 19:43

Before using the full text search, index the columns
```
ALTER TABLE <table> ADD FULLTEXT(column);
```

Verify that it already in Full Text

SELECT 
    index_name, group_concat(column_name) as columns
FROM 
    information_Schema.STATISTICS 
WHERE 
    table_schema = 'your_db_name' 
    AND table_name = 'table_name'
    AND index_type = 'FULLTEXT'
GROUP BY 
    index_name;

Then use following SQL

SELECT * 
FROM <table>
WHERE MATCH(<column>) AGAINST('+place +for +programmers' IN BOOLEAN MODE)

score 1 · Answer 2 · answered Apr 25 '15 at 07:42

1

MySQL has MATCH..AGAINST for full text searches. Try the following,

SELECT * FROM <table> WHERE MATCH(<column>) AGAINST('+place +for +programmers' IN BOOLEAN MODE)

The +'s basically mean AND (you could use - for OR)

https://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html

answered Apr 25 '15 at 07:42

dannym87

109
1
6

1

And also you have to add a full text index in the column – Parangan Apr 25 '15 at 07:50
I just tried it in mysql phpmyadmin i created a random table and populated it with the same above rows , and tried the query , But it is returning empty set of reults – user3578478 Apr 25 '15 at 07:53
What is the schema for the random table? – dannym87 Apr 25 '15 at 07:58
that wont work due to the presence of `+for`. if you want to look for the three letter words, read [this](http://stackoverflow.com/questions/13049306/fulltext-search-on-mysql-with-a-3-letter-word). also, the boolean searches are `or` by default, `+` in front of a word means `and`. `-` in front of a word means `not`, not `or` as is stated in the answer – pala_ Apr 25 '15 at 08:30
Thank You So Much , i just fixed it and now it works perfectly.. Thank You So Much!!!! – user3578478 Apr 25 '15 at 08:53

Full Text Search MySQL

2 Answers2