Efficient ways to retrieve specific rows...

[Daniel Tonks]

First, please excuse the fact that I'm a complete MySQL newbie.

My site used forum software that I wrote myself (in Perl) which, up until
now, has used flat files. This worked fine, however lately I've been wanting
to do more stuff with user accounts, and had been eying MySQL for over a
year.

Finally I've decided to start off small by converting the forum's account
system to a MySQL database (and convert the rest later after I'm
comfortable). So far everything is working fine, and I've figured out how to
create the table, insert records, modify records, and so on.

However I had a question on what was the most efficient way to retrieve
information about a user as I read through the flat file containing
messages.

As each message is read I want to find that user's relevant information,
build it into my output, then continue on to the next message. Now here's my
problem. Since I'm reading a flat file in a way that was once trying to be
somewhat memory efficient (on really slow server way back when, trying to
avoid arrays and hashes) I'm finding that I'm having to send separate
questions to MySQL as each message comes up. Say from 1 to 20 very simple
questions to complete printing the page. Also, sometimes the questions might
be repetitive - since I'm not storing any of the results in memory, if a
user appears twice I ask about it twice.

Would I really be better off trying to find a way to consolidate all
distinct users into a single question? Or is MySQL efficient enough that
this isn't really much of a concern?

- Daniel

[Jeff North]

Re: Efficient ways to retrieve specific rows...

On Fri, 6 Aug 2004 05:18:43 -0400, in mailing.databas e.mysql "Daniel
Tonks" <dtonks@sunstor mADD-DOT-COM> wrote:
[color=blue]
>| First, please excuse the fact that I'm a complete MySQL newbie.
>|
>| My site used forum software that I wrote myself (in Perl) which, up until
>| now, has used flat files. This worked fine, however lately I've been wanting
>| to do more stuff with user accounts, and had been eying MySQL for over a
>| year.
>|
>| Finally I've decided to start off small by converting the forum's account
>| system to a MySQL database (and convert the rest later after I'm
>| comfortable). So far everything is working fine, and I've figured out how to
>| create the table, insert records, modify records, and so on.
>|
>| However I had a question on what was the most efficient way to retrieve
>| information about a user as I read through the flat file containing
>| messages.
>|
>| As each message is read I want to find that user's relevant information,
>| build it into my output, then continue on to the next message. Now here's my
>| problem. Since I'm reading a flat file in a way that was once trying to be
>| somewhat memory efficient (on really slow server way back when, trying to
>| avoid arrays and hashes) I'm finding that I'm having to send separate
>| questions to MySQL as each message comes up. Say from 1 to 20 very simple
>| questions to complete printing the page. Also, sometimes the questions might
>| be repetitive - since I'm not storing any of the results in memory, if a
>| user appears twice I ask about it twice.
>|
>| Would I really be better off trying to find a way to consolidate all
>| distinct users into a single question? Or is MySQL efficient enough that
>| this isn't really much of a concern?[/color]

You are still using the database like a flat file. Therefore *you* are
still doing the work instead of the database.

It is difficult to give you a clear answer without seeing your table
structures but you could extract you information with a query like:
SELECT * from bloggs where username='Joe Bloggs';

Sorting this list would be:
SELECT * from bloggs where username='Joe Bloggs' ORDER BY bloggID;

Multiple criteria (last 30days of entries by Joe Bloggs)
SELECT * from bloggs where username='Joe Bloggs' and
EntryDate<=CurD ate()-30;

[Daniel Tonks]

Re: Efficient ways to retrieve specific rows...

"Jeff North" <jnorth@yourpan tsbigpond.net.a u> wrote in message
news:om07h0hqh4 voo31pmjpc3ccvj abbo2tmvm@4ax.c om...[color=blue]
> On Fri, 6 Aug 2004 05:18:43 -0400, in mailing.databas e.mysql "Daniel
> Tonks" <dtonks@sunstor mADD-DOT-COM> wrote:
>
> You are still using the database like a flat file. Therefore *you* are
> still doing the work instead of the database.
>
> It is difficult to give you a clear answer without seeing your table
> structures but you could extract you information with a query like:
> SELECT * from bloggs where username='Joe Bloggs';
>
> Sorting this list would be:
> SELECT * from bloggs where username='Joe Bloggs' ORDER BY bloggID;
>
> Multiple criteria (last 30days of entries by Joe Bloggs)
> SELECT * from bloggs where username='Joe Bloggs' and
> EntryDate<=CurD ate()-30;[/color]


