in reply to How to improve MYSQL search performance of perl?

Are all these queries similar to each other? I mean is it like: 
SELECT * FROM table WHERE field = 1
SELECT * FROM table WHERE field = 10
SELECT * FROM table WHERE field = 75
SELECT * FROM table WHERE field = 3
SELECT * FROM table WHERE field = 8
...

[download]

If that is the case you could probably benefit from using placeholders and using $sth = $dbh->prepare($statement) or $sth = $dbh->prepare_cached($statement).

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

Replies are listed 'Best First'.
Re^2: How to improve MYSQL search performance of perl?
by nan (Novice) on Aug 19, 2005 at 15:27 UTC

    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:

    Read more... Subroutine (869 Bytes)

    Thanks again,

    Nan

[reply]
      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]

        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]

In Section Seekers of Perl Wisdom