Re: Advice about fetching user information

In article <4566c326$0$330$e4fe514c@xxxxxxxxxxxxxx>,
Erwin Moller
<since_humans_read_this_I_am_spammed_too_much@xxxxxxxxxxxxxxxx>
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]
.

Relevant Pages

  • Re: Advice about fetching user information
    ... SELECT username FROM tbluser WHERE; ... SELECT userid, username FROM tbluser WHERE ... Fetching all 1000 members in one really big IN: ...
    (comp.lang.php)
  • Re: Advice about fetching user information
    ... you should of course NOT running seperate queries for each ... SELECT username FROM tbluser WHERE; ... SELECT userid, username FROM tbluser WHERE ...
    (comp.lang.php)
  • Re: Username of a system-process without admin-account??
    ... I can't find any member which contains information about the UserID. ... Maybe I misunderstood you. ... That works for all processes running under my username. ...
    (microsoft.public.vb.winapi)
  • Re: Advice about fetching user information
    ... but if I need to fetch information for 100 members on one page making 100 individual SQL queries is also a no-go. ... Whenever a logged-in user does something, update the timestamp in this table for that userid. ... But that's not very intelligent either, since a SQL query with "select x, x, x from member where id in" isn't very good. ...
    (comp.lang.php)
  • Re: Forgotten password
    ... are there any tutorials/guides out there that explain how to handle this subject? ... I was thinking of having a member enter his username and email, after which I then email him a new password. ... The real question then becomes: why do I need a seperate "member-id" when one of my email addresses identifies me anyway? ...
    (comp.lang.php)