Re: Using DBI, better option than importing into @array
- From: Brian Blackmore <blb8@xxxxxxxxxxx>
- Date: Tue, 31 Jul 2007 18:25:56 +0000 (UTC)
Jason <jwcarlton@xxxxxxxxx> wrote:
It's really a simple message board, just with a lot of rows. So the
subjects table is
Categories: id - lastmodified - subject
Actual row: 17090 - 20070730192222 - This is a test
The posts table would be like:
Categories: id - subject - postdate - username - email - comment
Actual row: 17090 - This is a test - 20070730192222 - Jason -
jwcarlton@xxxxxxxxx - This is a test comment.
Where are the messages?
In retrospect, I can't quite remember why I'm duplicating the
information into the subjects table; I think it was at the suggestion
of someone in another NG. I'm sure that the goal was to make the "view
subjects" section load faster by reading 17,000 rows instead of
600,000.
I presume that the goal was to make the subjects load faster by moving
the message _contents_ (i.e., the actual posts) of into their own table.
Anything that you display in the "quick view" should doubtless be
someplace else that allows for faster database access. Granted, a true
SQL VIEW might be able to handle fast selection on a big mess of a
table, but there's no point in doing that.
You need a posts table that contains the post id and the actual content
of the post (oh, maybe that was "comment"?); you need a summary table
that contains the "quick view" content; indices on the primary keys (of
course), and no doubt and index on postdate.
As suggested elsewhere, use LIMIT.
With such a small number of records, the database latency should be far
less than a second.
Also, I'll repeat one other thing from elsewhere, definitely let the
database do the selection because it knows how to optimize for fastest
query return. Looping select statements in perl is generally a bad
idea.
--
Brian Blackmore
blb8 at po dot cwru dot edu
.
- References:
- Prev by Date: Re: Perl with DBI
- Next by Date: How do you continue in a for loop?
- Previous by thread: Re: Using DBI, better option than importing into @array
- Next by thread: Re: Using DBI, better option than importing into @array
- Index(es):
Relevant Pages
|
|