That's all true, but the only thing I've MADE a database is the accounts
section. Everything else is still flat file, which is why I'm still sort of
accessing the database that way. Right now...

Read #1 from flat file: SUBJECT USERID DATE POST
SELECT stuffneeded FROM accounts WHERE id=userid
Process & print message.

Read #2 from flat file: SUBJECT USERID DATE POST
SELECT stuffneeded FROM accounts WHERE id=userid
Process & print message.

The only way around that seems to be to read the entire thread into memory,
gather a list of needed userid's, get the needed information from the
database, and THEN print everything out. Unless MySQL can handle this sort
of repeated simple request, in which case I'd rather not put the effort into
completely rewriting a problem that will probably need to be rewritten in a
few months.

I realize that this will become less of an issue once I make all the indexes
and messages part of a database (the books I'm reading give some good "ah
ha!" examples for stuff I'd like to do), but being an utter newbie to such
databases I'm starting small with just one aspect of it. Then I'll start
merging other features.

So far I'm very happy with how efficient the coding is for this... what was
40 lines to process and update the flat file before is down to just a
couple! Woo hoo!

- Daniel

[Bill Karwin]

Re: Efficient ways to retrieve specific rows...

Daniel Tonks wrote:[color=blue]
> Unless MySQL can handle this sort
> of repeated simple request, in which case I'd rather not put the effort into
> completely rewriting a problem that will probably need to be rewritten in a
> few months.[/color]

MySQL can probably handle it. A BB system like you describe isn't
likely to operate on enough volume of data to overwhelm a DBMS, unless
there are many hundreds of simultaneous users.

But if you want to do some improvements, you could do any of the following:

- Fetch the user information using a parameterized query that you
prepare once, e.g. "SELECT stuffneeded FROM accounts WHERE id=?" and
execute the prepared query each time while passing the userid as a
parameter.

- Fetch the user information for each given userid once, and keep it
cached in memory for the duration of the session. Store it in a hashed
array indexed by the user id. I assume this type of information is
usually static, and won't be likely to change during a given user's session.

- Do what you said about loading the thread into memory to find the
complete list of user id's relevant to the thread, and then load them:
"SELECT stuffneeded FROM accounts
WHERE id IN ($comma_separat ed_list_of_user _ids)"

Regards,
Bill K.

[Jeff North]

Re: Efficient ways to retrieve specific rows...

On Fri, 6 Aug 2004 18:37:27 -0400, in mailing.databas e.mysql "Daniel
Tonks" <dtonks@sunstor mADD-DOT-COM> wrote:
[color=blue]
>| "Jeff North" <jnorth@yourpan tsbigpond.net.a u> wrote in message
>| news:om07h0hqh4 voo31pmjpc3ccvj abbo2tmvm@4ax.c om...
>| > On Fri, 6 Aug 2004 05:18:43 -0400, in mailing.databas e.mysql "Daniel
>| > Tonks" <dtonks@sunstor mADD-DOT-COM> wrote:
>| >
>| > You are still using the database like a flat file. Therefore *you* are
>| > still doing the work instead of the database.
>| >
>| > It is difficult to give you a clear answer without seeing your table
>| > structures but you could extract you information with a query like:
>| > SELECT * from bloggs where username='Joe Bloggs';
>| >
>| > Sorting this list would be:
>| > SELECT * from bloggs where username='Joe Bloggs' ORDER BY bloggID;
>| >
>| > Multiple criteria (last 30days of entries by Joe Bloggs)
>| > SELECT * from bloggs where username='Joe Bloggs' and
>| > EntryDate<=CurD ate()-30;
>|
>| That's all true, but the only thing I've MADE a database is the accounts
>| section. Everything else is still flat file, which is why I'm still sort of
>| accessing the database that way. Right now...
>|
>| Read #1 from flat file: SUBJECT USERID DATE POST
>| SELECT stuffneeded FROM accounts WHERE id=userid
>| Process & print message.
>|
>| Read #2 from flat file: SUBJECT USERID DATE POST
>| SELECT stuffneeded FROM accounts WHERE id=userid
>| Process & print message.[/color]

Hold it right there. Lets get the terminology correct first.
To me a flat-file is a file outside of the database that can be opened
with a text editor. A flat-file is not subject to database queries. Is
this what you mean?

Or do you mean flat-table (non-normalised table) that resides in the
database?

Lets design your database. There are 3 types of tables People, Things
and Events.

People tables: this would be your users - so create a table called
users and add the necessary fields.
create table users
userid int(11) not null default 0 auto_increment,
firstname ..... etc
Primary KEY ('userid')

Next is the Things tables: you want to be able to store your message
header information. Create a table called MessageHdrs
create table MessageHdrs
MessageHdrID int(11) not null default 0 auto_increment,
Subject varchar(255) ....
CreateDate Date ....
Active enum('Yes','No' ) ....
InitiatedByUser int(11)....
Primary Key ('MessageHdrID' )

Finally are the Event tables - this is where you join all your People
and Things tables. This is what all the action is, meaning where you
store your messages. Create a table called Threads
create table Threads
ThreadsID int(11) not null default 0 auto_increment,

now link in the message header info
fkMsgHdr int(11) ....

now link in which user posted the message
fkUserID int(11)...

Now add whatever other information you like to capture
EntryDate Date ...
Message text ...
Primary KEY ('ThreadsID')

With this type of setup you can retrieve data by message or user.

You would retrieve the information by using the following sql
statement (append the where and order by clause to this statement).

SELECT threads.*, user.*, messageHdrs.*
FROM user INNER JOIN (messageHdrs INNER JOIN threads ON
messageHdrs.mes sagehdrid = threads.fkMsgHd rID) ON user.userid =
threads.fkUserI D;
[color=blue]
>| The only way around that seems to be to read the entire thread into memory,
>| gather a list of needed userid's, get the needed information from the
>| database, and THEN print everything out. Unless MySQL can handle this sort
>| of repeated simple request, in which case I'd rather not put the effort into
>| completely rewriting a problem that will probably need to be rewritten in a
>| few months.
>|
>| I realize that this will become less of an issue once I make all the indexes
>| and messages part of a database (the books I'm reading give some good "ah
>| ha!" examples for stuff I'd like to do), but being an utter newbie to such
>| databases I'm starting small with just one aspect of it. Then I'll start
>| merging other features.
>|
>| So far I'm very happy with how efficient the coding is for this... what was
>| 40 lines to process and update the flat file before is down to just a
>| couple! Woo hoo!
>|
>| - Daniel
>|[/color]

[Norman Peelman]

Re: Efficient ways to retrieve specific rows...

"Jeff North" <jnorth@yourpan tsbigpond.net.a u> wrote in message
news:om07h0hqh4 voo31pmjpc3ccvj abbo2tmvm@4ax.c om...[color=blue]
> On Fri, 6 Aug 2004 05:18:43 -0400, in mailing.databas e.mysql "Daniel
> Tonks" <dtonks@sunstor mADD-DOT-COM> wrote:
>[color=green]
> >| First, please excuse the fact that I'm a complete MySQL newbie.
> >|
> >| My site used forum software that I wrote myself (in Perl) which, up[/color][/color]
until[color=blue][color=green]
> >| now, has used flat files. This worked fine, however lately I've been[/color][/color]
wanting[color=blue][color=green]
> >| to do more stuff with user accounts, and had been eying MySQL for over[/color][/color]
a[color=blue][color=green]
> >| year.
> >|
> >| Finally I've decided to start off small by converting the forum's[/color][/color]
account[color=blue][color=green]
> >| system to a MySQL database (and convert the rest later after I'm
> >| comfortable). So far everything is working fine, and I've figured out[/color][/color]
how to[color=blue][color=green]
> >| create the table, insert records, modify records, and so on.
> >|
> >| However I had a question on what was the most efficient way to retrieve
> >| information about a user as I read through the flat file containing
> >| messages.
> >|
> >| As each message is read I want to find that user's relevant[/color][/color]
information,[color=blue][color=green]
> >| build it into my output, then continue on to the next message. Now[/color][/color]
here's my[color=blue][color=green]
> >| problem. Since I'm reading a flat file in a way that was once trying to[/color][/color]
be[color=blue][color=green]
> >| somewhat memory efficient (on really slow server way back when, trying[/color][/color]
to[color=blue][color=green]
> >| avoid arrays and hashes) I'm finding that I'm having to send separate
> >| questions to MySQL as each message comes up. Say from 1 to 20 very[/color][/color]
simple[color=blue][color=green]
> >| questions to complete printing the page. Also, sometimes the questions[/color][/color]
might[color=blue][color=green]
> >| be repetitive - since I'm not storing any of the results in memory, if[/color][/color]
a[color=blue][color=green]
> >| user appears twice I ask about it twice.
> >|
> >| Would I really be better off trying to find a way to consolidate all
> >| distinct users into a single question? Or is MySQL efficient enough[/color][/color]
that[color=blue][color=green]
> >| this isn't really much of a concern?[/color]
>
> You are still using the database like a flat file. Therefore *you* are
> still doing the work instead of the database.
>
> It is difficult to give you a clear answer without seeing your table
> structures but you could extract you information with a query like:
> SELECT * from bloggs where username='Joe Bloggs';
>
> Sorting this list would be:
> SELECT * from bloggs where username='Joe Bloggs' ORDER BY bloggID;
>
> Multiple criteria (last 30days of entries by Joe Bloggs)
> SELECT * from bloggs where username='Joe Bloggs' and
> EntryDate<=CurD ate()-30;
>
>[/color]

That really isn't the answer to his question. The problem is he is that he
has not committed to take the leap from flat file to database. The very way
in which he is retrieving the information is what is holding him back. I
believe there are two options:

1) go full database (would require writing scripts to convert flat files to
database structure)
2) just download forum software and modify to fit his needs.

