Brewt... thanks for the reply... although the only problem I was having was the method I was using for getting the total hits. A search for "music" returned 18000 results, but took 50 seconds to load (there are about 90000 records in the database). I fixed that by using a count(*) in one query to get the total hit count, and a regular select with a limit to get the link results as links on the current page. Now the load time is an average 3.5 to 4.5 seconds on the same query. If the count query I orginally had was left out, the load times were just slightly faster than what they are now.
Here's what I'm using to generate the query:
Code:
foreach my $term (@terms) {
$clause .= "\t (";
foreach my $field (@search_fields) {
if (param('ww')) {
$clause .= " $field regexp '" . '[[:<:]]' . $term . '[[:>:]]' . "' or";
} else {
$clause .= qq| binary| if (param('cs'));
$clause .= qq| $field like '%$term%' or|;
}
}
$clause =~ s/or$//;
$clause .= ") $bool\n";
}
chomp($clause);
$clause =~ s/$bool$//;
my $search_query .= qq|
select *
from links
where
$where
order by Title
limit $offset, $max
|;
Happy coding,
--Drew
http://www.camelsoup.com
ftp://ftp.camelsoup.com
