Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2009
    Posts
    2

    Unanswered: version 4 vs version 5

    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!

  2. #2
    Join Date
    Aug 2009
    Posts
    2
    Just figured I'd post some follow up on this. We ended up upgrading the other server to version 5 and now things seem to be working fine.

    Still not sure what the problem was though. Anyone have thoughts on that? I'd love to hear em if you do. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •