Hi,
I need to improve the performance of a 1-word search engine
which I've created. Currently the RAM is 1 GIG and the key_buffer is set at
512 MB which has improved the search results but I think perhaps I can improve
my method of search with your help perhaps. The program is coded to run in
mod_perl or just perl.
It consists of 2 tables: word and content.
content consists of 2 fields:
rec_id int unsigned not null,
description varchar(200) not null
word consists of 2 fields:
word varchar(50) not null,
rec_id int unsigned not null
word is created from content. Each actual word from the field
content.description is created as a record in the table word with its rec_id.
Both tables are indexed on rec_id and word respectively.
A keyword is entered such as "book".
The search consists of 2 SQL queries
Query 1: select rec_id from word where word='book';
All the rec_ids are captured into an array.
Query2: select description from content where rec_id=?
I then loop over the rec_ids stored in the array @codes.
my $SqlCmd = 'select description from content where rec_id=?';
my $sth = $dbh->prepare( $SqlCmd );
$sth->{'mysql_use_result'} = 1; # doesn't store entire result
while ( $continue ) {
if ($njk > $#codes) { last; }
$cd = $codes[$njk++];
$sth->execute($cd);
@row = $sth->fetchrow_array;
# Collect results for display etc ...
}
$sth->finish;
Test Results:
Query 1 Query 2
pottery (915 records) 0 sec 1 sec
book (12456 records) 0 sec 13 sec
computer (21999 records) 1 sec 21 sec
Question: Is there a better way of doing this with respect to:
A. My definition of fields and their types.
B. My queries - it seems that Query 2 could perhaps be made
faster.
I thank you in advance. It is my hope to increase to 2, 3 and 4 word searches
once I've maximized the above search logic.
regards
David Jourard
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