The first is a pain, the later, much easier.

Norm


--
Avatar hosting at www.easyavatar.com

[Daniel Tonks]

Re: Efficient ways to retrieve specific rows...

"Norman Peelman" <npeelman@cfl.r r.com> wrote in message
news:EbXQc.3955 $4s6.2739@torna do.tampabay.rr. com...[color=blue]
>
> That really isn't the answer to his question. The problem is he is that[/color]
he[color=blue]
> has not committed to take the leap from flat file to database. The very[/color]
way[color=blue]
> in which he is retrieving the information is what is holding him back. I
> believe there are two options:
>
> 1) go full database (would require writing scripts to convert flat files[/color]
to[color=blue]
> database structure)
> 2) just download forum software and modify to fit his needs.
>
> The first is a pain, the later, much easier.[/color]

It's not so much that I haven't committed as I don't want to bite off more
than I can chew at once. Thus converting in stages.

I have no problem inputting existing flat files to the database - the
scripts to do that are pretty easy - the bigger problem is coming up with an
efficient data structure that does what I need. Which will take a bit of
thought (like how many tables for the forums and indices - or should I use
completely separate databases - etc).

And I don't want to resort to pre-written forum software yet. I just prefer
having a really unique look and feel (it may be possible to modify some of
the prewritten forum software out there as much as I'd like, but frankly I
haven't seen a good example of that so I'll just stick to my own stuff...
which I enjoy doing at any rate).

