1
SELECT * FROM products WHERE
serial LIKE '{$ssz}'             //exact match, no other serials in row
OR serial LIKE '%,{$ssz}'        //match at list end
OR serial LIKE '%,{$ssz},%'      //match between other two serials
OR serial LIKE '{$ssz},%'        //match at list beginning

This is my perfectly working query, where {$ssz} is a PHP variable to search for. serial TEXT columns contain a list of serial numbers, separated with comma.

The serials are unique, but variable length, so "AAB001" and "AB001" are possible.

Maybe it would be faster with regex? Or with a totally different approach?

Viktor Koncsek
  • 564
  • 3
  • 13
  • 1
    For the first one, "exact match" you could replace it with a simple "=" For the other, you could try with a regex and see the result. But the best way for optimization would be extract the serial in another table. At the end, you will always have problem with this kind of implementation. – Pierre Bellan Nov 14 '18 at 15:51
  • Possible duplicate of [Where value in column containing comma delimited values](https://stackoverflow.com/questions/5611715/where-value-in-column-containing-comma-delimited-values) – rghome Nov 14 '18 at 16:41
  • This gets asked time and time again. The best solution is to normalise your database properly and store each serial number in its own column. YOu can then put an index on it. – rghome Nov 14 '18 at 16:42

1 Answers1

3

You can shorten it to:

SELECT p.*
FROM products p
WHERE FIND_IN_SET('{$ssz}', serial) > 0;

This is nicer to look at and easier to code. But it is not substantially faster.

What is the issue? Your data model is the issue. You should not be storing lists of things in a delimited string. SQL has this really great way to store lists. It is called a table not a string.

You need a junction/association table for your data. Then you can speed the query using appropriate indexes.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786