Hi, I have a problem with mysql
I have 3 tables:
Deposit
+-------------------+-------------+------+-----+
| Field | Type | Null | Key |
+-------------------+-------------+------+-----+
| id | bigint(20) | NO | PRI |
| status | int(2) | NO | |
| depositDate | datetime | NO | MUL |
| reversePayment_id | bigint(20) | YES | UNI |
| claim_id | int(2) | NO | UNI |
| payment_id | bigint(20) | YES | UNI |
+-------------------+-------------+------+-----+
Payment
+--------------------------+---------------+------+-----+
| Field | Type | Null | Key |
+--------------------------+---------------+------+-----+
| id | int(10) | NO | PRI |
| paymentDate | timestamp | NO | MUL |
| pin | int(10) | NO | MUL |
| balanceChange | decimal(15,2) | YES | |
Claim
+------------------------+--------------+------+-----+
| Field | Type | Null | Key |
+------------------------+--------------+------+-----+
| id | int(11) | NO | PRI |
| fullName | varchar(100) | NO | |
| depositSum | blob | NO | |
| ip | varchar(39) | NO | |
| status | int(2) | NO | |
+------------------------+--------------+------+-----+
I try to select deposits (with claims) payment or reversePayment were between two dates, I perform this query with 3 joins:
EXPLAIN SELECT this_.id AS id60_3_, ..., fcpayment2_.id AS id59_0_, ..., reversepay3_.id AS id59_1_, ..., cl1_.id AS id61_2_, ...
FROM Deposit this_
INNER JOIN Payment fcpayment2_ ON this_.payment_id = fcpayment2_.id
LEFT OUTER JOIN Payment reversepay3_ ON this_.reversePayment_id = reversepay3_.id
INNER JOIN Claim cl1_ ON this_.claim_id = cl1_.id
WHERE (
(
fcpayment2_.paymentDate >= '2010-08-04 21:00:00'
AND fcpayment2_.paymentDate <= '2010-08-05 08:01:00'
)
OR (
reversepay3_.paymentDate >= '2010-08-04 21:00:00'
AND reversepay3_.paymentDate <= '2010-08-05 08:01:00'
)
)
ORDER BY this_.depositDate DESC
the result is
+----+-------------+--------------+--------+--------------------------------------------------------------------+----------+---------+-----------------------------------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+--------+--------------------------------------------------------------------+----------+---------+-----------------------------------------+--------+---------------------------------+
| 1 | SIMPLE | cl1_ | ALL | PRIMARY | NULL | NULL | NULL | 426588 | Using temporary; Using filesort |
| 1 | SIMPLE | this_ | eq_ref | claim_id,payment_id,FKDB5A0548511B6CDD,FKDB5A054867BA4108 | claim_id | 4 | portal.cl1_.id | 1 | |
| 1 | SIMPLE | fcpayment2_ | eq_ref | PRIMARY,paymentDate,date | PRIMARY | 4 | portal.this_.payment_id | 1 | Using where |
| 1 | SIMPLE | reversepay3_ | eq_ref | PRIMARY | PRIMARY | 4 | portal.this_.reversePayment_id | 1 | Using where |
+----+-------------+--------------+--------+--------------------------------------------------------------------+----------+---------+-----------------------------------------+--------+---------------------------------+
Why the first table in result is cl1_ and why mysql doesn't use key?