Geek Answers Handbook

Int (11) and int (6) cause an index problem?

I am new to MySQL. So I found that 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) and o.shipmentAutoMatched is int(6) .
This query is indexed.

 IDX_OR_OR_CU(orders_status, orders_type, currency_type)

So far I have another index:

 IDX_OR_SH(orders_status, shipmentAutoMatched)

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

 IN(10, 11, 12)

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

So the questions are:

  • Am I right?
  • How does this problem occur when int(11) and int(6) are both int and they are actually saved as INT32
  • How can I solve this problem?
+6
source share
4 answers

For anyone who cares about the problem, this is probably InnoDB's mistake 10 years ago. And it should be fixed in MySQL 6.0. Unfortunately, I'm still using 5.5.

This has nothing to do with int (11) and int (6). Simply, all the results of the subquery will not affect the index of parental queries. Some people say that changing the JOIN instruction would solve this problem, but it doesn't seem to work for me.

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

+1
source

You use the index for the subquery, but lose it after passing it to In

Rewrite this query as a connection and you will see speed improvements. Especially in older versions of mysql (newer versions have more efficient subqueries.)

Mysql knows pretty well which index to use. And if he decided that one of them is better than the other. Mysql is usually correct. You can check with FORCE INDEX. make sure you use this on both smaller and larger datasets to get a true image, though

0
source

rewrite your request with jOIN

 SELECT o.`erp_orders_id` FROM `erp_orders` o INNER JOIN `erp_shipment` s ON (s.`shipmentID` = o.`shipmentAutoMatched` AND s.`shipmentScanLocal` = 2) WHERE o.`orders_status`> 2 AND o.`orders_status`< 5
0
source

I do not think the problem is with int(11) and int(6) . * * in INT(*) has nothing to do with space requirements or other performance issues, it's really just the width of the screen . This will actually be more useful with the UNSIGNED ZEROFILL option. for instance

 INT(6) UNSIGNED ZEROFILL 000001 000011 ------ INT(6) 1 11

So, I think the problem should be somewhere else

0
source

More articles:


All Articles