Início / dba / Perguntas / 53203
Accepted
Van
Asked: 2013-11-13 11:18:59 +0800 CST

MySQL 5.6.14 não usará chave/índice apropriado na consulta

  • 772

SO: ubuntu 12.04 amd64
DB: MySQL 5.6.14-community amd64

Não consigo entender por que essa consulta está usando o índice errado e fazendo uma verificação completa da tabela na tabela ar_batch (ab). Talvez alguém possa ver por quê!?

Consulta

SELECT
  DATE_FORMAT(ab.deposit_date, '%m/%d/%Y') AS paymentDate,
  FORMAT(pc.received_amt, 2) AS paymentAmount,
  pc.check_no AS paymentReference
FROM
  TRANSACTION ar 
JOIN payments py 
  ON (
    ar.transaction_id = py.ar_tranaction_id 
    AND py.ar_tranaction_id NOT LIKE '%AC'
  ) 
JOIN ar_batch ab
  ON (ab.batch_id = py.batch_id)
JOIN payment_checks pc 
  ON (py.payment_check_id = pc.id) 
WHERE ar.transaction_type = 'AR' 
AND ar.cust_no = 'SEACOR0003' 
ORDER BY py.id DESC 
LIMIT 1;

EXPLICAR ESTENDIDO

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "filtered"  "Extra"
"1" "SIMPLE"    "ab"    "index" "PRIMARY"   "Deposit_date_idx"  "4" \N  "34836" "100.00"    "Using index; Using temporary; Using filesort"
"1" "SIMPLE"    "py"    "ref"   "Batch_Id,Ar_Tranaction_Id,Payment_Check_Id"    "Batch_Id"  "5" "eci_lw_prod.ab.Batch_id"   "10"    "100.00"    "Using where"
"1" "SIMPLE"    "ar"    "eq_ref"    "PRIMARY,cust_no_idx,Trasns_type_idx"   "PRIMARY"   "4" "eci_lw_prod.py.Ar_Tranaction_Id"   "1" "100.00"    "Using where"
"1" "SIMPLE"    "pc"    "eq_ref"    "PRIMARY"   "PRIMARY"   "4" "eci_lw_prod.py.Payment_Check_Id"   "1" "100.00"    \N

Abaixo estão os CREATE TABLEs....

CREATE TABLE `ar_batch` (
`Batch_id` int(11) NOT NULL AUTO_INCREMENT,
`User_id` varchar(30) NOT NULL,
`Date` datetime NOT NULL,
`Deposit_date` date DEFAULT NULL,
`Total_Amount` decimal(10,2) DEFAULT NULL,
`Bank_Account` varchar(20) DEFAULT NULL,
`Notes` varchar(200) DEFAULT NULL,
`Status` varchar(15) NOT NULL,
`Reconciled` varchar(1) DEFAULT NULL,
`Applied_Amount` decimal(10,2) DEFAULT NULL,
`Balance_Amount` decimal(10,2) NOT NULL,
`On_Acct_Amount` decimal(10,2) DEFAULT NULL,
`Prepay_Amount` decimal(10,2) DEFAULT NULL,
`BankAccount_Desc` varchar(200) DEFAULT NULL,
`Gl_Account` varchar(50) DEFAULT NULL,
`batch_type` varchar(1) DEFAULT NULL,
`Adjust_Amount` decimal(10,2) DEFAULT '0.00',
`Using_By` int(11) DEFAULT NULL,
`direct_gl_account` tinyint(1) DEFAULT '0',
 PRIMARY KEY (`Batch_id`),
 KEY `Deposit_date_idx` (`Deposit_date`),
 KEY `status_idx` (`Status`),
 KEY `batch_type_idx` (`batch_type`),
 KEY `applied_amount_idx` (`Applied_Amount`)
) ENGINE=InnoDB AUTO_INCREMENT=44953 DEFAULT CHARSET=latin1;


CREATE TABLE `payments` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Batch_Id` int(11) DEFAULT NULL,
`Batch_Date` date DEFAULT NULL,
`Cust_No` varchar(10) DEFAULT NULL,
`Check_Date` date DEFAULT NULL,
`Payment_Amt` decimal(10,2) DEFAULT NULL,
`Payment_Type` varchar(10) DEFAULT NULL,
`Charge_Code` varchar(10) DEFAULT NULL,
`Ar_Tranaction_Id` varchar(15) DEFAULT NULL,
`invoice_no` varchar(100) DEFAULT NULL,
`Bill_Ladding_No` varchar(30) DEFAULT NULL,
`Adjustment_Amt` decimal(10,2) DEFAULT NULL,
`Check_no` varchar(30) DEFAULT NULL,
`adjustment_date` date DEFAULT NULL,
`userName` varchar(20) DEFAULT NULL,
`Payment_Check_Id` int(12) DEFAULT NULL,
`notes` varchar(200) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `Batch_Id` (`Batch_Id`),
KEY `Cust_No` (`Cust_No`),
KEY `Ar_Tranaction_Id` (`Ar_Tranaction_Id`),
KEY `Payment_Check_Id` (`Payment_Check_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=4118743 DEFAULT CHARSET=latin1;

