I have a program, that needs to search for a value within any of 24 fields of the same type. They are all string fields and the query looks like this:
SELECT distinct Product.sys_pro_id FROM Product
INNER JOIN Barcode ON Product.sys_pro_id = Barcode.sys_id
LEFT OUTER JOIN Product_fin ON Product.sys_pro_id = Product_fin.sys_pro_id
LEFT OUTER JOIN Owner ON Product.sys_who_id = Owner.sys_who_id
LEFT OUTER JOIN Where_sql ON Product.sys_whr_id = Where_sql.sys_whr_id
LEFT OUTER JOIN Supplier ON Product.sys_sup_id = Supplier.sys_sup_id
LEFT OUTER JOIN Model ON Product.sys_mod_id = Model.sys_mod_id
LEFT OUTER JOIN Manuf ON Product.sys_mfr_id = Manuf.sys_mfr_id
LEFT OUTER JOIN Dep_rules ON Product.sys_dep_id = Dep_rules.sys_dep_id
WHERE (Barcode.type = 'P')
AND ((Product.sys_pro_id LIKE '%100%')
OR (Barcode.barcode LIKE '%100%')
OR (Product.old_asset LIKE '%100%')
OR (Product.desc_short LIKE '%100%')
OR (Product.desc_full LIKE '%100%')
OR (Product.ser_num LIKE '%100%')
OR (Product.comments LIKE '%100%')
OR (Product.class LIKE '%100%')
OR (Product.status LIKE '%100%')
OR (Product.coll_code LIKE '%100%')
OR (Dep_rules.dep_code LIKE '%100%')
OR (Product.contract LIKE '%100%')
OR (Product.po LIKE '%100%')
OR (Product.time_stamp LIKE '%100%')
OR (Manuf.name_full LIKE '%100%')
OR (Model.name_full LIKE '%100%')
OR (Supplier.name_full LIKE '%100%')
OR (Supplier.short_code LIKE '%100%')
OR (Where_sql.name_full LIKE '%100%')
OR (Owner.name_full LIKE '%100%')
OR (Owner.name_first LIKE '%100%')
OR (Product_fin.Type1 LIKE '%100%')
OR (Product_fin.Type2 LIKE '%100%')
OR (Product_fin.Type3 LIKE '%100%')
OR (Product_fin.Type4 LIKE '%100%')
OR (Product_fin.Type5 LIKE '%100%')
OR (Product_fin.Type6 LIKE '%100%'))
ORDER BY product.sys_pro_id DESC
Is there a simpler way to get the same result. I was thinking of something alone the lines of
SELECT distinct Product.sys_pro_id FROM Product
INNER JOIN Barcode ON Product.sys_pro_id = Barcode.sys_id
LEFT OUTER JOIN Product_fin ON Product.sys_pro_id = Product_fin.sys_pro_id
LEFT OUTER JOIN Owner ON Product.sys_who_id = Owner.sys_who_id
LEFT OUTER JOIN Where_sql ON Product.sys_whr_id = Where_sql.sys_whr_id
LEFT OUTER JOIN Supplier ON Product.sys_sup_id = Supplier.sys_sup_id
LEFT OUTER JOIN Model ON Product.sys_mod_id = Model.sys_mod_id
LEFT OUTER JOIN Manuf ON Product.sys_mfr_id = Manuf.sys_mfr_id
LEFT OUTER JOIN Dep_rules ON Product.sys_dep_id = Dep_rules.sys_dep_id
WHERE (Barcode.type = 'P')
AND (Barcode.barcode or Product.sys_pro_id or Product.old_asset or Product.desc_short or Product.desc_full or Product.ser_num or Product.comments or Product.class or Product.status or Product.coll_code or Dep_rules.dep_code or Product.contract or Product.po or Product.time_stamp or Manuf.name_full or Model.name_full or Supplier.name_full or Supplier.short_code or Where_sql.name_full or Owner.name_full or Owner.name_first or Product_fin.Type1 or Product_fin.Type2 or Product_fin.Type3 or Product_fin.Type4 or Product_fin.Type5 or Product_fin.Type6 like '%100%' )
ORDER BY product.sys_pro_id DESC
Also are there any tips for speeding this query up, or is it always going to be relatively slow with so many likes?
Thanks, Colin