2

I have a slow subquery as below (taking 2 min 35.03 seconds)

SELECT *
FROM erp_gl_batch_item i
LEFT JOIN erp_gl_batch b
  ON i.gl_batch_id = b.gl_batch_id
LEFT JOIN erp_customer c
  ON i.gl_item_account_no = c.customer_code
LEFT JOIN (SELECT *
    FROM erp_acc_match_invoice mi
    WHERE mi.allocation = 'full') mi
      ON i.gl_reference = mi.reference
LEFT JOIN (SELECT *
    FROM erp_acc_match_invoice mip
    WHERE mip.allocation = 'partial') mip
      ON i.gl_reference = mip.reference
WHERE i.gl_item_account_no LIKE '3001/A01'
AND mip.match_invoice_id IS NULL
AND b.gl_period <= '2016012'
AND i.gl_item_debit <> '0.00'
AND mi.match_invoice_id IS NULL;
89 rows in set (2 min 35.03 sec)

Below is the explain output.

+----+-------------+------------+--------+--------------------------------+--------------------+---------+------------------------------------+-------+-------------------------+
| id | select_type | table      | type   | possible_keys                  | key                | key_len | ref                                | rows  | Extra                   |
+----+-------------+------------+--------+--------------------------------+--------------------+---------+------------------------------------+-------+-------------------------+
|  1 | PRIMARY     | i          | range  | gl_item_account_no,gl_batch_id | gl_item_account_no | 767     | NULL                               |  4713 | Using where             |
|  1 | PRIMARY     | b          | eq_ref | PRIMARY                        | PRIMARY            | 4       | c1xxxtemp_erp.i.gl_batch_id        |     1 | Using where             |
|  1 | PRIMARY     | c          | ref    | customer_code                  | customer_code      | 767     | c1xxxtemp_erp.i.gl_item_account_no |     1 |                         |
|  1 | PRIMARY     | <derived2> | ALL    | NULL                           | NULL               | NULL    | NULL                               | 28965 | Using where; Not exists |
|  1 | PRIMARY     | <derived3> | ALL    | NULL                           | NULL               | NULL    | NULL                               |   149 | Using where; Not exists |
|  3 | DERIVED     | mip        | ref    | allocation                     | allocation         | 33      |                                    |   149 | Using where             |
|  2 | DERIVED     | mi         | ref    | allocation                     | allocation         | 33      |                                    | 15306 | Using where             |
+----+-------------+------------+--------+--------------------------------+--------------------+---------+------------------------------------+-------+-------------------------+

Just want to know how the subquery & join works and how does it affect the performance of the query. Also, Is it because of the join it needs to look for row 4,713 x 28,965 = 136,512,045 row?

I have rewritten the query and it is faster now (0.12 sec), just wonder it is subquery as well, but how does this subquery work faster.

SELECT *
FROM erp_gl_batch_item i
LEFT JOIN erp_gl_batch b
  ON i.gl_batch_id = b.gl_batch_id
LEFT JOIN erp_customer c
  ON i.gl_item_account_no = c.customer_code
WHERE i.gl_item_account_no LIKE '3001/A01'
AND b.gl_period <= '2016012'
AND i.gl_item_debit <> '0.00'
AND NOT EXISTS (SELECT *
    FROM erp_acc_match_invoice mip
    WHERE mip.allocation = 'partial'
    AND i.gl_reference = mip.reference)
AND NOT EXISTS (SELECT *
    FROM erp_acc_match_invoice mi
    WHERE mi.allocation = 'full'
    AND i.gl_reference = mi.reference)

Explain

+----+--------------------+-------+--------+--------------------------------+--------------------+---------+------------------------------------+------+-------------+
| id | select_type        | table | type   | possible_keys                  | key                | key_len | ref                                | rows | Extra       |
+----+--------------------+-------+--------+--------------------------------+--------------------+---------+------------------------------------+------+-------------+
|  1 | PRIMARY            | i     | range  | gl_item_account_no,gl_batch_id | gl_item_account_no | 767     | NULL                               | 4713 | Using where |
|  1 | PRIMARY            | b     | eq_ref | PRIMARY                        | PRIMARY            | 4       | c1xxxtemp_erp.i.gl_batch_id        |    1 | Using where |
|  1 | PRIMARY            | c     | ref    | customer_code                  | customer_code      | 767     | c1xxxtemp_erp.i.gl_item_account_no |    1 |             |
|  3 | DEPENDENT SUBQUERY | mi    | ref    | reference,allocation           | reference          | 767     | c1xxxtemp_erp.i.gl_reference       |    1 | Using where |
|  2 | DEPENDENT SUBQUERY | mip   | ref    | reference,allocation           | reference          | 767     | c1xxxtemp_erp.i.gl_reference       |    1 | Using where |
+----+--------------------+-------+--------+--------------------------------+--------------------+---------+------------------------------------+------+-------------+

