MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:René Tegel Date:February 6 2001 1:25pm
Subject:Re: amazingly slow
View as plain text  
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" <tim@stripped>
To: "René Tegel" <tegel@stripped>
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" <tim@stripped>
>To: <mysql@stripped>
>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 <mysql-thread64299@stripped>
>> To unsubscribe, e-mail
<mysql-unsubscribe-tegel=chello.nl@stripped>
>> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>>
>
>
>



Thread
amazingly slowTim Samshuijzen6 Feb
  • Re: amazingly slowDave Hodgkinson6 Feb
    • Re: amazingly slowTim Samshuijzen6 Feb
      • RE: amazingly slowDaniel Kirk6 Feb
        • RE: amazingly slowTim Samshuijzen6 Feb
      • Re: amazingly slowBenjamin Pflugmann9 Feb
Re: amazingly slowRené Tegel6 Feb
Re: amazingly slowTim Samshuijzen6 Feb
Re: amazingly slowDave Hodgkinson6 Feb
  • Re: amazingly slowTim Samshuijzen6 Feb
Re: amazingly slowRené Tegel6 Feb
Re: amazingly slowRené Tegel6 Feb
  • Re: amazingly slowTim Samshuijzen6 Feb
RE: amazingly slowJulian Strickland6 Feb