- Daniel

[Daniel Tonks]

Re: Efficient ways to retrieve specific rows...

"Bill Karwin" <bill@karwin.co m> wrote in message
news:cf12vh014b o@enews1.newsgu y.com...[color=blue]
>
> MySQL can probably handle it. A BB system like you describe isn't
> likely to operate on enough volume of data to overwhelm a DBMS, unless
> there are many hundreds of simultaneous users.
>
> But if you want to do some improvements, you could do any of the[/color]
following:[color=blue]
>
> - Fetch the user information using a parameterized query that you
> prepare once, e.g. "SELECT stuffneeded FROM accounts WHERE id=?" and
> execute the prepared query each time while passing the userid as a
> parameter.[/color]

Does this really improve things? I was reading through my Perl/MySQL book on
this and couldn't see much point to it besides providing easier
compatibility with other database servers.

[color=blue]
> - Fetch the user information for each given userid once, and keep it
> cached in memory for the duration of the session. Store it in a hashed
> array indexed by the user id. I assume this type of information is
> usually static, and won't be likely to change during a given user's[/color]
session.[color=blue]
>
> - Do what you said about loading the thread into memory to find the
> complete list of user id's relevant to the thread, and then load them:
> "SELECT stuffneeded FROM accounts
> WHERE id IN ($comma_separat ed_list_of_user _ids)"[/color]

Thanks for the other suggestions as well.

- Daniel

[Daniel Tonks]

Re: Efficient ways to retrieve specific rows...

