* David Jourard
> I need to improve the performance of a 1-word search engine
> which I've created.
[...]
> 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.
You could probably improve on the speed by using a join:
select description
from content,word
where
word.word='book' and
word.rec_id = content.rec_id
[...]
> Question: Is there a better way of doing this with respect to:
> A. My definition of fields and their types.
varchar(50) for word... this column should be indexed, but you should not
index the full 50 characters. 5-10 is probably enough, depending on the
amount and distribution of your data. (If you have 100.000 words beginning
with 'computer-', you need more letters in the index.)
> B. My queries - it seems that Query 2 could perhaps be made
> faster.
You only need one query, and if word.word and content.rec_id are indexed, it
should be fast.
> 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.
You can join the same table multiple times:
select description
from content,word w1,word w2
where
w1.word='book' and
w2.word='computer' and
w1.rec_id = content.rec_id and
w2.rec_id = content.rec_id
HTH,
--
Roger
---------------------------------------------------------------------
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
