René,
Ok, here is a real representation of the tables
involved:
mysql> EXPLAIN maintable;
+---------------+---------------------+------+-----+------------+-----------
-----+
| Field | Type | Null | Key | Default | Extra
|
+---------------+---------------------+------+-----+------------+-----------
-----+
| RecordNumber | int(11) | | PRI | 0 |
auto_increment |
| Author | varchar(60) | | MUL | |
|
| Titel | varchar(175) | | MUL | |
|
| Description | varchar(100) | | | |
|
| DescripRest | text | | | NULL |
|
| Price | bigint(20) unsigned | | MUL | 0 |
|
+---------------+---------------------+------+-----+------------+-----------
-----+
20 rows in set (0.00 sec)
mysql> EXPLAIN hashindex;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| WordNumber | int(11) | | PRI | 0 | auto_increment |
| xWord | char(10) | | MUL | | |
+------------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> EXPLAIN wordindex;
+------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| WordNumber | int(11) | | MUL | 0 | |
| BookNumber | int(11) | | MUL | 0 | |
+------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
So you see, the fields that are used in the query are indexed.
And the queries still take more than 10 seconds each!
Tim
At 03:35 PM 6-2-2001 +0100, you wrote:
>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
