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

Edit: This is done on a percona server.

2

I would use LIKE for one very good reason. From the docco here - "This function 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.

  • 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 – Jamil Seaidoun Oct 23 '14 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. – Kamil Gosciminski Oct 23 '14 at 8:26
  • @Vérace, What do you mean when you say "SET datatype"? – Pacerier Apr 18 '15 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 '15 at 0:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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