List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:November 7 1999 10:55pm
Subject:Slow query - very bad optimisation.
View as plain text  
>>>>> "Peter" == Peter Campbell <pc@stripped> writes:

Peter> I have a query which is taking impossibly too long. (Mysql 3.22.27 
Peter> on Linux)

Peter> The first example (with DATE = constant) works as expected;

Peter> SELECT REF_NO FROM TRAN, MODULE_TRAN
Peter> WHERE COMPANY_ID = 'AM' 
Peter> AND DATE = '1999-06-30'
Peter> AND MODULE_TRAN.COMPANY_ID = TRAN.COMPANY_ID
Peter> AND MODULE_TRAN.TYPE = TRAN.TYPE
Peter> AND (MODULE_TRAN.PROGRAM = 'I'
Peter> OR MODULE_TRAN.PROGRAM = 'C')

Peter> When I do an "EXPLAIN SELECT ..." it shows the correct index to 
Peter> use on the TRAN record (which contains about 350,000 records) and 
Peter> works "instantly".

Peter> When I change the DATE clause to use a date range as follows;

Peter> SELECT REF_NO FROM TRAN, MODULE_TRAN
Peter> WHERE COMPANY_ID = 'AM' 
Peter> AND DATE >= '1999-06-01' AND DATE <= '1999-06-30'
Peter> AND MODULE_TRAN.COMPANY_ID = TRAN.COMPANY_ID
Peter> AND MODULE_TRAN.TYPE = TRAN.TYPE
Peter> AND (MODULE_TRAN.PROGRAM = 'I'
Peter> OR MODULE_TRAN.PROGRAM = 'C')

Peter> the query now uses a different index for the TRAN record, one that 
Peter> doesn't even include the DATE field and therefore every record is 
Peter> read.

Peter> The "correct" index to use contains the following fields;

Peter> COMPANY_ID, DATE, TYPE

Peter> The index the second query uses contains;

Peter> COMPANY_ID, REF_NO, HEADER, MODULE, SORT_SEQUENCE

Peter> Are there known problems with the optimiser?
Peter> Am I expected to force the system to use the right index?
Peter> Are there other things I should be aware of or reporting?

Hi!

Can you please post an EXPLAIN of both queries ?
Please also check if the numbers in the 'rows' column is 'approximate' 
right.

Regards,
Monty
Thread
Slow query - very bad optimisation.Peter Campbell2 Nov
  • Re: Slow query - very bad optimisation.sinisa2 Nov
  • Slow query - very bad optimisation.Michael Widenius8 Nov
    • Re: Slow query - very bad optimisation.Peter Campbell8 Nov
      • EXPLAIN/SHOW format [was Re: Slow query - very bad optimisation.]Bob Kline8 Nov
      • Re: Slow query - very bad optimisation.Michael Widenius10 Nov