MySQL Forums
Forum List  »  Perl

Unpredictable Results
Posted by: Jack Lam
Date: December 22, 2010 03:30AM


I've a database table with 664,955 rows. The column structure of one of the column, 'Title' is as follows:

title CHAR(255) NOT NULL,
FULLTEXT (title)

I've a perl script to count the number of matches of various keywords against the column 'Title'. It will print out the time it took to run MySql and the total count of matches.

Most of the time, there are no problem because it will get the search results very quickly.

There are times for some keywords, it will take a long time to get the search result. However, if I ran the script again with the same keywords, it will get the search result very quickly.

The followings are the results of 3 runs for the same keywords:

Time Ran: 48, Total Count: 6325
Time Ran: 0, Total Count: 6325
Time Ran: 0, Total Count: 6325

As you can see, the first run took 48 sec., the second and third runs took 0 sec.

I'm preplexed by the results.

Here are my questions:

1) Why would it take so long to get the result in the first run?
2) Why would it be so quick to get the results after the first run?

My server is a Linus Server.

The following is the perl script:


require 'myconfigure.cgi';

use DBI;
$keywords = "contact-information, and more. Just enter the domain-name";

print "Content-type: text/html\n\n";

$time1 = time;
$count = 0;
$dbh=DBI->connect("dbi:mysql:$database:localhost","$mysqlusername","$mysqlpassword") || &return_page('System Error', "Couldn't connect to database(212): $DBI::errstr\n");

$keywords_quoted = $dbh ->quote ($keywords);
$query="SELECT count(*) FROM $websites_table WHERE MATCH(title) AGAINST ($keywords_quoted) and title != '' and pagerank >= '2'";
$sth=$dbh->prepare(qq{$query}) || &return_page('System Error', "Prepare failed(502): $query\n");
$sth->execute() || &return_page('System Error', "Couldn't execute query(503): $query\n");
$total_count = $sth->fetchrow_array ();

$dbh->disconnect ||die("Couldn't disconnect to database!\n");
$time2 = time;
$time_ran = $time2 - $time1;

print "Time Ran: $time_ran, Total Count: $total_count\n";

Options: ReplyQuote

Written By
Unpredictable Results
December 22, 2010 03:30AM
December 24, 2010 10:34AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.