我有两个巨大的表，从中我选择了大量的数据。
表存储采购订单详细信息和产品信息。

PURCHASE_ORDER_DETAILS.
    CREATE TABLE `PURCHASE_ORDER_DETAILS` (
    `PURCHASE_ORDER_NUMBER_PF` INT(20) NOT NULL,
    `PRODUCT_CODE_PF` VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
    `ORDER_QUANTITY` INT(8) DEFAULT NULL,
    `UNIT_PRICE` DECIMAL(12,2) DEFAULT NULL,
    `ORDER_FULLFILLMENT_DUE_DATE` DATETIME DEFAULT NULL,
    `DELIVERY_ADDRESS` VARCHAR(64) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    `DELIVERY_CITY` VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
     `DELIVERY_ZIP` BIGINT(10) DEFAULT NULL,
     `other columns`
      PRIMARY KEY (`PURCHASE_ORDER_NUMBER_PF`,`PRODUCT_CODE_PF`),
      KEY `RMAPWBTX_PUCH_ORDE_DLST_INDX` (`DELIVERY_STATE_ID_FK`),
      KEY `RMAPWBTX_PUCH_ORDE_DLTY_INDX` (`DELIVERY_TYPE_FK`),
      KEY `RMAPWBTX_PUCH_ORDE_TACO_INDX` (`TAX_CODE_FK`),
      KEY `RMAPWBMS_PUOR_DETL_PDCO_FK` (`PRODUCT_CODE_PF`),
      KEY `RMAPWBTX_PUOR_DETL_TACO_FK` (`TAX_CODE_FK`),
      KEY `CREATED_DATE_INDX` (`CREATED_DATE`),
      KEY `MODIFIED_DATE_INDX` (`MODIFIED_DATE`),
      CONSTRAINT `RMAPWBMS_PUOR_DETL_PDCO_FK` FOREIGN KEY (`PRODUCT_CODE_PF`) 
      REFERENCES `PRODUCT` (`PRODUCT_CODE_PK`) ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT `RMAPWBMS_PUOR_DETL_PONU_FK` FOREIGN KEY 
      (`PURCHASE_ORDER_NUMBER_PF`) REFERENCES `PURCHASE_ORDER` 
      (`PURCHASE_ORDER_NUMBER_PK`),
     CONSTRAINT `RMAPWBTX_PO_DETL_DSID_FK` FOREIGN KEY 
     (`DELIVERY_STATE_ID_FK`) REFERENCES `STATE` (`STATE_ID_PK`),
     CONSTRAINT `RMAPWBTX_PUOR_DETL_TACO_FK` FOREIGN KEY (`TAX_CODE_FK`) 
     REFERENCES `TAX` (`TAX_CODE_PK`) ON DELETE NO ACTION ON UPDATE NO ACTION
     ) ENGINE=INNODB DEFAULT CHARSET=latin1;

