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.