Tim,
Hmmm... so you are suggesting the format of the query might be the cause of
this slow response. I find it hard to believe, unless you have a very big
result set i believe the query should complete within short time.
can you send the output of DESCRIBE db.tablename of all used tables?
regards,
rene
----- Original Message -----
From: "Tim Samshuijzen" <[EMAIL PROTECTED]>
To: "René Tegel" <[EMAIL PROTECTED]>
Sent: Tuesday, February 06, 2001 3:07 PM
Subject: Re: amazingly slow
Yep, all requested fields are indexed.
At 02:25 PM 6-2-2001 +0100, you wrote:
>Tim,
>Just kidding about the 513 Mb
>
>you put an index on wordindex.word as well ? (it's not in the table
>description but you use it in your query...Not indexing this field means
>mysql searches whole table for values.. Then your P800 has a reasonable
>performance on such a big table :)) )
>
>regards,
>
>rene
>
>----- Original Message -----
>From: "Tim Samshuijzen" <[EMAIL PROTECTED]>
>To: "René Tegel" <[EMAIL PROTECTED]>
>Sent: Tuesday, February 06, 2001 1:58 PM
>Subject: Re: amazingly slow
>
>
>
>
>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
>
>
>
---------------------------------------------------------------------
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
