Posted by Sandman on 11/24/06 11:12

In article <4566c326$0$330$e4fe514c@news.xs4all.nl>,
Erwin Moller
<since_humans_read_this_I_am_spammed_too_much@spamyourself.com>
wrote:

> >> As Jerry and Micha indicated: Do not fetch all members into PHP memory
> >> each page, just fetch the ones you need.
> >
> > Right, but how?
>
> Well, it is your platform, how can I tell? ;-)
> Can you not find out beforehand what you need?
> eg: Who wrote in this topic?
> + to who do they refer?
> + anything else that show up on the page.
>
> I am quite sure that you can tell that beforehand, simply because you do
> produce the output eventually.
> But it is possible that doing this takes up more time. :-/
> Hard to tell from this distance.

Yeah, that's the problem... Any given URL may consist of 20-30 PHP
scripts running, producing different form of output.

Just listing the last forum entries may be a simple enough task, but I
have different function from different files to prit images, member
info and such.

What I'm trying to say is that it's hard to say, when a new page
"starts" to know what member info will be presented when it has
finnished.

> > That's impossible to know beforehand. Articles listed may or may not
> > contain references to members. Any given page might contain references
> > to member ids from 30 different sources in 30 different tables. I
> > can't know, at the beginning of any page, exactly what member
> > information will be displayed on that page.
>
> See my former comment.
>
> I stress this point because you claim that the query itself takes only 0.16
> secs.
> That means that most time is 'lost' by PHP importing this resultset.
> If you can drill down the results to 1% by first finding out WHAT you
> need......

I realize the benefit of that, but it can't be done *beforehand*.

What *can* be done is doing it *after*. I.e. when presented with a
member id, the member_name() function only outputs a placeholder for
the information, like so:

Post from [[MEMBERINFO::1234]] on 2006-11-23:

And then, when processing the output buffer, I replace all these with
the proper information, and only fetch what member_name() has saved in
$GLOBALS["displayed_members"] or something like that.

Problem with THAT is that the page may contain one hundred, or maybe
five hundred such placeholders, and selecting something with "and id
in(<list of 500 id numbers>)" takes a lot of time (a lot more than
0.16 seconds). And then preg_replacing() them after selecting them
also takes some time.

I also have some pages that reference the prefetched array directly,
but those have to be edited either way, since the prefectched array
shouldn't exist.

> >> 4) Design your database smart. I mean by this: Design your database in
> >> such a way that the queries you typically need run fast.
> >
> > They do, they run very fast. The query runs in 0.16 seconds, the
> > action takes 0.8 seconds (and I'm assuming the extra time is moving
> > the resultset to the array).
>
> Is the 0.8 secs the time needed to produce all the HTML also, or is the 0.8
> secs the time needed to read the resultset into PHP's memory?

The 0.8 seconds are for the mysql_query() and mysql_fetch_array().
I.e. the query below

> >> The more joins, the slower the queries.
> >
> > No joins in this select. It's a very simple select and looks pretty
> > much like this:
> >
> > select field, field, field from member.member where status = 'ok' and
> > origin = 'LVL';
> >
> > "LVL" being the code for the particular site. "origin" is indexed. The
> > above query, when fetching 9000 posts, takes 0.16 seconds. That I can
> > live with it. It's moving that information to a PHP array that seems
> > to take too long.
> >
> >> SO it might help to duplicate some data (denormalization) in some tables.
> >> This can help a lot, but use it cautiously, since a good designed
> >> database stay as close to 100% nomalized as possible.
> >
> > Yes, the data I want is all in seperate "field, field, field".
>
> Clear.
> Just to be sure, you should of course NOT running seperate queries for each
> result you need.
> eg BAD:
> SELECT username FROM tbluser WHERE (userid=23);
> SELECT username FROM tbluser WHERE (userid=36);
> SELECT username FROM tbluser WHERE (userid=123);

No, that's stupid.

> eg GOOD:
> SELECT userid, username FROM tbluser WHERE
> (userid IN (23,36,123));

But BAD:
SELECT userid, username FROM tbluser WHERE
(userid IN (<500 id numbers>));

> > Yeah, I've never actually used them, and wouldn't you agree that that
> > wouldn't help me?
>
> Correct, it would only reduce the 0.16 secs you talked about. Not the (0.8 -
> 0.16) secs.

Exactly. And 0.16 is ok, 0.8 is not.


--
Sandman[.net]

[Back to original message]