I am searching welds.welder_id and welds.bal_welder_id which are lists of unique welder IDs separated by spaces by the users.
The record set looks like 99,199,99 w259,w259 259 5-a
99,199,259,5-a and w259 are unique welder id numbers
I cannot use the MYSQL INSTR() function by itself as a search for "99" will pull up records with "199"
Users on each project format their welder IDs a different way (000,a000,0aa) usually to match their customer's records.
I really want to avoid using PHP code for a number of reasons.
To select records with "w259" in the welder_id OR in the bal_welder_id columns, my query looks like this.
SELECT * FROM `welds`
WHERE `omit`=0
AND( (`welder_id`='w259' OR `bal_welder_id`='w259')
OR (`welder_id` LIKE 'w259 %' OR `bal_welder_id` LIKE 'w259 %')
OR (`welder_id` LIKE '% w259' OR `bal_welder_id` LIKE '% w259')
OR (INSTR(`welder_id`, ' w259 ') > 0 OR INSTR(`bal_welder_id`,' w259 ') > 0))
ORDER BY `date_welded` DESC
LIMIT 100;
It works but it takes 0.0030 seconds with 1300 test records on my workstation's SSD.
The actual DB will have hundreds of thousands after a year or two.
Is there a better way?
Thanks.
If I understand your question correctly, one option is to use FIND_IN_SET(str, strlist) string function, which returns the position of the string str in the comma separated string list strlist, for example:
SELECT FIND_IN_SET('b','a,b,c,d');
will return 2. Since your string is not separated by commas, but by spaces, you could use REPLACE() to replace spaces with commas. Your query can be like this:
SELECT * FROM `welds`
WHERE
`omit`=0
AND
(FIND_IN_SET('w259', REPLACE(welder_id, ' ', ','))>0
OR
FIND_IN_SET('w259', REPLACE(bal_welder_id, ' ', ','))>0)
The optimizer however cannot to much, since FIND_IN_SET cannot make use of an index, if present. I would suggest you to normalize your table, if it is possible.