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?

Any reason you're using text fields to store numeric user IDs? Typically, one would use a numeric field type instead. That allows you to use regular indexes, rather than fulltext. There is absolutely no benefit to using a text field to store all numeric data, and there are several downsides. I suggest you rethink your schema. — Chris Baker, Sep 09 '13 at 19:19

Lee · Answer 1 · 2013-09-09T19:37:11.453

1

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';

edited Sep 09 '13 at 19:37

answered Sep 09 '13 at 19:16

Lee

10,496
4
37
45

Your solution would not solve this particular problem. Contactid contains 4 contacts (63, 66, 65, and 63). Doing a contactid=63 would work because it matches against the very first value, but contactid=66 would not work. So, I guess my question is; is there an operator that says contactid "contains" ? – Kimomaru Sep 09 '13 at 19:23
The way you have set up the table and relations is completely wrong from a db design point of view, so it's hard to work out a best solution based on what you have. But for the sake of giving you an answer, see my edit. It should cover all possible locations of the id i think – Lee Sep 09 '13 at 19:38
Lee, thank you! I agree with both of your comments, the tables could be arranged better, but the statement in your proposed solution works as well! Thank you, Lee. – Kimomaru Sep 09 '13 at 19:55

Don't Panic · Accepted Answer · 2018-03-19T16:56:46.107

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?

Searching rows in mysql

2 Answers2