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
Improve this question
11
  • Is (match_invoice_id) the PK of that table?
    – ypercubeᵀᴹ
    Dec 30, 2016 at 10:47
  • Do you really need c? Does it need to be LEFT?
    – Rick James
    Dec 30, 2016 at 20:47
  • What percent of the table has gl_item_account_no LIKE '3001/A01'?
    – Rick James
    Dec 30, 2016 at 20:47
  • Please provide SHOW CREATE TABLE.
    – Rick James
    Dec 30, 2016 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, 2016 at 11:29

1 Answer 1

Reset to default
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.
Improve this answer
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, 2017 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, 2017 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.
    – ypercubeᵀᴹ
    Jan 1, 2017 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, 2017 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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