CREATE TABLE `transaction` (
 `Transaction_ID` int(10) NOT NULL AUTO_INCREMENT,
 `Bill_Ladding_No` varchar(42) DEFAULT NULL,
 `Charge_Code` varchar(20) DEFAULT NULL,
 `Transaction_date` date DEFAULT NULL,
 `posted_date` datetime DEFAULT NULL,
 `GL_account_number` varchar(20) DEFAULT NULL,
 `Transaction_amt` decimal(10,2) DEFAULT NULL,
 `Currency_code` varchar(100) DEFAULT NULL,
 `invoice_number` varchar(100) DEFAULT NULL,
 `Cheque_number` varchar(150) DEFAULT NULL,
 `Subledger_Source_code` varchar(20) DEFAULT NULL,
 `Journal_Entry_number` varchar(20) DEFAULT NULL,
 `Line_Item_number` varchar(20) DEFAULT NULL,
 `cust_name` varchar(60) DEFAULT NULL,
 `cust_no` varchar(30) DEFAULT NULL,
 `Transaction_type` varchar(10) DEFAULT NULL,
 `Balance` decimal(10,2) DEFAULT NULL,
 `BL_Terms` varchar(30) DEFAULT NULL,
 `Sub_House_BL` varchar(30) DEFAULT NULL,
 `Voyage_No` varchar(30) DEFAULT NULL,
 `Container_No` varchar(500) DEFAULT NULL,
 `Master_BL` varchar(30) DEFAULT NULL,
 `Vessel_No` varchar(30) DEFAULT NULL,
 `Status` varchar(20) DEFAULT NULL,
 `Due_Date` date DEFAULT NULL,
 `Bill_to` char(1) DEFAULT NULL,
 `Customer_Reference_No` varchar(500) DEFAULT NULL,
 `Originating_Terminal` varchar(50) DEFAULT NULL,
 `Destination` varchar(60) DEFAULT NULL,
 `Saling_date` date DEFAULT NULL,
 `Fwd_name` varchar(50) DEFAULT NULL,
 `Fwd_no` varchar(10) DEFAULT NULL,
 `Cons_name` varchar(100) DEFAULT NULL,
 `Cons_no` varchar(10) DEFAULT NULL,
 `Third_Pty_name` varchar(50) DEFAULT NULL,
 `Third_Pty_no` varchar(10) DEFAULT NULL,
 `Agent_name` varchar(50) DEFAULT NULL,
 `Agent_no` varchar(10) DEFAULT NULL,
 `Credit_Hold` varchar(20) DEFAULT NULL,
 `correction_flag` varchar(4) DEFAULT NULL,
 `Credit_Terms` int(4) DEFAULT NULL,
 `bank_no` varchar(10) DEFAULT NULL,
 `bank_name` varchar(50) DEFAULT NULL,
 `bank_account_no` varchar(30) DEFAULT NULL,
 `check_date` date DEFAULT NULL,
 `cleared` varchar(1) DEFAULT NULL,
 `cleared_date` date DEFAULT NULL,
 `reconciled` varchar(1) DEFAULT NULL,
 `reconciled_date` date DEFAULT NULL,
 `confirmation_number` varchar(30) DEFAULT NULL,
 `void` varchar(1) DEFAULT NULL,
 `void_date` date DEFAULT NULL,
 `reprint` varchar(1) DEFAULT NULL,
 `reprint_date` date DEFAULT NULL,
 `Balance_In_Process` decimal(10,2) DEFAULT NULL,
 `shipper_no` varchar(20) DEFAULT NULL,
 `shipper_name` varchar(50) DEFAULT NULL,
 `booking_no` varchar(30) DEFAULT NULL,
 `quotation_no` varchar(20) DEFAULT NULL,
 `Ap_Batch_Id` int(11) DEFAULT NULL,
 `Ar_Batch_Id` int(11) DEFAULT NULL,
 `pay_method` varchar(20) DEFAULT NULL,
 `drcpt` varchar(30) DEFAULT NULL,
 `Created_On` datetime DEFAULT NULL,
 `Created_By` int(6) DEFAULT NULL,
 `Updated_On` datetime DEFAULT NULL,
 `Updated_By` int(6) DEFAULT NULL,
 `Paid_By` int(6) DEFAULT NULL,
 `Approved_By` int(6) DEFAULT NULL,
 `Closed_Date` date DEFAULT NULL,
 `ach_batch_sequence` int(7) DEFAULT NULL,
 `manifest_flag` varchar(4) DEFAULT NULL,
 `correction_notice` varchar(10) DEFAULT NULL,
 `owner` int(6) DEFAULT NULL,
 `paid_on` datetime DEFAULT NULL,
 `emailed` tinyint(1) DEFAULT '0',
 `seal_no` varchar(1000) DEFAULT NULL,
 `eta` date DEFAULT NULL,
 `vessel_name` varchar(500) DEFAULT NULL,
 `steam_ship_line` varchar(35) DEFAULT NULL,
 `removed_from_hold` tinyint(1) NOT NULL DEFAULT '0',
 `search_invoice_number` varchar(100) DEFAULT NULL,
 `ap_invoice_id` int(11) DEFAULT NULL,
 `ap_invoice_status` varchar(30) DEFAULT NULL,
 `ap_invoice_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`Transaction_ID`),
KEY `cust_name_idx` (`cust_name`),
KEY `cust_no_idx` (`cust_no`),
KEY `Trasns_type_idx` (`Transaction_type`),
KEY `bill_ladding_idx` (`Bill_Ladding_No`),
KEY `Invoice_number` (`invoice_number`),
KEY `Status_IDX` (`Status`),
KEY `check_number_idx` (`Cheque_number`),
KEY `ap_batch_id_idx` (`Ap_Batch_Id`),
KEY `cleared_idx` (`cleared`),
KEY `reconciled_idx` (`reconciled`),
KEY `void_idx` (`void`),
KEY `void_date_idx` (`void_date`),
KEY `pay_method_idx` (`pay_method`),
KEY `drcpt_idx` (`drcpt`),
KEY `balance_idx` (`Balance`),
KEY `voyage_no_idx` (`Voyage_No`),
KEY `charge_code_idx` (`Charge_Code`),
KEY `container_no_idx` (`Container_No`),
KEY `transaction_amt_idx` (`Transaction_amt`),
KEY `balance_in_process_idx` (`Balance_In_Process`),
KEY `search_invoice_number_idx` (`search_invoice_number`),
KEY `ap_invoice_id_idx` (`ap_invoice_id`),
KEY `ap_invoice_status_idx` (`ap_invoice_status`),
KEY `created_by_idx` (`Created_By`)
) ENGINE=InnoDB AUTO_INCREMENT=1251542 DEFAULT CHARSET=latin1;

CREATE TABLE `payment_checks` (
 `Id` int(11) NOT NULL AUTO_INCREMENT,
 `Batch_id` int(11) DEFAULT NULL,
 `Batch_date` date DEFAULT NULL,
 `Cust_id` varchar(10) DEFAULT NULL,
 `Check_no` varchar(20) DEFAULT NULL,
 `Check_date` date DEFAULT NULL,
 `Received_Amt` decimal(10,2) DEFAULT NULL,
 `applied_amount` double(10,2) DEFAULT NULL,
 `On_Acct_In` decimal(10,2) DEFAULT '0.00',
 `Pre_Pmt_In` decimal(10,2) DEFAULT '0.00',
 `Invoice_Out` decimal(10,2) DEFAULT '0.00',
 `On_Acct_Out` decimal(10,2) DEFAULT '0.00',
 `Pre_Pmt_Out` decimal(10,2) DEFAULT '0.00',
 `Charge_Code_Out` decimal(10,2) DEFAULT '0.00',
 `Invoice_In` decimal(10,2) DEFAULT '0.00',
 `Adjust_Amt` decimal(10,2) DEFAULT '0.00',
 PRIMARY KEY (`Id`),
 KEY `Batch_id` (`Batch_id`),
 KEY `Check_no` (`Check_no`),
 KEY `Cust_id` (`Cust_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=203272 DEFAULT CHARSET=latin1;
mysql-5.6

1 respostas

  1. Best Answer

    Esta parte parece estranha, para dizer o mínimo:

    ON (
   ar.transaction_id = py.ar_tranaction_id 
   AND py.ar_tranaction_id NOT LIKE '%AC'
)

    Você está juntando um INTcom uma VARCHARcoluna, então qualquer esperança de eficiência é perdida ali mesmo. Corrija uma (ou ambas) colunas para que seus tipos de dados correspondam.

    Pelo mesmo motivo, o py.ar_tranaction_id NOT LIKE '%AC'é redundante, portanto pode ser removido, mesmo antes ou se você não alterar os tipos de dados. Nenhum valor nessa coluna pode ser ambos LIKE '%AC'e igual a um número inteiro.

    • 1

relate perguntas