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

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

Peter> I seem to be able to narrow the problem down and it seems to me 
Peter> very strange.

Peter> Below I have used a simpler query - if I SELECT the DATE field only 
Peter> then MySQL uses an index, otherwise it doesn't.

Peter> Please note, this is using version 3.23.5-alpha-log as I upgraded 
Peter> thinking it may solve the problems.

mysql> EXPLAIN SELECT REF_NO FROM TRAN WHERE COMPANY_ID 
Peter> = 'AM' AND DATE BETWEEN '1999-06-01' AND '1999-06-30';
Peter> +-------+------+---------------------------------+---------+---------+----
Peter> ---+------+------------+
Peter> | table | type | possible_keys                   | key     | key_len | ref   
Peter> | rows | Extra      |
Peter> +-------+------+---------------------------------+---------+---------+----
Peter> ---+------+------------+
Peter> | TRAN  | ref  | TRAN_I1,TRAN_I2,TRAN_I3,TRAN_ID | TRAN_ID |    
Peter> 2 | const | 3546 | where used |
Peter> +-------+------+---------------------------------+---------+---------+----
Peter> ---+------+------------+
Peter> 1 row in set (0.07 sec)

mysql> EXPLAIN SELECT DATE FROM TRAN WHERE COMPANY_ID = 
Peter> 'AM' AND DATE BETWEEN '1999-06-01' AND '1999-06-30';
Peter> +-------+------+---------------------------------+---------+---------+----
Peter> ---+------+-------------------------+
Peter> | table | type | possible_keys                   | key     | key_len | ref   
Peter> | rows | Extra                   |
Peter> +-------+------+---------------------------------+---------+---------+----
Peter> ---+------+-------------------------+
Peter> | TRAN  | ref  | TRAN_I1,TRAN_I2,TRAN_I3,TRAN_ID | TRAN_ID |    
Peter> 2 | const | 3546 | where used; Using index |
Peter> +-------+------+---------------------------------+---------+---------+----
Peter> ---+------+-------------------------+
Peter> 1 row in set (0.01 sec)

mysql> EXPLAIN SELECT REF_NO, DATE FROM TRAN WHERE 
Peter> COMPANY_ID = 'AM' AND DATE BETWEEN '1999-06-01' AND '1999-
Peter> 06-30';
Peter> +-------+------+---------------------------------+---------+---------+----
Peter> ---+------+------------+
Peter> | table | type | possible_keys                   | key     | key_len | ref   
Peter> | rows | Extra      |
Peter> +-------+------+---------------------------------+---------+---------+----
Peter> ---+------+------------+
Peter> | TRAN  | ref  | TRAN_I1,TRAN_I2,TRAN_I3,TRAN_ID | TRAN_ID |    
Peter> 2 | const | 3546 | where used |
Peter> +-------+------+---------------------------------+---------+---------+----
Peter> ---+------+------------+
Peter> 1 row in set (0.04 sec)

mysql> 

Peter> When I select DATE field only the query takes 2.65 seconds and 
Peter> returns 6147 rows. If I SELECT any other fields the query doesn't 
Peter> use the index and takes several minutes on my database (total 
Peter> 320000 records).

Note that the 'ref' means that MySQL did use an index.

The 'Using index' means that MySQL could execute the query by only
using the index (without having to access the rows in the table)

Peter> It seems very strange that the query should use different search 
Peter> method based on the fields being selected - unless of course it was 
Peter> trying simply to read the indexes only without even loading the data 
Peter> records to return results(?).

Yes; See above.

On the other hand, it should not take several minutes on a table with
only 320000 rows.

Any change you can upload the table to
http://www.mysql.com/pub/mysql/secret so that I can test this?

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