Hello,
For the company I work for I have set up a MySQL database for a
books website with more that 1 million records of books.
The visitor can search for keywords. A sequential scan would be
much too slow. (When I made the database, I had not yet heard of
the new FULLTEXT index in MySQL.) To speed up searches I placed
every word of every collumn in a seperate table called 'words'.
table: words
--------------------------
| WordNumber | Word |
|-------------------------
| .. | .. |
| 200 | 'jack' |
| 201 | 'road' |
| .. | .. |
--------------------------
I created another table called 'links' with references to the
books table:
table: links
----------------------------
| WordNumber | BookNumber |
|---------------------------
| .. | .. |
| 201 | 258374 |
| 675 | 578524 |
| .. | .. |
----------------------------
The 'books' table looks something like this:
table: books
-----------------------------------------------------
| BookNumber | Author | Title | ...
|----------------------------------------------------
| .. | .. | ... | ...
| 258374 | 'Kerouac, Jack' | 'On the Road' | ...
| .. | .. | ... | ...
-----------------------------------------------------
As records are added and deleted from the books table,
I make sure the 'words' table and the 'links' table are
updated.
As an example, when the visitor searches for the keywords
"jack kerouac road", then the following SQL statement is
created:
SELECT B.* FROM books AS B, words AS XL, links AS YL WHERE
(XL.xWord LIKE 'jack%' OR
XL.xWord LIKE 'kerouac%' OR
XL.xWord LIKE 'road%') AND
YL.WordNumber = XL.WordNumber AND
YL.BookNumber = B.BookNumber;
The website is getting incredibly busy and some searches
are getting slow.
Also, the links table is getting very big (more than
30 million records!).
If it continues to grow like it does now, we will end up in
problems in the near future.
Could anyone give me advice on how to increase performance?
Any help is much appreciated.
Thanks!
Tim Samshuijzen
---------------------------------------------------------------------
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
