Tim,
1. i'd remove 1 Mb from your 513 Mb machine... maybe it's an very old edo
simm or something.
2. you put an index on all requested fields (maintable.recordnumber and
wordindex.word) ? I bet not.
> ----- Original Message -----
> From: "Tim Samshuijzen" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Tuesday, February 06, 2001 12:04 PM
> Subject: amazingly slow
>
>
> >
> >
> > 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
> >
>
---------------------------------------------------------------------
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
