Rev cgi & mySQL (again)

Bill Marriott wjm at wjm.org
Fri Jan 2 12:32:14 EST 2009


Hi jbv,

I am less of an expert with SQL than many, but I think you can simplify your 
query a bit using the MATCH...AGAINST keywords:

for example, the first part (b) of your query might look like:

-- convert myList1 to a space-delimited items

SELECT Col10, Col11, Col12, Col13, Col14, Col15
FROM myTable WHERE
MATCH (Col1,Col2) AGAINST ('{myList1}')"

notice that I use single quotes to enclose strings; you shouldn't need your 
" &q& " business cluttering things up.

I'm not sure I can figure out the second (a) part of the query, like what is 
the %/ construction? Is that a special escape character, or part of the 
literal text you're looking for? A fully-constructed query example would be 
easier to wrap my head around and simplify.

Nevertheless, I'm pretty sure the performance hit is in the data transfer 
from SQL to Rev, so keeping that as small as possible should speed things up 
significantly. I know the tendency is to suck as much data as possible into 
Rev, where you can manipulate it with more familiar tools... but SQL is 
designed for transactions. Trust that it will handle well-formed queries 
quickly, and focus Rev on presentation, not massaging/selecting/sorting 
data.

- Bill 






More information about the Use-livecode mailing list