Hey All,
Usually don't have to resort to forums but could really use some advice here. On moving an updated version of our app over to one of our other servers which uses an older database, I came across what seems to be a problem between mysql 4 and 5.
For some reason this query:
Code:
SELECT bsm.bundle_sold_id,a.*,c.*
FROM appointments a LEFT JOIN customers c
ON a.customer_id=c.id
LEFT JOIN bundles_sold_members bsm
ON bsm.customer_id=c.id
WHERE a.salon_id = 6
AND svc_dt >= "2009-8-12"
AND svc_dt < "2009-8-13"
ORDER BY svc_dt;
ran just fine on mysql 5. But on mysql 4 it gives no results.
Running explain to see how these queries are being run yeilds:
Code:
+----+-------------+-------+--------+------------------------------------+-------------+---------+----------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+------------------------------------+-------------+---------+----------------------------+------+-------------+
| 1 | SIMPLE | a | range | appt_idx,employee_id,employee_id_2 | appt_idx | 16 | NULL | 1 | Using where |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 8 | floydware_db.a.customer_id | 1 | |
| 1 | SIMPLE | bsm | ref | customer_id | customer_id | 8 | floydware_db.c.id | 6 | Using index |
+----+-------------+-------+--------+------------------------------------+-------------+---------+----------------------------+------+-------------+
On mysql 5. Full length 16 index being used on first select.
On mysql 4, however...
Code:
+----+-------------+-------+--------+----------------------+----------+---------+----------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------------+----------+---------+----------------------------+------+----------------------------------------------+
| 1 | SIMPLE | a | ref | appt_idx,employee_id | appt_idx | 8 | const,const | 1 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 8 | floydware_db.a.customer_id | 1 | |
| 1 | SIMPLE | bsm | ALL | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+-------+--------+----------------------+----------+---------+----------------------------+------+----------------------------------------------+
Only 8 bytes of the first key are being used and the third query has type ALL and isn't using a ref.
Has the syntax on multiple left joins changed between the versions? Doesn't seem to me that this query is overly complicated. I tried another variant of it...
explain SELECT a.*,c.* FROM appointments a LEFT JOIN (customers c LEFT JOIN bundles_sold_members bsm ON bsm.customer_id=c.id) ON a.customer_id=c.id WHERE a.salon_id = 6 AND a.svc_dt >= "2009-8-12" AND a.svc_dt < "2009-8-13" ORDER BY a.svc_dt;
Code:
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------------------------------------------+
| 1 | SIMPLE | a | ref | appt_idx | appt_idx | 4 | const | 1 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | c | ALL | NULL | NULL | NULL | NULL | 482 | |
| 1 | SIMPLE | bsm | ALL | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------------------------------------------+
This time both customers and bundles_sold_members had a NULL ref. I'm guessing it has something to do with the placement of the second ON? Does mysql 4 have a problem with multiple left joins? Should I campaign to upgrade the database to version 5? I'd like to know if its fixable without resorting to that. Please let me know! Thanks!
dBforums
>
version 4 vs version 5
08-13-09, 15:12
Linear Mode