"Jeff North" <jnorth@yourpan tsbigpond.net.a u> wrote in message
news:vg88h0ha0s 8p7jr9evs630qep 750k331jk@4ax.c om...[color=blue]
> On Fri, 6 Aug 2004 18:37:27 -0400, in mailing.databas e.mysql "Daniel
> Tonks" <dtonks@sunstor mADD-DOT-COM> wrote:
>[color=green]
> >| Read #2 from flat file: SUBJECT USERID DATE POST
> >| SELECT stuffneeded FROM accounts WHERE id=userid
> >| Process & print message.[/color]
>
> Hold it right there. Lets get the terminology correct first.
> To me a flat-file is a file outside of the database that can be opened
> with a text editor. A flat-file is not subject to database queries. Is
> this what you mean?
>
> Or do you mean flat-table (non-normalised table) that resides in the
> database?[/color]

Sorry, I was just paraphrasing. Yes, I have to read in each line from the
text file and split the line into the variables I need. Hey, the forum was
originally structured back in 1998 (expanded many times since), databases
wern't exactly on my mind at the time. :-)

[color=blue]
> Lets design your database. There are 3 types of tables People, Things
> and Events.
>
> People tables: this would be your users - so create a table called
> users and add the necessary fields.
> create table users
> userid int(11) not null default 0 auto_increment,
> firstname ..... etc
> Primary KEY ('userid')
>
> Next is the Things tables: you want to be able to store your message
> header information. Create a table called MessageHdrs
> create table MessageHdrs
> MessageHdrID int(11) not null default 0 auto_increment,
> Subject varchar(255) ....
> CreateDate Date ....
> Active enum('Yes','No' ) ....
> InitiatedByUser int(11)....
> Primary Key ('MessageHdrID' )
>
> Finally are the Event tables - this is where you join all your People
> and Things tables. This is what all the action is, meaning where you
> store your messages. Create a table called Threads
> create table Threads
> ThreadsID int(11) not null default 0 auto_increment,
>
> now link in the message header info
> fkMsgHdr int(11) ....
>
> now link in which user posted the message
> fkUserID int(11)...
>
> Now add whatever other information you like to capture
> EntryDate Date ...
> Message text ...
> Primary KEY ('ThreadsID')
>
> With this type of setup you can retrieve data by message or user.[/color]

I think you've done something like this before! A very well thought out
structure...

[color=blue]
> You would retrieve the information by using the following sql
> statement (append the where and order by clause to this statement).
>
> SELECT threads.*, user.*, messageHdrs.*
> FROM user INNER JOIN (messageHdrs INNER JOIN threads ON
> messageHdrs.mes sagehdrid = threads.fkMsgHd rID) ON user.userid =
> threads.fkUserI D;[/color]


Ah, joins. Hadn't quite waded through that chapter yet, so the whole concept
escapes me right now.

This seems to be a very memory efficient design - minimal duplication of
data. I've read your post three times and I think I'll give it a few
additional go overs. One of the reasons I'm splitting this up into phases -
the accounts section is easy, the rest seems much harder. :-)

Thanks very much for the ideas - you have no idea how appreciated they are.

- Daniel

[Jeff North]

Re: Efficient ways to retrieve specific rows...

On Sat, 7 Aug 2004 00:56:52 -0400, in mailing.databas e.mysql "Daniel
Tonks" <dtonks@sunstor mADD-DOT-COM> wrote:
[color=blue]
>| "Jeff North" <jnorth@yourpan tsbigpond.net.a u> wrote in message
>| news:vg88h0ha0s 8p7jr9evs630qep 750k331jk@4ax.c om...
>| > On Fri, 6 Aug 2004 18:37:27 -0400, in mailing.databas e.mysql "Daniel
>| > Tonks" <dtonks@sunstor mADD-DOT-COM> wrote:
>| >
>| > >| Read #2 from flat file: SUBJECT USERID DATE POST
>| > >| SELECT stuffneeded FROM accounts WHERE id=userid
>| > >| Process & print message.
>| >
>| > Hold it right there. Lets get the terminology correct first.
>| > To me a flat-file is a file outside of the database that can be opened
>| > with a text editor. A flat-file is not subject to database queries. Is
>| > this what you mean?
>| >
>| > Or do you mean flat-table (non-normalised table) that resides in the
>| > database?
>|
>| Sorry, I was just paraphrasing. Yes, I have to read in each line from the
>| text file and split the line into the variables I need. Hey, the forum was
>| originally structured back in 1998 (expanded many times since), databases
>| wern't exactly on my mind at the time. :-)[/color]

