I don't disagree with anything that Hank says. Just a general point though,
remember the design of an OLAP (Online analytical processing) is very
different from a Transaction database. In the later integrity of the data is
paramount, and the DB should be fully rationalized.

In the former speed of access is more important. Therefore do as few joins
as possible, and instead use as many computed tables as possible (MySQL
AFAIK does not support this, so do it yourself with scripts)

The reason of course the join on the third table runs so slow is that you
are doing  in the order of  46,734 *238,350 queries! (It's not quite this
bad, but I'm sure you get the point).

Write me privately if you want some specifics.

Frank

----- Original Message -----
From: "Hank Marquardt" <hmarq(at)yerpso.net>
To: "Nathan Lyle" <nathan(at)upwebmaestro.com>
Cc: "hwg-techniques" <hwg-techniques(at)hwg.org>
Sent: Thursday, December 05, 2002 11:53 AM
Subject: Re: MySQL Techniques


> One more thing ...
>
> If you're going to have 'paging'; show 1-10, then a previous/next
> button ... and/or expect queries to have patterns -- like lot's of
> Brittany searches when a new album comes out ... you should setup
> caching on your queries such that identical queries within a period of
> time never talk to the database, they just read from a file -- that'll
> speed up the common queries to near-instantaneous and make pagination
> within the same base query database friendly.
>
>
>
> On Wed, Dec 04, 2002 at 02:59:36PM -0500, Nathan Lyle wrote:
> > Ok... I *sort* of asked something similar a while back, but have since
> > refined my problems. (I suppose that's progress.)
> >
> > I have a database in MySQL of three large tables:
> >   master (20,938 rows)
> >   artist (46,734 rows)
> >   track (238,350 rows)
> >
> > I've reduced query times to next to instantaneous when selecting from
> > any of the tables individually. (The Match/Against function is
> > particularily quick.)
> >
> > My problem is that a query that joins any of the two tables
> > (especially the third one) slows down to the point of being not
> > practical, often not even producing results in time before the server
> > blows the whistle and provides a page unreadable error to the browser.
> > (I'm using PHP to access MySQL, BTW.)
> >
> > My question is one maybe of database design, but at least of function.
> > Is there a better way to arrange the tables that would speed things
> > up? Is there a better way of structuring queries?
> >
> > Some quick background info: the tables hold information for a CD
> > library. The master table holds things like the album title, label,
> > library catalog number, date of entry, etc. The artist holds
> > (obviously) information about artists associated with a particular
> > album, and track holds information on the tracks for each album. Both
> > artist and track hold the unique id of the master row associated with
> > them.
> >
> > Ideally, I need one query, because I want to limit the results to a
> > variable number, and sort them by one of a few methods. This seems to
> > prevent me from just loading all the initial results into PHP arrays
> > and dealing with the info there.
> >
> > Any ideas?
> >
> > ~ The U.P. Web Maestro (Nathan Lyle)
> >
> >   E-Mail: nathan(at)upwebmaestro.com
> >   Online: www.upwebmaestro.com
> >    Phone: (906)485-4806
> >
> > "Computers do not solve problems, they execute solutions." - Laurent
Gasser.
> >
>
> --
> Hank Marquardt <hank(at)yerpso.net>
> http://web.yerpso.net
> GPG Id: 2BB5E60C
> Fingerprint: D807 61BC FD18 370A AC1D  3EDF 2BF9 8A2D 2BB5 E60C
> *** Web Development: PHP, MySQL/PgSQL - Network Admin: Debian/FreeBSD
> *** PHP Instructor - Intnl. Webmasters Assn./HTML Writers Guild
> *** Beginning PHP && PHP II -- Starting March 25, 2002
> *** See /services/classes

• Web Designer Courses
• Off the Shelf Training Courses

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