Re[2]: MySQL Techniques

<Mike>
> In ASP, we can use the GetRows method to store the query results into a
> two-dimensional array.  Is there anything analogous to that in PHP?  If so,
> perhaps that would help Nathan's issue here for subsequent queries (does
> nothing for the initial query, though).

I've tried this sort of thing as an alternate solution, and it got
kind of messy. Mostly because the arrays are sometimes quite huge, and
then breaking up the results across pages would require sending the
entire array(s) or writing to a file, either of which present problems
of their own that seemed to negate the advantages.

> Also, what kind of connection is made to the MySQL database?  Is it ODBC or
> something else?  When we  use ODBC, we find extremely improved query speed
> results when we have the ForwardOnly, ReadOnly cursortypes initiated.  Using
> other cursortypes can cause the record to be locked, disallowing anyone else
> from querying and basically queuing them up for a chance to get at the same
> results.

$link = mysql_connect("localhost","username","password") or die("...majorly muffed up.");
mysql_select_db("cd") or die("...majorly muffed up.");

That's the connection from a script on the same server. Locking isn't
an issue for us, because there are only ever going to be one or two
people using this thing at a time. The timing of a search becomes an
issue because often they are looking for something fast before the
next break on their radio air shift. :)

> Finally, what happens when inner joins are used in the query instead of the
> query Nathan previously posted?  It would seem the use of a couple nested
> inner joins in conjunction with match/against may improve results.  I noted
> that his three tables all shared the "MasterID" so they could be joined on
> that.

That is the way I've attempted to join the tables, but one table
always ends up being rather large and so becomes the bottleneck.

I've actually not looked at this project for a few weeks now (holidays
and other project demands clamoring for attention) so I've gotta jump
back in. I keep hoping it'll all "click on" one of these times. <grin>

~ Nathan Lyle
   __  __   ____   _       __     __      __  ___                __           
  / / / /  / __ \ | |     / /__  / /_    /  |/  /___ ____  _____/ /__________ 
 / / / /  / /_/ / | | /| / / _ \/ __ \  / /|_/ / __ `/ _ \/ ___/ __/ ___/ __ \
/ /_/ /_ / ____/  | |/ |/ /  __/ /_/ / / /  / / /_/ /  __(__  ) /_/ /  / /_/ /
\____/(_)_/ (_)   |__/|__/\___/_.___/ /_/  /_/\__,_/\___/____/\__/_/   \____/ 

     www.upwebmaestro.com     nathan(at)upwebmaestro.com     (906)485-4806

"Where a calculator on the ENIAC is equipped with 18,000 vacuum 
 tubes and weighs 30 tons, computers of the future may have only 
 1,000 vacuum tubes and perhaps weigh 1.5 tons." - Popular Mechanics, March 1949.

HWG hwg-techniques mailing list archives, maintained by Webmasters @ IWA