Then create your tables and use the database post haste. Text files
can not be indexed and therefore a sequential search of the data is
require. There is no getting away from this.
[color=blue]
>| > Lets design your database. There are 3 types of tables People, Things
>| > and Events.[/color]

[snip]
[color=blue]
>| > With this type of setup you can retrieve data by message or user.
>|
>| I think you've done something like this before![/color]

Only a couple of thousand times :-)
It is also how I used to teach students to design relational
databases.
[color=blue]
>| A very well thought out structure...[/color]

Its the template I use to create any database. Helps me focus on the
overall structure then drill down to the individual tables and their
requirements.
[color=blue]
>| > You would retrieve the information by using the following sql
>| > statement (append the where and order by clause to this statement).
>| >
>| > SELECT threads.*, user.*, messageHdrs.*
>| > FROM user INNER JOIN (messageHdrs INNER JOIN threads ON
>| > messageHdrs.mes sagehdrid = threads.fkMsgHd rID) ON user.userid =
>| > threads.fkUserI D;
>|
>| Ah, joins. Hadn't quite waded through that chapter yet, so the whole concept
>| escapes me right now.
>|
>| This seems to be a very memory efficient design - minimal duplication of
>| data. I've read your post three times and I think I'll give it a few
>| additional go overs. One of the reasons I'm splitting this up into phases -
>| the accounts section is easy, the rest seems much harder. :-)[/color]

Use the People, Tings, Events (PTE) methodology to design you
database. A bit of time with pen and paper and thought will help you
alot.

Something I didn't mention in the previous post. You might want to add
different categories/areas that people can post to i.e. General
Discussion, Weather, Politics etc. Using the PTE methodology can you
see how this table could be added later without disrupting the current
design?

Also the previous design only allowed for a flat list (unlike a
tree-like structure). Obviously this is way over your head at the
moment but once you understand databases then you should be able to
plug in this feature without too much trouble (provided your database
design is sound to start with).
[color=blue]
>| Thanks very much for the ideas - you have no idea how appreciated they are.[/color]

If it helps, here is my web site that I setup for my students. I
haven't updated in years and it uses MSAccess as the database.

http://www.users.bigpond.net.au/jefnorth/access.htm

The Tables area might be very helpful to you.

No probs.

[Jeff North]

Re: Efficient ways to retrieve specific rows...

On Sat, 07 Aug 2004 02:33:08 GMT, in mailing.databas e.mysql "Norman
Peelman" <npeelman@cfl.r r.com> wrote:
[color=blue]
>| "Jeff North" <jnorth@yourpan tsbigpond.net.a u> wrote in message
>| news:om07h0hqh4 voo31pmjpc3ccvj abbo2tmvm@4ax.c om...
>| > On Fri, 6 Aug 2004 05:18:43 -0400, in mailing.databas e.mysql "Daniel
>| > Tonks" <dtonks@sunstor mADD-DOT-COM> wrote:
>| >
>| > >| First, please excuse the fact that I'm a complete MySQL newbie.
>| > >|
>| > >| My site used forum software that I wrote myself (in Perl) which, up
>| until
>| > >| now, has used flat files. This worked fine, however lately I've been
>| wanting
>| > >| to do more stuff with user accounts, and had been eying MySQL for over
>| a
>| > >| year.
>| > >|
>| > >| Finally I've decided to start off small by converting the forum's
>| account
>| > >| system to a MySQL database (and convert the rest later after I'm
>| > >| comfortable). So far everything is working fine, and I've figured out
>| how to
>| > >| create the table, insert records, modify records, and so on.
>| > >|
>| > >| However I had a question on what was the most efficient way to retrieve
>| > >| information about a user as I read through the flat file containing
>| > >| messages.
>| > >|
>| > >| As each message is read I want to find that user's relevant
>| information,
>| > >| build it into my output, then continue on to the next message. Now
>| here's my
>| > >| problem. Since I'm reading a flat file in a way that was once trying to
>| be
>| > >| somewhat memory efficient (on really slow server way back when, trying
>| to
>| > >| avoid arrays and hashes) I'm finding that I'm having to send separate
>| > >| questions to MySQL as each message comes up. Say from 1 to 20 very
>| simple
>| > >| questions to complete printing the page. Also, sometimes the questions
>| might
>| > >| be repetitive - since I'm not storing any of the results in memory, if
>| a
>| > >| user appears twice I ask about it twice.
>| > >|
>| > >| Would I really be better off trying to find a way to consolidate all
>| > >| distinct users into a single question? Or is MySQL efficient enough
>| that
>| > >| this isn't really much of a concern?
>| >
>| > You are still using the database like a flat file. Therefore *you* are
>| > still doing the work instead of the database.
>| >
>| > It is difficult to give you a clear answer without seeing your table
>| > structures but you could extract you information with a query like:
>| > SELECT * from bloggs where username='Joe Bloggs';
>| >
>| > Sorting this list would be:
>| > SELECT * from bloggs where username='Joe Bloggs' ORDER BY bloggID;
>| >
>| > Multiple criteria (last 30days of entries by Joe Bloggs)
>| > SELECT * from bloggs where username='Joe Bloggs' and
>| > EntryDate<=CurD ate()-30;
>| >
>| >
>|
>| That really isn't the answer to his question.[/color]

