Re: Using DBI, better option than importing into @array
- From: xhoster@xxxxxxxxx
- Date: 30 Jul 2007 22:25:46 GMT
Jason <jwcarlton@xxxxxxxxx> wrote:
I've posted a few times now that I'm rebuilding a message board
program to use MySQL instead of flat text files. I'm relatively new to
MySQL, though, so I'm having fun with the challenges along the way.
The database has 2 tables: one to hold subjects, and one to hold all
of the posts. The subjects table has around 17000 rows, while the
posts table has around 600,000.
The most current problem is SPEED! From sheer lack of knowledge, I'm
importing the subjects table into an array in the beginning of the
script, then using a for loop throughout the program to access that
array. But the program is running pretty slow, and I'm sure that the
bottleneck is with this array.
Here is the code that I'm using:
# Push subjects into Perl array
my $filelist = $dbh->selectall_arrayref("SELECT `id`, `lastmodified`,
`subject` FROM $forum_subjects ORDER BY lastmodified DESC");
my @filenames;
for my $row (@$filelist) {
my ($id, $lastmodified, $subject) = @$row;
push(@filenames, $id . "|:|" . $lastmodified . "|:|" . $subject);
Why join the data with |:| just to later use split on it? Unless you are
severally strapped for memory, that is probably not a good thing to do.
}
# In the "view subject" section, loop through last 20 indexes of
@filenames
# 0 and 20 are dynamic in the real script
You could use the "limit" keyword (mysql specific) to retrieve only
the topics you are interested in, rather than returning all of them
and then using only a subset. I don't know how much, if any, of a speed
improvement this would make.
for ($count=0; $count < 20; $count++) {
($id, $lastmodified, $subject) = split(/\|:\|/, $filenames[$count]);
my $topiclist = $dbh->selectall_arrayref("SELECT `id`, `subject`,
`postdate`, `username`, `email`, `comment` FROM $forum_posts WHERE
id=" . $dbh->quote($id) . " ORDER BY postdate ASC");
As a side note, it is generally better to use placeholders than to use
dbh->quote.
Is there an index on the id column of $forum_posts? If not, then MySQL has
to read through all 600,000 rows, for each of the 21 topics. I'm guessing
that this will make MySQL, not Perl, the bottleneck.
Xho
--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB
.
- Follow-Ups:
- References:
- Prev by Date: Re: Perl with DBI
- Next by Date: Re: Using DBI, better option than importing into @array
- Previous by thread: Using DBI, better option than importing into @array
- Next by thread: Re: Using DBI, better option than importing into @array
- Index(es):
Relevant Pages
|
