Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

I cannot figure out why this query is using the wrong index and doing a full table scan on the ar_batch (ab) table. Perhaps someone can see why!?

Query

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;

EXPLAIN EXTENDED

"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

Below are the 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;
share|improve this question
    
Can you check if the execution plan is different if you replace JOIN ar_batch ab with LEFT JOIN ar_batch ab? –  ypercube Nov 12 '13 at 19:32
    
yes, the execution does change by doing a LEFT JOIN. It's worse - no index at all. –  Van Nov 12 '13 at 20:12
    
Please add the CREATE statements for the other 2 tables as well. –  ypercube Nov 12 '13 at 20:16
    
Added the two other tables. –  Van Nov 14 '13 at 14:23

1 Answer 1

up vote 1 down vote accepted

This part seems weird, to say the least:

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

You are joining an INT with a VARCHAR column so any hope for efficiency is lost right there. Correct one (or both) columns so their datatypes match.

For the same reason, the py.ar_tranaction_id NOT LIKE '%AC' is redundant so it can be removed, even before or if you don't change the datatypes. No value in that column can be both LIKE '%AC' and equal to an integer.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.