In a way your right :-)
[color=blue]
>| The problem is he is that he
>| has not committed to take the leap from flat file to database. The very way
>| in which he is retrieving the information is what is holding him back. I
>| believe there are two options:
>|
>| 1) go full database (would require writing scripts to convert flat files to
>| database structure)
>| 2) just download forum software and modify to fit his needs.
>|
>| The first is a pain, the later, much easier.[/color]

True :-)

[Jeff North]

Re: Efficient ways to retrieve specific rows...

On Sat, 7 Aug 2004 00:33:24 -0400, in mailing.databas e.mysql "Daniel
Tonks" <dtonks@sunstor mADD-DOT-COM> wrote:
[color=blue]
>| "Norman Peelman" <npeelman@cfl.r r.com> wrote in message
>| news:EbXQc.3955 $4s6.2739@torna do.tampabay.rr. com...
>| >
>| > That really isn't the answer to his question. The problem is he is that
>| he
>| > has not committed to take the leap from flat file to database. The very
>| way
>| > in which he is retrieving the information is what is holding him back. I
>| > believe there are two options:
>| >
>| > 1) go full database (would require writing scripts to convert flat files
>| to
>| > database structure)
>| > 2) just download forum software and modify to fit his needs.
>| >
>| > The first is a pain, the later, much easier.
>|
>| It's not so much that I haven't committed as I don't want to bite off more
>| than I can chew at once. Thus converting in stages.
>|
>| I have no problem inputting existing flat files to the database - the
>| scripts to do that are pretty easy - the bigger problem is coming up with an
>| efficient data structure that does what I need. Which will take a bit of
>| thought (like how many tables for the forums and indices - or should I use
>| completely separate databases - etc).
>|
>| And I don't want to resort to pre-written forum software yet. I just prefer
>| having a really unique look and feel (it may be possible to modify some of
>| the prewritten forum software out there as much as I'd like, but frankly I
>| haven't seen a good example of that so I'll just stick to my own stuff...
>| which I enjoy doing at any rate).[/color]

You'll get there. Althought there will be must frustration along the
way but the effort will be worth it. :-)

[Daniel Tonks]

Re: Efficient ways to retrieve specific rows...

"Jeff North" <jnorth@yourpan tsbigpond.net.a u> wrote in message
news:ud19h09b4g 1a2905171rcugdp vbkm6c11q@4ax.c om...[color=blue]
> On Sat, 7 Aug 2004 00:56:52 -0400, in mailing.databas e.mysql "Daniel
> Tonks" <dtonks@sunstor mADD-DOT-COM> wrote:
>[color=green]
> >| > Or do you mean flat-table (non-normalised table) that resides in the
> >| > database?
> >|
> >| Sorry, I was just paraphrasing. Yes, I have to read in each line from[/color][/color]
the[color=blue][color=green]
> >| text file and split the line into the variables I need. Hey, the forum[/color][/color]
was[color=blue][color=green]
> >| originally structured back in 1998 (expanded many times since),[/color][/color]
databases[color=blue][color=green]
> >| wern't exactly on my mind at the time. :-)[/color]
>
> Then create your tables and use the database post haste. Text files
> can not be indexed and therefore a sequential search of the data is
> require. There is no getting away from this.[/color]

Oh, I know that... my current search engine sequentially reads through over
400,000 messages (73,000+ files) as it is now... although I've come up with
ways to make it more efficient over the years. I recently built myself a
dedicated server (which is why I finally have MySQL available) that has
oodles of power and actually manages to work though that horrible searching
task quite quickly.

