Rev cgi & mySQL (again)

[Jan Schenkel]

--- jbv <jbv.silences at club-internet.fr> wrote:
> 
> 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
> 

Modern SQL databases use a query execution plan, which
is built as it parses your SQL query, and serves as
its tactic for interrogating the database. Depending
on your query, it may be forced to hit the entire
database table - and full word indexes aren't going to
be the fastest way to access the data.
The speed of your database is extremely dependent on
the structure of the content. Perthaps you should
split the data out over more columns and several
tables, rather than stuffing a large amount of data in
a single text column, separated by slashes.
Though it's impossible to tell for sure without seeing
the actual data structure and content, I'd say you're
asking MySQL to do something in a way that's
sub-optimal for an RDBMS but very well suited for
Rev's text munging routines.
It's all about using the right tool for the job, and
using the tool to its best abilities. In this case,
given that the data isn't updated constantly, doing it
all in Revolution looks like the ticket to a fast
solution.

Jan Schenkel.

Quartam Reports & PDF Library for Revolution
<http://www.quartam.com>

=====
"As we grow older, we grow both wiser and more foolish at the same time."  (La Rochefoucauld)