PRODUCT

    CREATE TABLE `PRODUCT` (
    `PRODUCT_CODE_PK` VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
    `PRODUCT_DESC` VARCHAR(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    `FEE_BILL_CODE` VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    `other columns`
     PRIMARY KEY (`PRODUCT_CODE_PK`),
     KEY `CREATED_DATE_INDX` (`CREATED_DATE`),
     KEY `MODIFIED_DATE_INDX` (`MODIFIED_DATE`),
     KEY `PRODUCT_EXCO_FK` (`EXPENSE_CODE_ID_FK`),
     KEY `FK_PRODUCT_ENTITY_TYPE` (`ENTITY_TYPE_CODE_FK`),
     CONSTRAINT `FK_PRODUCT_ENTITY_TYPE` FOREIGN KEY (`ENTITY_TYPE_CODE_FK`) REFERENCES `ENTITY_TYPE` (`ENTITY_TYPE_CODE_PK`)
     ) ENGINE=INNODB DEFAULT CHARSET=latin1

     Below query is taking ~10min to get ~1M records.

     EXPLAIN SELECT * FROM
     PURCHASE_ORDER_DETAILS POD
     JOIN PRODUCT PRD ON POD.PRODUCT_CODE_PF=PRD.PRODUCT_CODE_PK;
+----+-------------+-------+------+----------------------------+-----------------

    -----------+---------+-----------------------------------------------+-------+-------+
    | id | select_type | table | type | possible_keys              | key                        | key_len | ref                                           | rows  | Extra |
    +----+-------------+-------+------+----------------------------+----------------------------+---------+-----------------------------------------------+-------+-------+
    |  1 | SIMPLE      | PRD   | ALL  | PRIMARY                    | NULL                       | NULL    | NULL                                          | 14283 | NULL  |
    |  1 | SIMPLE      | POD   | ref  | RMAPWBMS_PUOR_DETL_PDCO_FK | RMAPWBMS_PUOR_DETL_PDCO_FK | 34      | REALREMIT_PROD_ALTISOURCE.PRD.PRODUCT_CODE_PK |    40 | NULL  |
    +----+-------------+-------+------+----------------------------+----------------------------+---------+-----------------------------------------------+-------+---

edit1：上面的查询是一个示例，下面是实际的查询，我试图在其中获取1m条记录（主表pod有22m条记录）。

SELECT `some columns`
FROM `REALREMIT_PPIPFC_MIG`.MIGR_ORDER_DENORM MPO
INNER JOIN PURCHASE_ORDER_DETAILS POD 
ON MPO.PURCHASE_ORDER_NUMBER_PK=POD.PURCHASE_ORDER_NUMBER_PF
INNER JOIN PRODUCT PRD 
ON POD.PRODUCT_CODE_PF=PRD.PRODUCT_CODE_PK 
INNER JOIN EXPENSE_CODE EXP
ON PRD.EXPENSE_CODE_ID_FK=EXP.EXPENSE_CODE_ID_PK 
WHERE MPO.BATCH_ID=1;

解释上述查询的输出

+----+-------------+-------+--------+-------------------------------------+----------------------------+---------+--------------------------------------------------------+-------+-------------+
| id | select_type | table | type   | possible_keys                       | key                        | key_len | ref                                                    | rows  | Extra       |
+----+-------------+-------+--------+-------------------------------------+----------------------------+---------+--------------------------------------------------------+-------+-------------+
|  1 | SIMPLE      | PRD   | ALL    | PRIMARY,PRODUCT_EXCO_FK             | NULL                       | NULL    | NULL                                                   | 14283 | NULL        |
|  1 | SIMPLE      | EXP   | eq_ref | PRIMARY                             | PRIMARY                    | 4       | REALREMIT_PROD_ALTISOURCE.PRD.EXPENSE_CODE_ID_FK       |     1 | NULL        |
|  1 | SIMPLE      | POD   | ref    | PRIMARY,RMAPWBMS_PUOR_DETL_PDCO_FK  | RMAPWBMS_PUOR_DETL_PDCO_FK | 34      | REALREMIT_PROD_ALTISOURCE.PRD.PRODUCT_CODE_PK          |    40 | NULL        |
|  1 | SIMPLE      | MPO   | ref    | MIGR_PO_NBR_INDX,MIGR_BATCH_ID_INDX | MIGR_PO_NBR_INDX           | 4       | REALREMIT_PROD_ALTISOURCE.POD.PURCHASE_ORDER_NUMBER_PF |     1 | Using where |
+----+-------------+-------+--------+-------------------------------------+----------------------------+---------+--------------------------------------------------------+-------+-------------+
4 rows in set (0.20 sec)

两个数据库在join中使用的字符集列具有相同的collate两个表具有相同的字符集
我已经创建了一个新的表，其中主键是连接的列-purhcase\ order\ number\ pf和product\ code\ pf，然后我在product\ code\ pf上添加了一个新的索引。在这种情况下，将使用index和/或这是在join中使用index的最佳方法。
谢谢