Matt,

There is no way in any DB that I've seen to optimize a "contains" search argument like this. However, it is possible to optimize when the wildcard is at the end of th string.

In any database, if you really do need to perform searches on the middle of a string, it's best to try and separate that out into its own field so you can do a simple match in your WHERE clause.

In VFP, one additional trick is the ability to build indexes on expressions. For example, you could build an index on RIGHT() and then ensure all of your SQL statements have a matching search argument (i.e., WHERE RIGHT() = '123').

I also noticed that you're using the HAVING clause without a GROUP BY. This is redundant, as in VFP, using HAVING without GROUP BY essentially makes the HAVING an unoptimized WHERE clause. Even worse, if this isn't run on VFP, you'd get an error for doing this, as it's not standard SQL.

Hope this helps.

>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)?
[SNIPPED]
>
>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

[snipped]