Unpredictable Results

[Jack Lam]

Hi,

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:

#!/usr/bin/perl

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 ();
$sth->finish;

$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";
exit;