David Jourard wrote:
> 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.
I have done a similiar thing, but the programming is done in php, but
that shouldnt make any difference. To speed up full word searches I have
added a int(11) column where I store a crc32(www.php.net/crc32) of the
word. There is probably an equivalent of the php crc32 function
availible in perl, but I dont know.
From my experience of this I would say that mysql searches faster in an
int column than in a varchar. And then add an index to it and its real
fast. I have saved the varchar column for some like searches to, but it
should be possible to remove it, but it doesnt make much difference I guess.
> 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;
This must take a while, even if its fast since you have to execute a
query for each rec_id you found. I have solved this by doing 1 query to
get all of it.
For example, transform the rec_ids you get into something like this:
select description from content where rec_id in (1,45,3,4,19);
This will enable you to just make one query. Currently I'm not aware if
there is some limitation in the number of values to the IN (...) but it
might be.
To create a "several word search", you could do step 2 for each word and
then go through them again in an array or something to see which
contents that have all of the words desired.
Well, hope you got at least a bit wiser by this.. :)
Best regards,
Eric
---------------------------------------------------------------------
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
