Dear René,
Thanks for your reply.
Oops, the 513 was a typing mistake.
And yes, all the requested fields are indexed.
CREATE TABLE wordindex (
WordNumber int(11) NOT NULL,
RecordNumber int(11) NOT NULL,
KEY WordNumber (WordNumber),
KEY RecordNumber (RecordNumber)
);
CREATE TABLE books (
RecordNumber int(11) NOT NULL auto_increment,
Field1 varchar(60) NOT NULL,
Field2 varchar(60) NOT NULL,
Field3 varchar(60) NOT NULL,
Field4 varchar(60) NOT NULL,
Field5 varchar(60) NOT NULL,
Price bigint(20) unsigned NOT NULL,
PRIMARY KEY (RecordNumber),
KEY Price (Price)
);
Any suggestions are very welcome.
Tim
At 01:51 PM 6-2-2001 +0100, you wrote:
>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
