Searching through a comma-separated field

Sample Table

CREATE TABLE 
   `foo` (
        `id` INT NOT NULL AUTO_INCREMENT ,     
        `keyword_ids` VARCHAR(128) ,     
        PRIMARY KEY (`id`)  
   );

Sample Data

INSERT INTO 
 开发者_开发知识库   `foo`
SET
    `keyword_ids` = '14,10,5,19,12'

Sample Query

SELECT 
    * 
FROM 
    `foo` 
WHERE 
    (`keyword_ids` LIKE '5,%%' 
    OR 
    `keyword_ids` LIKE '%%,5' 
    OR 
    `keyword_ids` = '5' 
    OR 
    `keyword_ids` LIKE '%%,5,%%')

As per my most recent question, this works just fine but is there a way I can improve it?

SELECT  *
FROM    foo
WHERE   FIND_IN_SET(5, keyword_ids)

Note, however, that this expression is still not sargable, that means an index on keyword_ids cannot improve this query.

You should normalize your model if you want this to be searchable fast.

Another option is to store comma-separated keywords (rather than ids) in the table and create a FULLTEXT index on them:

CREATE FULLTEXT INDEX fx_foo_keyword ON (keywords)

SELECT  *
FROM    foo
WHERE   MATCH(keywords) AGAINST ('+keyword5' IN BOOLEAN MODE)

This, however, would only work on a MyISAM table.

Take a look at the FIND_IN_SET function.