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