Re: amazingly slow
Hi. You may want to try a combined index (RecordNumber, WordNumber) on wordindex, because the indexes you have can only restrict on field (RecordNumber in the EXPLAIN below) e.g. ALTER TABLE wordindex ADD UNIQUE(RecordNumber, WordNumber); I presume that the RecordNumber/WordNumber pairs are unique (seems so from what your tables look like). If not, you have to replace UNQIUE by KEY. Bye, Benjamin. On Tue, Feb 06, 2001 at 01:49:41PM +0100, [EMAIL PROTECTED] wrote: [...] > I did the EXPLAIN as you suggested. > > Here is the output: > > mysql> EXPLAIN SELECT B.* FROM maintable AS B , > -> wordindex AS YL1 , > -> wordindex AS YL2 , > -> wordindex AS YL3 WHERE > -> YL1.WordNumber = 123 AND > -> YL1.RecordNumber = B.RecordNumber AND > -> YL2.WordNumber = 345 AND > -> YL2.RecordNumber = B.RecordNumber AND > -> YL3.WordNumber = 678 AND > -> YL3.RecordNumber = B.RecordNumber AND > -> B.Price >= 1000 > -> LIMIT 0,51; > +-+--+---++---++ > +--+ > |table|type |possible_keys |key |key_len|ref > |rows|Extra | > +-+--+---++---++ > +--+ > |YL3 |ref |WordNumber,RecordNumber|WordNumber | 4 |??? > | 1 | | > |B|eq_ref|PRIMARY|PRIMARY | 4 > |YL3.RecordNumber| 1 |where used| > |YL2 |ref |WordNumber,RecordNumber|RecordNumber| 4 |B.RecordNumber > | 23 |where used| > |YL1 |ref |WordNumber,RecordNumber|RecordNumber| 4 |B.RecordNumber > | 23 |where used| > +-+--+---++---++ > +--+ > 4 rows in set (0.01 sec) > [...] - 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
RE: amazingly slow
Try this
SELECT * FROM maintable AS M, wordindex AS YL
WHERE
YL.Word IN ('billy' , 'bob' , 'john' )
AND
YL3.RecordNumber = M.RecordNumber
AND
M.Price >= 1000
LIMIT 0,50;
Your query is probably slow because you are cubing the size of the wordindex
by invoking it three times
> -Original Message-
> From: René Tegel [SMTP:[EMAIL PROTECTED]]
> Sent: 06 February 2001 14:35
> To: Tim Samshuijzen; [EMAIL PROTECTED]
> Subject: Re: amazingly slow
>
> 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!
> >>
RE: amazingly slow
Dear Dan, Thanks for your reply. I've already done OPTIMIZE for all tables. I suppose that's the same as rebuilding the indices. Tim At 01:26 AM 7-2-2001 -0800, you wrote: >i've heard it said that every so often (eg every month) you need to rebuild >indexes on tables, as over time they lose their performance enhancement. >could that be true? > >dan > > >-Original Message- >From: Tim Samshuijzen [mailto:[EMAIL PROTECTED]] >Sent: Tuesday, 6 February 2001 04:50 >To: [EMAIL PROTECTED] >Subject: Re: amazingly slow > > > > >Dear Dave, > >Thanks for your reply. > >(The table actually works with word numbers, as the words >are present in a hash table. I explained it the way I did >because functionally it is the same. So the actual search >is for WordNumber values instead of Word values.) > >I did the EXPLAIN as you suggested. > >Here is the output: > >mysql> EXPLAIN SELECT B.* FROM maintable AS B , >-> wordindex AS YL1 , >-> wordindex AS YL2 , >-> wordindex AS YL3 WHERE >-> YL1.WordNumber = 123 AND >-> YL1.RecordNumber = B.RecordNumber AND >-> YL2.WordNumber = 345 AND >-> YL2.RecordNumber = B.RecordNumber AND >-> YL3.WordNumber = 678 AND >-> YL3.RecordNumber = B.RecordNumber AND >-> B.Price >= 1000 >-> LIMIT 0,51; >+-+--+---++---++ >+--+ >|table|type |possible_keys |key |key_len|ref >|rows|Extra | >+-+--+---++---++ >+--+ >|YL3 |ref |WordNumber,RecordNumber|WordNumber | 4 |??? >| 1 | | >|B|eq_ref|PRIMARY|PRIMARY | 4 >|YL3.RecordNumber| 1 |where used| >|YL2 |ref |WordNumber,RecordNumber|RecordNumber| 4 |B.RecordNumber >| 23 |where used| >|YL1 |ref |WordNumber,RecordNumber|RecordNumber| 4 |B.RecordNumber >| 23 |where used| >+-+--+---++---++ >+--+ >4 rows in set (0.01 sec) > > >This still does not tell me why the query is amazingly slow. > >Tim > > >At 10:52 AM 6-2-2001 +, you wrote: >>Tim Samshuijzen <[EMAIL PROTECTED]> writes: >> >>> Anyone out there who wants to save me and our company? >> >>You missed the important first step: EXPLAIN the query. >> >>-- >>Dave Hodgkinson, http://www.hodgkinson.org >>Editor-in-chief, The Highway Star http://www.deep-purple.com >> Apache, mod_perl, MySQL, Sybase hired gun for, well, hire >> - >> >>- >>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 > > > - 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
Re: amazingly slow
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 800M
Re: amazingly slow
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
RE: amazingly slow
i've heard it said that every so often (eg every month) you need to rebuild indexes on tables, as over time they lose their performance enhancement. could that be true? dan -Original Message- From: Tim Samshuijzen [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 6 February 2001 04:50 To: [EMAIL PROTECTED] Subject: Re: amazingly slow Dear Dave, Thanks for your reply. (The table actually works with word numbers, as the words are present in a hash table. I explained it the way I did because functionally it is the same. So the actual search is for WordNumber values instead of Word values.) I did the EXPLAIN as you suggested. Here is the output: mysql> EXPLAIN SELECT B.* FROM maintable AS B , -> wordindex AS YL1 , -> wordindex AS YL2 , -> wordindex AS YL3 WHERE -> YL1.WordNumber = 123 AND -> YL1.RecordNumber = B.RecordNumber AND -> YL2.WordNumber = 345 AND -> YL2.RecordNumber = B.RecordNumber AND -> YL3.WordNumber = 678 AND -> YL3.RecordNumber = B.RecordNumber AND -> B.Price >= 1000 -> LIMIT 0,51; +-+--+---++---++ +--+ |table|type |possible_keys |key |key_len|ref |rows|Extra | +-+--+---++---++ +--+ |YL3 |ref |WordNumber,RecordNumber|WordNumber | 4 |??? | 1 | | |B|eq_ref|PRIMARY|PRIMARY | 4 |YL3.RecordNumber| 1 |where used| |YL2 |ref |WordNumber,RecordNumber|RecordNumber| 4 |B.RecordNumber | 23 |where used| |YL1 |ref |WordNumber,RecordNumber|RecordNumber| 4 |B.RecordNumber | 23 |where used| +-+--+---++---++ +--+ 4 rows in set (0.01 sec) This still does not tell me why the query is amazingly slow. Tim At 10:52 AM 6-2-2001 +, you wrote: >Tim Samshuijzen <[EMAIL PROTECTED]> writes: > >> Anyone out there who wants to save me and our company? > >You missed the important first step: EXPLAIN the query. > >-- >Dave Hodgkinson, http://www.hodgkinson.org >Editor-in-chief, The Highway Star http://www.deep-purple.com > Apache, mod_perl, MySQL, Sybase hired gun for, well, hire > - > >- >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
Re: amazingly slow
Dear Dave, Here is the output from vmstat 5: >vmstat 5 procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 0 0 0 3936 2980 10568 218856 8 411 33 9 0 3 10 0 0 0 3936 3096 10580 218728 0 0 0 1 11130 0 1 99 0 0 0 3936 3092 10584 218728 0 0 0 1 10926 1 1 98 2 0 0 3936 1676 10564 220012 0 0 287 1 212 137 1 2 97 2 0 0 3936 2628 10480 219084 0 0 646 2 235 130 4 3 93 1 0 0 3936 1652 10488 219428 22 0 727 5 456 522 2 2 96 0 1 0 3936 1708 10480 218776 0 0 1042 4 438 433 0 2 97 1 0 0 3936 2724 10484 218216 28 0 1028 0 488 557 0 3 97 1 0 0 3936 3080 10484 217860 0 0 308 2 297 380 34 16 50 1 0 0 3936 3072 10484 217860 0 0 0 0 10310 67 33 0 0 0 0 3936 4064 10500 218392 0 0 565 1 411 558 10 5 84 Does this make any sense to you? Tim At 12:17 PM 6-2-2001 +, you wrote: >Tim Samshuijzen <[EMAIL PROTECTED]> writes: > >> This still does not tell me why the query is amazingly slow. > >What else is happening on the system? what does, say, a vmstat 5 give? > > >-- >Dave Hodgkinson, http://www.hodgkinson.org >Editor-in-chief, The Highway Star http://www.deep-purple.com > Apache, mod_perl, MySQL, Sybase hired gun for, well, hire > - > >- >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
Re: amazingly slow
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
Re: amazingly slow
Tim Samshuijzen <[EMAIL PROTECTED]> writes: > This still does not tell me why the query is amazingly slow. What else is happening on the system? what does, say, a vmstat 5 give? -- Dave Hodgkinson, http://www.hodgkinson.org Editor-in-chief, The Highway Star http://www.deep-purple.com Apache, mod_perl, MySQL, Sybase hired gun for, well, hire - - 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
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
Re: amazingly slow
Dear Dave, Thanks for your reply. (The table actually works with word numbers, as the words are present in a hash table. I explained it the way I did because functionally it is the same. So the actual search is for WordNumber values instead of Word values.) I did the EXPLAIN as you suggested. Here is the output: mysql> EXPLAIN SELECT B.* FROM maintable AS B , -> wordindex AS YL1 , -> wordindex AS YL2 , -> wordindex AS YL3 WHERE -> YL1.WordNumber = 123 AND -> YL1.RecordNumber = B.RecordNumber AND -> YL2.WordNumber = 345 AND -> YL2.RecordNumber = B.RecordNumber AND -> YL3.WordNumber = 678 AND -> YL3.RecordNumber = B.RecordNumber AND -> B.Price >= 1000 -> LIMIT 0,51; +-+--+---++---++ +--+ |table|type |possible_keys |key |key_len|ref |rows|Extra | +-+--+---++---++ +--+ |YL3 |ref |WordNumber,RecordNumber|WordNumber | 4 |??? | 1 | | |B|eq_ref|PRIMARY|PRIMARY | 4 |YL3.RecordNumber| 1 |where used| |YL2 |ref |WordNumber,RecordNumber|RecordNumber| 4 |B.RecordNumber | 23 |where used| |YL1 |ref |WordNumber,RecordNumber|RecordNumber| 4 |B.RecordNumber | 23 |where used| +-+--+---++---++ +--+ 4 rows in set (0.01 sec) This still does not tell me why the query is amazingly slow. Tim At 10:52 AM 6-2-2001 +, you wrote: >Tim Samshuijzen <[EMAIL PROTECTED]> writes: > >> Anyone out there who wants to save me and our company? > >You missed the important first step: EXPLAIN the query. > >-- >Dave Hodgkinson, http://www.hodgkinson.org >Editor-in-chief, The Highway Star http://www.deep-purple.com > Apache, mod_perl, MySQL, Sybase hired gun for, well, hire > - > >- >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
Re: amazingly slow
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
Re: amazingly slow
Tim Samshuijzen <[EMAIL PROTECTED]> writes: > Anyone out there who wants to save me and our company? You missed the important first step: EXPLAIN the query. -- Dave Hodgkinson, http://www.hodgkinson.org Editor-in-chief, The Highway Star http://www.deep-purple.com Apache, mod_perl, MySQL, Sybase hired gun for, well, hire - - 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