All in due time though, getting the account system done is turning into
enough work (although I'm merging in a whole output design overhaul as
well).

[color=blue][color=green]
> >| > With this type of setup you can retrieve data by message or user.
> >|
> >| I think you've done something like this before![/color]
>
> Only a couple of thousand times :-)
> It is also how I used to teach students to design relational
> databases.[/color]

Ah, that explains it. :-)

<SNIP>
[color=blue]
> Use the People, Tings, Events (PTE) methodology to design you
> database. A bit of time with pen and paper and thought will help you
> alot.
>
> Something I didn't mention in the previous post. You might want to add
> different categories/areas that people can post to i.e. General
> Discussion, Weather, Politics etc. Using the PTE methodology can you
> see how this table could be added later without disrupting the current
> design?[/color]

Yeah, I was figuring that out (currently have 21 forums). However couldn't
it just be part of the "Things" table? Add a "ForumID" field and assign a
forum ID to each message header when it's added, then just specify one when
retrieving headers.

[color=blue]
> Also the previous design only allowed for a flat list (unlike a
> tree-like structure). Obviously this is way over your head at the
> moment but once you understand databases then you should be able to
> plug in this feature without too much trouble (provided your database
> design is sound to start with).[/color]

The current system isn't threaded, so I'm content to stick with that.

[color=blue][color=green]
> >| Thanks very much for the ideas - you have no idea how appreciated they[/color][/color]
are.[color=blue]
>
> If it helps, here is my web site that I setup for my students. I
> haven't updated in years and it uses MSAccess as the database.
> http://www.users.bigpond.net.au/jefnorth/access.htm
> The Tables area might be very helpful to you.
>
> No probs.[/color]

Thanks, I'll take a look at it. One of my books had a chapter on smart
database design which helped me improve the efficiency of the current
accounts table (which stores quite a few different items).

- Daniel

[Norman Peelman]

Re: Efficient ways to retrieve specific rows...

"Daniel Tonks" <dtonks@sunstor mADD-DOT-COM> wrote in message
news:41145c16$0 $8084$a1866201@ authen.newsread er.visi.com...[color=blue]
> "Norman Peelman" <npeelman@cfl.r r.com> wrote in message
> news:EbXQc.3955 $4s6.2739@torna do.tampabay.rr. com...[color=green]
> >
> > That really isn't the answer to his question. The problem is he is that[/color]
> he[color=green]
> > has not committed to take the leap from flat file to database. The very[/color]
> way[color=green]
> > in which he is retrieving the information is what is holding him back. I
> > believe there are two options:
> >
> > 1) go full database (would require writing scripts to convert flat files[/color]
> to[color=green]
> > database structure)
> > 2) just download forum software and modify to fit his needs.
> >
> > The first is a pain, the later, much easier.[/color]
>
> It's not so much that I haven't committed as I don't want to bite off more
> than I can chew at once. Thus converting in stages.
>
> I have no problem inputting existing flat files to the database - the
> scripts to do that are pretty easy - the bigger problem is coming up with[/color]
an[color=blue]
> efficient data structure that does what I need. Which will take a bit of
> thought (like how many tables for the forums and indices - or should I use
> completely separate databases - etc).
>
> And I don't want to resort to pre-written forum software yet. I just[/color]
prefer[color=blue]
> having a really unique look and feel (it may be possible to modify some of
> the prewritten forum software out there as much as I'd like, but frankly I
> haven't seen a good example of that so I'll just stick to my own stuff...
> which I enjoy doing at any rate).
>
> - Daniel
>
>
>[/color]

Daniel,
I understand how you feel about 'sticking to your own stuff' but to be
honest, you really are holding yourself back. Any of the decent forum
software has editable (templated) themes where all you really have to do is
change some css for your unique feel. You are just trying to reinvent the
wheel. Maybe one suggestion would be to download a forum and examine their
SQL SCHEMA (database setup for tables, etc) and see what they are using and
adapt from there.

Norm


--
Avatar hosting at www.easyavatar.com

[Daniel Tonks]

Re: Efficient ways to retrieve specific rows...

"Daniel Tonks" <dtonks@sunstor mADD-DOT-COM> wrote in message
news:41149e54$0 $65576$a1866201 @authen.newsrea der.visi.com...[color=blue]
>
> The current system isn't threaded, so I'm content to stick with that.[/color]

I should clarify... it's threaded one level deep.

- Daniel