Searching rows in mysql

Question

I'm just learning to set up searchable tables, I apologize for any obtuse questions in advance. I've set up a table that will allow me to post messages to, seems to be working fine. I need to be able to search a particular column in the table in order to determine if a message is supposed to show up in a particular user's feed. This is my show create;

CREATE TABLE `feed` (
  `messageid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `userid` text,
  `contactid` text,
  `subject` text,
  `message` text,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `flag` int(2) NOT NULL,
  `state` int(2) NOT NULL,
  `trash` int(2) NOT NULL,
  PRIMARY KEY (`messageid`),
  FULLTEXT KEY `contactid` (`contactid`),
  FULLTEXT KEY `userid` (`userid`),
  FULLTEXT KEY `message` (`message`),
  FULLTEXT KEY `subject` (`subject`)
) ENGINE=MyISAM AUTO_INCREMENT=41 DEFAULT CHARSET=latin1

I believe the table type is set properly (MyISAM) and that any fields that I would want to be searchable have been set appropriately to text. Here is the full content of the table;

+-----------+--------+-----------+-----------------------------------------+--------------------------------------------+
| messageid | userid | contactid | subject                                 | message                                    |
+-----------+--------+-----------+-----------------------------------------+--------------------------------------------+
|        40 | 67     | 63 66 65  | Another test with apostraphes ''''''    | ''' '''' ,,,, ''' ,,,' '''' test test test |
|        39 | 67     | 63        | Here's a test (with apostraphes '''''') | '''''' test test test '''''                |
+-----------+--------+-----------+-----------------------------------------+--------------------------------------------+

So, my thinking is to search the contactid column for a user's userid. If it shows up, the message will show up in the user's feed. But, when I do a search, nothing shows up;

mysql> select * from feed where match(contactid) against(63);
Empty set (0.00 sec)

Can someone help me figure out where I'm going wrong?

Answer 1

It looks like userid represents the message creator, and the collection of contactid's respresents the set of users who will be able to see the message.

I would suggest splitting feed into two tables, one with messages and another with a many-to-many relationship of messageid to contactid rather than including the column containing a list of contactids. This way you will be able to join the two tables to create a query to retrieve messages viewable by a particular user using a simple = rather than a full text search.

Here's an example:

messages
+-----------+--------+------------------+----------------+
| messageid | userid | subject          | message        |
+-----------+--------+------------------+----------------+
|        40 | 67     | Another test     | test test test |
|        39 | 67     | Here's a test    | test test test |
+-----------+--------+------------------+----------------+

message_contacts
+-----------+-----------+
| messageid | contactid |
+-----------+-----------+
|    40     |    63     |
|    40     |    66     |
|    40     |    65     |
|    39     |    63     |
+-----------+-----------+

SELECT messages.* FROM messages
INNER JOIN message_contacts ON messages.messageid = message_contacts.messageid
WHERE message_contacts.contactid = 63

Storing your data this way can help you avoid other problems as well. You can read more about that in one of my favorite answers: Is storing a delimited list in a database column really that bad?

Answer 2

MATCH() AGAINST() is used for what is called a "full text search", google it for more information, there's already enough information out there.

If you are matching column (aka field) against value, you would generally use an operator to tell mysql, what field to match against your value, and how to match it.

In your example, you should use the equals operator like so:

mysql> select * from feed where contactid=2

Lots of operators exist, which will tell mysql to do different lookups (i.e. the > greater than operator, would tell mysql to get all records with a contactid greater than 2, in your example).

Edit: MySQL doesn't provide string splitting functions, since you would normally be expected to split this data up over multiple tables and use relationships. The best you can get via a "non-hacky" approach is to use FIND_IN_SET, but that would require your contactids to be stored as CSV.

Try this:

mysql > select * from feed WHERE TRIM(contactid) = '2' OR contactid LIKE '% 2 %' OR contactid LIKE '2 %' OR contactid LIKE '% 2';