If you have a SQL like this:

select * from job where po_num like "%123%" or order_num like "%123%"

is it optimized (or, is there a better way)? My point is, I have a searh form where users can search for a string sequence in the PO_num, or the order_num, or the desciption of any line item on the order, and it runs kinda slow, and I was just wondering if the above query method is the fastest way or if there is a better way to do this kind of search.

Basically, I have to allow subtring searches on many different fields, and I am looking for any match.

In case you are interested, here is the actual code:



*--- Trim inputs -----
cJob_num=alltrim(upper(thisform.job_num))
cPO_num=alltrim(upper(thisform.po_num))
cDesc=alltrim(upper(thisform.srchtext))
cVendor=alltrim(upper(thisform.vend_num))
cCustomer=alltrim(upper(thisform.cust_num))

do case
case thisform.optionStatus.value=1
cStatus='A'
case thisform.optionStatus.value=2
cStatus='C'
case thisform.optionStatus.value=3
cStatus=''
endcase

do case
case thisform.optOrder.value=1
cOrder="Vendor,Order_Date"
case thisform.optOrder.value=2
cOrder="Order_Date,Vendor"
case thisform.optOrder.value=3
cOrder="PO,Order_Date"
case thisform.optOrder.value=4
cOrder="Cust_num,Order_Date"
endcase


lcSQL="SELECT mat_code as Job_Num,"+;
"mat_po as PO,"+;
"company as Company,"+;
"mat_qty as Qty,"+;
"mat_des1 as Desc,"+;
"material.mat_cost as Cost,"+;
"material.mat_cost*mat_qty AS Total,"+;
"ord_date as Order_Date,"+;
"mat_inv as Invoice,"+;
"mat_quote as Quote,"+;
"cust_num as Cust_Num,"+;
"status as Status,"+;
"vendor "+;
"FROM material, vendor, job_info, Purchase "+;
"WHERE Purchase.vendor=vendor.vendno"+;
" and material.mat_po=Purchase.po_num"+;
" and material.mat_code=job_info.job_num "+;
"HAVING "

lcAnd = ""

if not empty(cJob_num)
lcSQL = lcSQL + lcAnd + "job_num like '%" + cJob_num + "%' "
lcAnd = "AND "
endif
if not empty(cPO_num)
lcSQL = lcSQL + lcAnd + "po_num like '%" + cPO_num + "%' "
lcAnd = "AND "
endif
if not empty(cDesc)
lcSQL = lcSQL + lcAnd+ "desc like '%" + cDesc + "%' "
lcAnd = "AND "
endif
if not empty(cVendor)
lcSQL = lcSQL + lcAnd + "Vendor = '" + cVendor + "'"
lcAnd = "AND "
endif
if not empty(cCustomer)
lcSQL = lcSQL + lcAnd + "Cust_num = '" + cCustomer + "'"
lcAnd = "AND "
endif
if not empty(cStatus)
lcSQL = lcSQL + lcAnd + "Status = '" + cStatus + "'"
endif

lcSQL = lcSQL + " INTO cursor mat_srch ORDER by " + cOrder


&lcSQL