Comma separated values in MySQL “IN” clause

by

Building on the FIND_IN_SET() example from @Jeremy Smith, you can do it with a join so you don’t have to run a subquery.

SELECT * FROM table t
JOIN locations l ON FIND_IN_SET(t.e_ID, l.city) > 0
WHERE l.e_ID = ?

This is known to perform very poorly, since it has to do table-scans, evaluating the FIND_IN_SET() function for every combination of rows in table and locations. It cannot make use of an index, and there’s no way to improve it.

I know you said you are trying to make the best of a bad database design, but you must understand just how drastically bad this is.

Explanation: Suppose I were to ask you to look up everyone in a telephone book whose first, middle, or last initial is “J.” There’s no way the sorted order of the book helps in this case, since you have to scan every single page anyway.

The LIKE solution given by @fthiella has a similar problem with regards to performance. It cannot be indexed.

Also see my answer to Is storing a delimited list in a database column really that bad? for other pitfalls of this way of storing denormalized data.

If you can create a supplementary table to store an index, you can map the locations to each entry in the city list:

CREATE TABLE location2city (
 location INT,
 city INT,
 PRIMARY KEY (location, city)
);

Assuming you have a lookup table for all possible cities (not just those mentioned in the table) you can bear the inefficiency one time to produce the mapping:

INSERT INTO location2city (location, city)
  SELECT l.e_ID, c.e_ID FROM cities c JOIN locations l
  ON FIND_IN_SET(c.e_ID, l.city) > 0;

Now you can run a much more efficient query to find entries in your table:

SELECT * FROM location2city l
JOIN table t ON t.e_ID = l.city
WHERE l.e_ID = ?;

This can make use of an index. Now you just need to take care that any INSERT/UPDATE/DELETE of rows in locations also inserts the corresponding mapping rows in location2city.

More Related Contents:

  1. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE `id` [closed]
  2. Using PHP data in MySQL [closed]
  3. How can create Database using url or other method [duplicate]
  4. Problame with files on SQL
  5. Checking the availability of rooms
  6. Im trying to create some temporary tables in mysql using php
  7. How can I prevent SQL injection in PHP?
  8. SQL injection that gets around mysql_real_escape_string()
  9. How to include a PHP variable inside a MySQL statement
  10. How to apply bindValue method in LIMIT clause?
  11. How to properly set up a PDO connection
  12. Commands out of sync; you can’t run this command now
  13. SELECT COUNT(*) AS count – How to use this count
  14. Get table column names in MySQL?
  15. How to resolve ambiguous column names when retrieving results?
  16. Achieve hierarchy, Parent/Child Relationship in an effective and easy way
  17. Loading .sql files from within PHP
  18. selecting unique values from a column
  19. Use bound parameter multiple times
  20. How to search for slash (\) in MySQL? and why escaping (\) not required for where (=) but for Like is required?
  21. I cannot get my login form to connect interact properly with mySQL database [closed]
  22. mysql_num_rows(): supplied argument is not a valid MySQL result resource [duplicate]
  23. MySQL “NOT IN” query 3 tables
  24. Increment value in MySQL update query
  25. Create a dynamic mysql query using php variables
  26. PHP: multiple SQL queries in one mysql_query statement
  27. How to separate DATE and TIME from DATETIME in MySQL?
  28. Do SQL connections opened with PDO in PHP have to be closed
  29. How to convert ISO8601 to Date format in php
  30. Why does this return Resource id #2? [duplicate]

Leave a Comment