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!
> >>>
> >>> 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
---------------------------------------------------------------------
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
