Hello, I don't understand why my queries are so incredibly slow.
We have MySQL on a 800MHz Linux machine with 513Mb.
Most queries look like this:
SELECT B.* FROM maintable AS M, wordindex AS YL1,
wordindex AS YL2, wordindex AS YL3
WHERE
YL1.Word = 'billy' AND
YL1.RecordNumber = M.RecordNumber AND
YL2.Word = 'bob' AND
YL2.RecordNumber = M.RecordNumber AND
YL3.Word = 'john' AND
YL3.RecordNumber = M.RecordNumber AND
M.Price >= 1000
LIMIT 0,50;
wordindex is a table that contains all words present in maintable.
For each word there is a link to maintable through RecordNumber.
This query searches for all records in maintable that contain the
three words and where it's price is more than 1000.
This query takes more than 20 seconds!!!!!!!!!
I hear from others that this query should be returned in a flash!
maintable contains about 900,000 records.
wordindex contains about 21,000,000 records
All columns are indexed.
Here are my parameters:
key_buffer=256M
table_cache=256
sort_buffer=1M
record_buffer=2M
join_buffer=4M
max_sort_length=30
max_connections=300
I am really desperate. I've been trying everything.
I've tried the OPTIMIZE TABLE commands, but this also doesn't help.
Anyone out there who wants to save me and our company?
Thanks a lot!
Tim
---------------------------------------------------------------------
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