Update

CREATE TABLE `erp_gl_batch_item` (
  `gl_batch_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `gl_batch_id` int(11) NOT NULL,
  `gl_line_item` int(11) NOT NULL,
  `gl_item_date` date NOT NULL,
  `gl_item_account_id` int(11) DEFAULT NULL,
  `gl_item_account_no` varchar(255) NOT NULL,
  `gl_reference` varchar(255) NOT NULL,
  `gl_reference2` varchar(255) DEFAULT NULL,
  `gl_tax_code` int(11) DEFAULT NULL,
  `gl_desc` varchar(255) NOT NULL,
  `gl_foreign_currency` varchar(10) DEFAULT NULL,
  `currency_id` int(11) DEFAULT NULL,
  `gl_rate` varchar(10) DEFAULT NULL,
  `gl_item_foreign_amount_debit` decimal(10,2) DEFAULT '0.00',
  `gl_item_foreign_amount_credit` decimal(10,2) NOT NULL DEFAULT '0.00',
  `gl_item_debit` decimal(10,2) DEFAULT '0.00',
  `gl_item_credit` decimal(10,2) DEFAULT '0.00',
  `knockoff` varchar(40) DEFAULT NULL,
  `marked` varchar(10) DEFAULT NULL,
  `gst_log_id` int(11) NOT NULL DEFAULT '0',
  `tax_group_key` int(11) NOT NULL DEFAULT '0',
  `user_created` int(11) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `user_modified` int(11) DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`gl_batch_item_id`),
  KEY `gl_item_account_id` (`gl_item_account_id`),
  KEY `gl_reference` (`gl_reference`),
  KEY `knockoff` (`knockoff`),
  KEY `marked` (`marked`),
  KEY `gl_item_account_no` (`gl_item_account_no`),
  KEY `gl_batch_id` (`gl_batch_id`)
) ENGINE=InnoDB AUTO_INCREMENT=208712 DEFAULT CHARSET=utf8 


CREATE TABLE `erp_gl_batch` (
  `gl_batch_id` int(11) NOT NULL AUTO_INCREMENT,
  `gl_batch_no` int(11) NOT NULL,
  `gl_batch_name` varchar(255) NOT NULL,
  `gl_period` int(11) DEFAULT NULL,
  `gl_bill_type` int(11) NOT NULL,
  `gl_date_start` date NOT NULL,
  `gl_date_end` date NOT NULL,
  `user_created` int(11) DEFAULT NULL,
  `user_modified` int(11) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`gl_batch_id`)
) ENGINE=InnoDB AUTO_INCREMENT=282 DEFAULT CHARSET=utf8

CREATE TABLE `erp_customer` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_code` varchar(255) NOT NULL,
  `customer_name` varchar(255) NOT NULL,
  `company_reg_no` varchar(255) DEFAULT NULL,
  `billing_address` text,
  `shipping_address` text,
  `contact_name` varchar(255) DEFAULT NULL,
  `contact_phone` varchar(255) DEFAULT NULL,
  `contact_email` varchar(255) DEFAULT NULL,
  `contact_fax` varchar(255) DEFAULT NULL,
  `contact_website` varchar(255) DEFAULT NULL,
  `currency_id` int(11) DEFAULT NULL,
  `credit_term_id` int(11) DEFAULT NULL,
  `credit_limit` decimal(16,4) DEFAULT NULL,
  `gst_no` varchar(255) DEFAULT NULL,
  `tax_code_id` int(11) DEFAULT NULL,
  `po_line_required` tinyint(1) DEFAULT NULL,
  `freeze_account` int(11) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `status_id` int(11) DEFAULT NULL,
  `remarks` text,
  `user_created` int(11) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `user_modified` int(11) DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL
  PRIMARY KEY (`customer_id`),
  KEY `customer_code` (`customer_code`)
) ENGINE=InnoDB AUTO_INCREMENT=1072 DEFAULT CHARSET=utf8

