Rev cgi & mySQL (again)

jbv jbv.silences at club-internet.fr
Fri Jan 2 14:22:34 EST 2009



Bill,

> 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.

ok, I'll try that...

>
>
> 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.

the "/" is actually used as a separator in some columns of the table...

>
>
> 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.
>

I too am less than an expert, but I tend to disagree with you on that issue :
acccording to my experience, splitting "large" queries into a set of "smaller"
ones
doesn't help much as for speed of data transfer between mySQL and Rev : what
you gain by returning small chunks of data is almost always lost by the
multiplication
of queries...
When one needs to grab large amounts of data from a DB, a good solution is to
use the "SELECT ... INTO OUTFILE..." construct to export data as a text file
that can
be then opened in Rev. Rather ugly, I agree, but much faster...

Last but not least, in my examples, I don't think the performance difference is
in the
data transfer. Actually, the fact that the boolean part of the query can
feature up to 50
elements doesn't imply that the amount of data returned will be huge...
Furthermore,
when I send the same query from PHPmyAdmin (adding "LIMIT 1, 10" for instance),

or when I use "SELECT COUNT(*)" instead of "SELECT Col1, Col2 etc" (which
returns
only 1 integer) I get similar processing times in mySQL...
I'd like to add also that my table includes fulltext indexes on every
searchable column, which
imho should speed up things in some way... it actually does, coz when I delete
those indexes,
processing time of the query is significantly longer...

Please correct me if I'm wrong, but even if I have no clue on how mySQL
processes queries,
imho there is no big difference between the structure of the SQL query and my
Rev code...
The only difference being the content of the table already available as a text
file when the
Rev code starts... This file text is generated once a day (when the table is
updated, so no need
to generate it each time the script runs), and it takes only 300 / 320 msec to
generate it,
which is less that 20% of the performance difference between both techniques...

So, unless proven wrong, I feel like sticking to my first conclusion that
Transcript is faster than
SQL (if that makes any sense)...

Best,
JB




More information about the use-livecode mailing list