does int(11) and int(6) cause index issue?

Question

I'm new to MySQL. So I found someone wrote some SQL in our production environment:

SELECT o.`erp_orders_id`
FROM `erp_orders` o
WHERE o.`orders_status`> 2
  AND o.`orders_status`< 5
  AND o.`shipmentAutoMatched` IN
    (SELECT s.`shipmentID`
     FROM `erp_shipment` s
     WHERE s.`shipmentScanLocal` = 2)

where s.shipmentID is int(11) while o.shipmentAutoMatched is int(6).
This query hits an index

IDX_OR_OR_CU(orders_status, orders_type, currency_type)

While I actually have another index:

IDX_OR_SH(orders_status, shipmentAutoMatched)

which I think could be more efficient. and if I change the IN directive to numbers like:

IN(10, 11, 12)

the IDX_OR_SH is correctly hit. so I think the only problem might be about the int(11) and int(6).

So the questions:

1. Am I correct?
2. How does this issue happen when int(11) and int(6) are both int and they are actually both stored as an INT32
3. How can I resolve this issue?

Answer 1

For everyone who cares about the problem, this is probably a bug of InnoDB since 10 years ago. And it's supposed to get fixed in MySQL 6.0. Unfortunately I'm still using 5.5.

It has nothing to do with int(11) and int(6). Simply all subquery result won't hit parent query's index. Some people says change to JOIN statement would resolve this issue, but it doesn't seem to work for me though.

I will get back and update the answer when I get something new.