Re^2: How to improve MYSQL search performance of perl?

Hi CountZero,

They are all URLs, for example: http://www.permonks.org/. What I did before is to make the database stuff as a sub routine and call it every time a new line is read as I don't know how to optimize the codes:

sub search{
#take search parameter from html <form/>
my $q = $_[0]; #taking param;
my $found = 0; #initialize category found count;
#open database
my $ref = {RaiseError => 1, AutoCommit => 0};
my $dbh = DBI->connect('DBI:mysql:diet', $ref) || die "Failed to connect: $DBI::errstr";
#prepare SQL search statement
my $sql = qq{select topic FROM table1 WHERE uri LIKE '$q'};
my $sth = $dbh->prepare($sql);
$sth->execute();
while(my $record = $sth->fetchrow_hashref) {
no warnings;
print &topic($record->{topic}), "
\n";
$found++;
}
if ($found == 0){ #if no category was found, output no found
print "

$q is not found in the database!

\n";
}
$sth->finish();
$dbh->disconnect(); #disconnect from database;
}

Thanks again,

Nan

Comment on Re^2: How to improve MYSQL search performance of perl?

Replies are listed 'Best First'.
Re^3: How to improve MYSQL search performance of perl? by CountZero (Bishop) on Aug 19, 2005 at 18:32 UTC
I see why it is so slow: you are effectively for every search opening a connection, doing the search for 1 item and then destroying the connection. All this connecting and disconnecting is very time-consuming. You should put your connection stuff in an initialization subroutine, then `prepare` your SQL-statement once, using place-holders as follows: " `my $sth = $dbh->prepare('select topic FROM table1 WHERE uri LIKE ?');`" (added benefit: you don't have to worry about quoting!) and then hand off the `$sth`-variable and the search-argument to your search-subroutine which calls the `execute`-method with the search string as its parameter: `my ($statement_handle, $search_argument)=@_; $statement_handle->execute($search_argument); ...` [download] Do you get the idea! CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply] [d/l] [select]
Re^4: How to improve MYSQL search performance of perl? by nan (Novice) on Aug 24, 2005 at 17:11 UTC
Hi, I can see what you mean but the result seems not improved and I received Internal Server 500 Error. I don't know if it is because I have hundreds of queries needed to be executed one by one or whatever. Initially, when I search by just one query, the time spent is no more than 30 secs but if I have 10 or 20 queries, the time spent is much more than 30 * 10/20. Do you have any ideas? Many many thanks! Nan	[reply]
Re^5: How to improve MYSQL search performance of perl? by CountZero (Bishop) on Aug 24, 2005 at 18:29 UTC
The "`Internal Server 500`"-error is probably due to the fact that your script generates an error and terminates, thus bringing down that part of your Apache server with it. What do you find in the error-log of Apache? Do you check all your DBI-methods for errors? Use CGI::Carp so you are sure all your errors are logged to the error-log and can be checked later. Can you run your script outside of the Apache-server and if so what is the result? CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply] [d/l]
Re^6: How to improve MYSQL search performance of perl? by nan (Novice) on Aug 25, 2005 at 15:56 UTC