I'm trying to build a query to search against a fulltext index in mysql

Question

The table's schema is pretty simple. I have a child table that stores a customer's information like address and phone number. The columns are user_id, fieldname, fieldvalue and fieldname. So each row will hold one item like phone number, address or email. This is to allow an unlimited number of each type of information for each customer.

The people on the phones need to look up these customers quickly as they call into our call center. I have experimented with using LIKE% and I'm working with a FULLTEXT index now. My queries work, but I want to make them more useful because if someone searches for a telephone area code like 805 that will bring up many people, and then they add the name Bill to narrow it down, '805 Bill'. It will show EVERY customer that has 805 OR Bill. I want it to do AND searches across multiple rows within each customer.

Currently I'm using the query below to grab the user_ids and later I do another query to fetch all the details for each user to build their complete record.

SELECT DISTINCT `user_id` FROM `user_details` WHERE MATCH (`fieldvalue`) AGAINST ('805 Bill')

Again, I want to do the above query against groups of rows that belong to a single user, but those users have to match the search keywords. What should I do?

Answer 1

Thoughts: can you have two FULLTEXT indexes and do WHERE MATCH(blah) AND MATCH(blah)?

Of course, you'd store area codes in a separate field.

MySQL also supports subselects, so you don't have to make two queries to get the result.

Answer 2

Have you tried using BOOLEAN MODE search queries?

SELECT `user_id` FROM `user_details` 
WHERE MATCH (`fieldvalue`) AGAINST ('+805 +Bill' IN BOOLEAN MODE)

It means you have to manipulate the search query that is entered by the user. You can't just pass it into your query verbatim; you have to add + characters and so on.

---

Re your comment: Any SQL predicate evaluates in the context of only one row at a time. The way to compare rows is to use a self-join:

SELECT `user_id` FROM `user_details` u1 JOIN `user_details` u2 USING (`user_id`)
WHERE MATCH (u1.`fieldvalue`) AGAINST ('805 Bill' IN BOOLEAN MODE)
  AND MATCH (u2.`fieldvalue`) AGAINST ('805 Bill' IN BOOLEAN MODE)

PS: I removed DISTINCT because it's a no-op in this query and can only reduce performance.