Using DBI, better option than importing into @array

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);
}

# In the "view subject" section, loop through last 20 indexes of
@filenames
# 0 and 20 are dynamic in the real script
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");

print ...
}


I know that this has got to be the most inefficient method possible,
but I haven't found a better way. Is there a faster method to get the
information I want than this?

TIA,

Jason

.

Relevant Pages

  • Re: Advice about fetching user information
    ... but actually *selecting* them in MySQL ... PHP scripts and use a gaugefunction to time pretty much every ... The same is pretty much true for searching an array in PHP - ... But I wouldn't be searching. ...
    (comp.lang.php)
  • Re: Using DBI, better option than importing into @array
    ... MySQL, though, so I'm having fun with the challenges along the way. ... importing the subjects table into an array in the beginning of the ... is not a good measurement. ... That's extremely helpful, Peter. ...
    (comp.lang.perl.misc)
  • Re: [PHP] Add New Records Only!
    ... I do understand getting data from both the existing DBF and the multiple ... mySQL tables into a temporary mySQL table. ... Then, in the receiving code, have an array of SQL connections. ... Save a copy of your primary key for each next ...
    (php.general)
  • Re: Using DBI, better option than importing into @array
    ... MySQL, though, so I'm having fun with the challenges along the way. ... importing the subjects table into an array in the beginning of the ... and you'll probably guess wrong and spend a lot of time improving ... my @filenames; ...
    (comp.lang.perl.misc)
  • php/mysql puzzler
    ... wrapper function I've written to insert an array in a table. ... What is weirder is that it works fine on my local server -- I only get ... php or mysql setting that may have unintended consequence elsewhere. ...
    (comp.lang.php)