views:

42

answers:

1
+1  Q: 

mysql three joins

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?

A: 

Because you used the keyword 'Explain', and because cl1_ is the alias you gave the table in your query.

I don't understand your question about the key.

Evernoob
I think mysql should 1. select records from this_ 2. then using cl1_.id (PK) select records from cl1_isn't it?
Anev