CREATE TABLE `erp_acc_match_invoice` (
  `match_invoice_id` int(11) NOT NULL AUTO_INCREMENT,
  `log_id` int(11) NOT NULL,
  `chartaccount_id` int(11) NOT NULL,
  `batch_item_id` int(11) NOT NULL,
  `inv_date` date NOT NULL DEFAULT '0000-00-00',
  `reference` varchar(255) NOT NULL,
  `pay_reference` varchar(255) DEFAULT NULL,
  `mi_currency` varchar(10) DEFAULT NULL,
  `mi_rate` varchar(10) DEFAULT NULL,
  `mi_amount` decimal(10,2) NOT NULL,
  `inv_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `allocation` varchar(10) DEFAULT NULL,
  `user_created` int(11) NOT NULL,
  `date_created` datetime NOT NULL,
  `rvknockoff` varchar(40) DEFAULT NULL,
  `inknockoff` varchar(40) DEFAULT NULL,
  `table_location` varchar(10) NOT NULL,
  PRIMARY KEY (`match_invoice_id`),
  KEY `rvknockoff` (`rvknockoff`),
  KEY `inknockoff` (`inknockoff`),
  KEY `reference` (`reference`),
  KEY `pay_reference` (`pay_reference`),
  KEY `log_id` (`log_id`),
  KEY `allocation` (`allocation`)
) ENGINE=InnoDB AUTO_INCREMENT=37368 DEFAULT CHARSET=utf8
11
  • Is (match_invoice_id) the PK of that table? Dec 30 '16 at 10:47
  • Do you really need c? Does it need to be LEFT?
    – Rick James
    Dec 30 '16 at 20:47
  • What percent of the table has gl_item_account_no LIKE '3001/A01'?
    – Rick James
    Dec 30 '16 at 20:47
  • Please provide SHOW CREATE TABLE.
    – Rick James
    Dec 30 '16 at 20:47
  • @TypoCubeᵀᴹ yes, match_invoice_id is primary key. @RickJames I prefer using LEFT incase customer accidentally delete the customer data in erp_customer table.
    – cww
    Dec 31 '16 at 11:29
2

You don't need to have these as subqueries (derived tables). The first for example:

LEFT JOIN (SELECT *
    FROM erp_acc_match_invoice mi
    WHERE mi.allocation = 'full') mi
      ON i.gl_reference = mi.reference

can be rewritten as:

LEFT JOIN erp_acc_match_invoice mi
    ON  mi.allocation = 'full'
    AND i.gl_reference = mi.reference

and the second accordingly. This would avoid any potential materialization of the subqueries and produce similar plans to your NOT EXISTS method (which is a good method, too, to write anti-joins).

Notes:

  • An index on (allocation, reference) would improve efficiency - of both queries/methods.
  • Assuming that (match_invoice_id) is non-nullable column and considering the NOT EXISTS query, you can replace the two AND mi.match_invoice_id IS NULL with AND mi.reference IS NULL.

So the whole query becomes:

SELECT *
FROM erp_gl_batch_item i
  LEFT JOIN erp_gl_batch b
    ON i.gl_batch_id = b.gl_batch_id
  LEFT JOIN erp_customer c
    ON i.gl_item_account_no = c.customer_code
  LEFT JOIN erp_acc_match_invoice mi
    ON  mi.allocation = 'full'
    AND i.gl_reference = mi.reference
  LEFT JOIN erp_acc_match_invoice mip
    ON  mip.allocation = 'partial'
    AND i.gl_reference = mip.reference
WHERE i.gl_item_account_no LIKE '3001/A01'
  AND mip.reference IS NULL
  AND b.gl_period <= '2016012'
  AND i.gl_item_debit <> '0.00'
  AND mi.reference IS NULL ;

Additional notes:

  • Why is LIKE used and not i.gl_item_account_no = '3001/A01'?
  • The b.gl_period <= '2016012' makes the LEFT JOIN to erp_gl_batch b work an an INNER JOIN.
4
  • your left join query use around 0.05 seconds to run, while the Not exists query use around 0.04 seconds to run.
    – cww
    Jan 1 '17 at 18:54
  • After reading the article from explainextended.com/2009/09/18/… So can I understand it as left join query work fastest, anti-join second while dependent subquery join is the slowest?
    – cww
    Jan 1 '17 at 19:01
  • 1
    That's an old article. While it's very good, you shouldn't rely solely on it - or any article for that matter. Efficiency may vary according to various details, eg distribution of values, size of tables, version of the dbms and many other factors. So considering only that the article queries were tested in much older versions, I suggest you test in your environment, tables and queries before deciding what to use. Jan 1 '17 at 19:44
  • 1
    Regarding LIKE vs =, apparently they work differently with trailing spaces (although I'd expect an account number to have none anyway, so maybe you could have either in this case).
    – Andriy M
    Jan 7 '17 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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