Dionysus
asked on
Troube with MySQL query between 2 columns using LIKE
I have a query to make that compares one field against the other.. basically a table with a list of printers and another table with a list of compatible ink cartridges.
The field "printerlist" contains a comma-separated string of different printer id numbers for each ink product.
I'd like to do more than query "where printerlist = printerid". I also want to include possible printers that are in the middle and end of this string, as in the following example.
Ex. 1
printerlist contains 23,245,337
I want the match to occur on 23 OR 245 OR 337 but not 45 or 37.
Therefore I search for products with only 1 printer listed ie. 23
- or products in the middle of the string with a comma preceding and following ie. ,245,
- or product at the end of the string, with a comma preceding ie. ,337
SELECT * FROM printers RIGHT JOIN products ON (printerlist = printerid OR printerlist LIKE (',' + printerid + ',') OR printerlist LIKE (',' + printerid))
Now this works, but it's slow. And despite all the searching on the net, no one really talks aobut using "LIKE" between columns.. only matching a column to a string.
Is there a faster/better way of making this query?
The field "printerlist" contains a comma-separated string of different printer id numbers for each ink product.
I'd like to do more than query "where printerlist = printerid". I also want to include possible printers that are in the middle and end of this string, as in the following example.
Ex. 1
printerlist contains 23,245,337
I want the match to occur on 23 OR 245 OR 337 but not 45 or 37.
Therefore I search for products with only 1 printer listed ie. 23
- or products in the middle of the string with a comma preceding and following ie. ,245,
- or product at the end of the string, with a comma preceding ie. ,337
SELECT * FROM printers RIGHT JOIN products ON (printerlist = printerid OR printerlist LIKE (',' + printerid + ',') OR printerlist LIKE (',' + printerid))
Now this works, but it's slow. And despite all the searching on the net, no one really talks aobut using "LIKE" between columns.. only matching a column to a string.
Is there a faster/better way of making this query?
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
OK.. I'm confused.. but I appreciate your quick response. :)
You seem to be proposing 2 solutions.
First, the alternate syntax - using FIND_IN_SET. Would using this syntax alone be faster than my current query?
Second, adding a new table, while probably the best way, would be too much work and require re-working other pages and so I'll hold off on this as a last resort.
Please clarify. Thanks!
You seem to be proposing 2 solutions.
First, the alternate syntax - using FIND_IN_SET. Would using this syntax alone be faster than my current query?
Second, adding a new table, while probably the best way, would be too much work and require re-working other pages and so I'll hold off on this as a last resort.
Please clarify. Thanks!
ASKER
OK.. using FIND_IN_SET seems a bit faster already.. I think I can live with that...
If you have any other suggestions.. I'm listening.. otherwise.. you have solved my problem. :)
If you have any other suggestions.. I'm listening.. otherwise.. you have solved my problem. :)
>You seem to be proposing 2 solutions.
yes, although the "would be too much work and require re-working other pages" is actually wrong, if code is not too "spaghetti-code" like.
in short, any update/insert of that field will be replaced by a second insert/update statement (to be build very alike the current code).
just make the test about the performance difference, and if your site will be used "alot", you HAVE to go that way.
yes, although the "would be too much work and require re-working other pages" is actually wrong, if code is not too "spaghetti-code" like.
in short, any update/insert of that field will be replaced by a second insert/update statement (to be build very alike the current code).
just make the test about the performance difference, and if your site will be used "alot", you HAVE to go that way.
ASKER
I'll look into this and take it under consideration.
I appreciate your help.
Thanks again!
I appreciate your help.
Thanks again!
ASKER
What I have now :
SELECT DISTINCTROW printerid, printername FROM printers RIGHT JOIN products ON (printerlist = printerid OR printerlist LIKE (',' + printerid + ',') OR printerlist LIKE (',' + printerid))
Any ideas please?