http://www.perlmonks.org?node_id=107153

mandrakus has asked for the wisdom of the Perl Monks concerning the following question:

Can someone help me optimize this query? I'm kind of new at database stuff. $q=" select sitename,url,country,language,listing,description from main where category='$category' and sitename like '$letter' "; Thanks, Axel

Replies are listed 'Best First'.
Re: Optimizing a MySQL Query
by lhoward (Vicar) on Aug 23, 2001 at 01:40 UTC
    1. use DBI placeholders... (very rough code below)
      my $sth=$dbh->prepare_cached("select sitename, url, country, language, + listing, description from main where category=? and sitename like ?" +); $sth->execute($category,$letter);
    2. Make sure your DB has good indexes on category and sitename.
      You add indexes in mysql by writing create index indexname ON main (category) in the mysql program... See CREATE INDEX

      T I M T O W T D I
Re: Optimizing a MySQL Query
by Cine (Friar) on Aug 23, 2001 at 02:02 UTC
    In general, what you do is use the functions in mysql that tells you what the database will do with your query. See DESCRIBE SYNTAX. This will almost always tell you where you need a good index and also possible give you a hint of what to do with the query to make it faster.

    T I M T O W T D I
      Tim, Thanks for the advice. I tried implementing the changes you suggested but that query still takes about 30 seconds to display the list on my website when the results number about 170 records. It only takes about 2 seconds to execute on the server. That makes me think it might possibly be a problem with data transfer. Do you have any more suggestions for me for finding out how to make this faster? Thanks, Axel
        There are two possibilites. You either have a bandwidth problem, which is easily checked my running you script on the server and in case is performs the same that is not the problem, or you are using plenty of modules and you are running plain CGI. The latter is most likely the problem and you should consider looking into modperl.

        T I M T O W T D I
Re: Optimizing a MySQL Query
by pmas (Hermit) on Aug 23, 2001 at 18:31 UTC
    When speaking about placeholders, you may want to look at Tricks with DBI. I liked it and learned a lot. Also, you may want to look at discussion about Learning CGI & DBI well - if this is what you want to learn well... ;-)

    And yes, INDEX is a way how to increase speed of data access in database - for the price: you have some speed penalty to maintan it when updating indexed fields. This is interesting, but OT here.

    pmas
    To make errors is human. But to make million errors per second, you need a computer.