Hello smart people,
Here's the situation:
table: books
----------------------------------------------------
|BookNumber |Author |Title | ....
----------------------------------------------------
| ... | | |
| 312 |'TWAIN, MARK' |'Huckleberry Finn' |
| ... | | |
----------------------------------------------------
The books table consists of more than 1 million records,
so wildcard searches are very slow.
To speed up searches for any appearing words, I've created a
dictionary containing all occuring words:
table: words
-------------------------
|WordNumber |Word |
-------------------------
| ... | |
| 457 | 'Finn' |
| ... | |
|------------------------
another table lists which words occur in which books:
table: wordlinks
--------------------------
|WordNumber |BookNumber |
--------------------------
| ... | |
| 457 | 312 |
| ... | |
|-------------------------
The table wordlinks contains 30 million records (!)
All nessesary fields are indexed.
When I search for a title or a couple of keywords, a search
is done in the words table to retrieve the associated WordNumber
values.
Secondly, these WordNumber values are searched in the wordlinks
table, and the associated BookNumber values are retrieved.
Only the BookNumber values which appear for each searched word
are retrieved.
Thirdly, the books are then located by BookNumber and
displayed in a search result.
My question is:
Is this three stage action possible with one SQL statement?
Any help is much appreciated.
Thanks!
Jelle Samshuijzen
Rockingstone Information Technology
http://www.rockingstone.com
http://www.boekenvondst.nl
http://www.nvva.nl
http://www.ilab-lila.com
http://www.interstad.nl
[EMAIL PROTECTED]
Tel. 0317-425550
Bergstraat 18a
6701 AD Wageningen
the Netherlands
---------------------------------------------------------------------
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
