MYSQL search for specific word or number in field excluding words or numbers containing those

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.

Solution

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.