I come to your gates to find out where my bottle neck is. I am connecting to MySQL database (holding at the current time about 400,000 records and growin with every day) via web interface and PERL, of course. My problems is that to return a query it takes between 30 seconds to 2.10 minutes.

I know that database is not an issue because I can query in database itself in a jiffy. No slow downs. When I look into MySQL stats, I see many "slow queries" entries. I presume that this is from the web.

This is my piece of code: snip...] 

if ($Choice eq 't_datein') {
    if ($Choice2 eq 't_datein') {
        $query .= " ORDER BY $Choice $SortOrder, t_timein $SortOrder, 
+$Choice2, t_timein $SortOrder";
    } else {
        $query .= " ORDER BY $Choice $SortOrder, t_timein $SortOrder, 
+$Choice2";
    }
} else {
    $query .= " ORDER BY $Choice $SortOrder, $Choice2";
}

if ($Top ne 'All') {
    $query .= " LIMIT $Top";
}    
    
$sth = $dbh->prepare($query);
$sth->execute();

[download]
and

snip..] 

if (($Top eq 'All') && (!$SearchString)) {

    $total = $sth->fetchrow_array ();
    $total = "I'm confused" if !defined ($total);
    
    print "</table style=\"border: 1 solid #000080\" align='center'>\n
+";
    print "<p>\n";
    print "<h4>Query has returned $total number of records.</h4>\n";
    print "<hr width=\"98%\" align='left'>Please, narrow down your cry
+teria or make sure that if";
    print "<b><font color='red'> ALL is selected</b></font>,<br>";
    print "a string is provided in the query field as well!<p>\n";
        
} else {    
    while (($t_datein, $t_timein, $t_dateout, $t_timeout, 
        $t_port, $t_prot, $t_where, $t_from, $t_pkt_out, 
        $t_pkt_in, $t_byte_in, $t_byte_out, $t_id) = $sth->fetchrow_ar
+ray ()) {
            
        $skip = $i % 2;
        $i++;
        $bytes = $t_byte_out + $bytes;

        if ($t_where =~ /(adult|sex|oral|porn|xxx)/i) #you get an idea
+ {
            print "<tr bgcolor=\"#FF9999\">\n";
        } elsif ($skip eq 0 ) {
            print "<tr bgcolor=\"#CCFFCC\">\n";
        } else {
            print "<tr>\n";
        }
    
        print " <td align='right' bgcolor='black' align='top'><font co
+lor='white'><b>$i</b></font></td>\n";
        print "    <td align='right' align='top'>$t_from</td><td align
+='right' align='top'>";
        
            if ($t_where =~ /.*\.[a-z][a-z][a-z]?$/i) {
                print "<a href=\"http://$t_where\">$t_where</a>";
            } else {
                print "$t_where";
            }
        
        print "</td>\n";
        print "    <td align='right' align='top'>$t_datein $t_timein</
+td><td align='right' align='top'>$t_dateout $t_timeout</td>\n";
        print "<td align='right' align='top'>$t_byte_out</td>\n";
        
    }
    
    $bytes = ($bytes / 1024) / 1024;

}

($sth)->finish();
($dbh)->disconnect();

[download]
That's pretty much the core what I have been doing. I think that the problem is my code. How can I speed up those queries (for example, a simple 'select count(*) from table' shouldn't take 30 seconds).

I really appreciate your words of wisdom. :-)

In reply to Speed blues by bman

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.