Geek asks and answers

Multi Column Search

I'm currently working on a real-time search, and I need to find the parts of the name in two columns (we need to separate the first and last name). I personally would like to keep the team short, however the only way I could get this to work was to:

User Search

John Doe

Generated SQL query

SELECT * FROM users WHERE
(first_name LIKE '%john%' OR last_name LIKE '%john%') AND
(last_name LIKE '%doe%' OR last_name LIKE '%doe%');

The search box is a single box, so I am doing some simple separation by space. In most cases, this will not exceed three sets of sentences, just wondering if there is a better way to do this.

Note: I would like to be able to perform partial matching. So I should be able to find John Doe if I search for "John Doe"

, , , , . , , :

SELECT * FROM users WHERE
(MATCH(first,last) AGAINST ('+john* +do*' IN BOOLEAN MODE))
+2
1

FULLTEXT,

1)

echo "a" > /var/lib/mysql/stopwords.txt
echo "an" → /var/lib/mysql/stopwords.txt
echo "the" → /var/lib/mysql/stopwords.txt

2) /etc/my.cnf

ft_min_word_len = 2
ft_stopword_file =//Library/MySQL/stopwords.txt

3) FULLTEXT

ALTER TABLE ADD FULLTEXT first_last_name_index (, );

4) MATCH

- :

SELECT * FROM users WHERE (MATCH (, ) ('John' IN BOOLEAN MODE)) (MATCH (, ) ('Doe' IN BOOLEAN MODE))

, FULLTEXT

+5

More articles:


All Articles