MySQL explain anomaly

Question

Consider the following query:

select FEE_NUMBER
from CARRIER_FEE CF
left outer join CONTYPE_FEE_LIST cfl on CF.CAR_FEE_ID=cfl.CAR_FEE_ID and cfl.CONT_TYPE_ID=3
where CF.SEQ_NO = (
    select max(CF2.SEQ_NO) from CARRIER_FEE CF2 
    where CF2.FEE_NUMBER=CF.FEE_NUMBER 
    and CF2.COMPANY_ID=CF.COMPANY_ID 
    group by CF2.FEE_NUMBER) 
group by CF.CAR_FEE_ID

On my laptop this returns no results. Using exactly the same (dumped) database on my servers it does return results.

If I run an EXPLAIN on my laptop I get this

| id | select_type        | table | type  | possible_keys                               | key                   | key_len | ref                    | rows | Extra                                        |
+----+--------------------+-------+-------+---------------------------------------------+-----------------------+---------+------------------------+------+----------------------------------------------+
|  1 | PRIMARY            | CF    | index | NULL                                        | PRIMARY               | 8       | NULL                   |  132 | Using where                                  | 
|  1 | PRIMARY            | cfl   | ref   | FK_CONTYPE_FEE_LIST_1,FK_CONTYPE_FEE_LIST_2 | FK_CONTYPE_FEE_LIST_1 | 8       | odysseyB.CF.CAR_FEE_ID |    6 |                                              | 
|  2 | DEPENDENT SUBQUERY | CF2   | ref   | FK_SURCHARGE_1                              | FK_SURCHARGE_1        | 8       | func                   |   66 | Using where; Using temporary; Using filesort |

Whereas on all of my other servers it gives this (note the difference in the ref column)

| id | select_type        | table | type  | possible_keys                               | key                   | key_len | ref                    | rows | Extra                                        |
+----+--------------------+-------+-------+---------------------------------------------+-----------------------+---------+------------------------+------+----------------------------------------------+
|  1 | PRIMARY            | CF    | index | NULL                                        | PRIMARY               | 8       | NULL                   |  132 | Using where                                  | 
|  1 | PRIMARY            | cfl   | ref   | FK_CONTYPE_FEE_LIST_1,FK_CONTYPE_FEE_LIST_2 | FK_CONTYPE_FEE_LIST_1 | 8       | odysseyB.CF.CAR_FEE_ID |    6 |                                              | 
|  2 | DEPENDENT SUBQUERY | CF2   | ref   | FK_SURCHARGE_1                              | FK_SURCHARGE_1        | 8       | odysseyB.CF.COMPANY_ID |   66 | Using where; Using temporary; Using filesort |

If I remove either the join, the subquery or the last group-by then I get the expected results.

I'm assuming that this is a configuration issue, however it's not one that I've seen before. Does anybody know what might cause this?

My laptop is running OSX 10.6 with MySQL 5.0.41. Another laptop running OSX 10.5.7 and MySQL 5.0.37 works fine, as do the Linux servers running MySQL 5.0.27.

Can anyone explain the difference between one explain plan using ref=func and the other using ref=odysseyB.CF.COMPANY_ID?

Thanks.

Answer 1

I don't know why it's giving different results. You don't have exactly the same data dump, since the row counts reported in your EXPLAIN reports are different. I'd recommend doing some simpler queries to test your assumptions.

Also double-check that you're really executing the exact same SQL query on both servers. For instance, if you inadvertently changed your left outer join to an inner join, that could make the whole query return no results.

BTW, tangential to your question but I solve these "greatest row per group" types of queries with an outer join:

select FEE_NUMBER
from CARRIER_FEE CF
left outer join CARRIER_FEE CF2
  on CF.FEE_NUMBER = CF2.FEE_NUMBER and CF.COMPANY_ID = CF.COMPANY_ID 
     and CF.SEQ_NO < cf2.SEQ_NO
left outer join CONTYPE_FEE_LIST cfl 
  on CF.CAR_FEE_ID=cfl.CAR_FEE_ID and cfl.CONT_TYPE_ID=3
where CF2.SEQ_NO IS NULL 
group by CF.CAR_FEE_ID;

This type of solution is often much faster than the correlated subquery solution you're currently using. I wouldn't think that could change the result of the query, I'm just offering it as an option.

Answer 2

On both machines:

mysql> SHOW CREATE TABLE CARRIER_FEE CF;

Make sure that both table ENGINE types are the same.

Also, since you are using OS X 10.6 on the machine having the error? Perhaps the data types on that OS have different qualities than 10.5.x.

Seems like people are having compatibility problems with snow leopard. Try installing MySQL 5.4 on your 10.6 laptop.

http://forums.mysql.com/read.php?10,278942,278942#msg-278942