1

Is there a difference in behavior or performance in choosing FIND_IN_SET over LIKE for a operation that looks like:

SELECT * FROM my_table WHERE column_1 LIKE '%string%';

vs

SELECT * FROM my_table WHERE FIND_IN_SET('string', column_1) > 0;

This is done on Percona Server.

1

1 Answer 1

2

I would use LIKE for one very good reason. From the documentation here - "This function (FIND_IN_SET) does not work properly if the first argument contains a comma (“,”) character".

You could always use an EXPLAIN to check on performance - but simply for the reason above, I would not use it.

[EDIT]

You could always use LOCATE(). I strongly recommend that you don't use MySQL's SET datatype. It's non-standard and illogical to say the least.

4
  • I can guarantee the substring i would be searching for would not have a comma. I remember looking at that doc but it didnt really provide any information to lean one way or the other. Thanks for the help, its much appreciated Oct 23, 2014 at 8:09
  • @JamilSeaidoun I see that docs actually provide a lot of information to deal with. Do you need to locate where 'string' is in your set? I don't think that's your expected output, therefore LIKE would be much better. Oct 23, 2014 at 8:26
  • @Vérace, What do you mean when you say "SET datatype"?
    – Pacerier
    Apr 18, 2015 at 7:01
  • @Pacerier MySQL allows you to declare a datatype called SET (which is also an SQL keyword to add to the confusion). See here. It's a breach of Codd's second rule which says that every datum should be addressable by table name, PK and field name only - not dependent on it's position in a "SET".
    – Vérace
    Apr 20, 2015 at 0:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.