[Maria-developers] Delete vs multi_delete performance issue

29 Nov 2017

      Hi All,

I've a question on kind of delete on whose we have performance issues.

Consider two tables:

CREATE TABLE `gapfl` (
  `pgegapfl` varchar(150) COLLATE utf8_bin NOT NULL,
  `blkgapfl` varchar(30) COLLATE utf8_bin NOT NULL,
  `fldgapfl` varchar(30) COLLATE utf8_bin NOT NULL,
  `ordgapfl` int(11) NOT NULL,
...
  `stlgapfl` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  UNIQUE KEY `gapfl_fld` (`pgegapfl`,`blkgapfl`,`fldgapfl`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

and 

CREATE TABLE `gapge` (
  `nuugapge` varchar(150) COLLATE utf8_bin NOT NULL,
  `nuigapge` int(11) NOT NULL,
  `frmgapge` varchar(30) COLLATE utf8_bin NOT NULL,
  `prsgapge` varchar(60) COLLATE utf8_bin NOT NULL,
  `typgapge` varchar(2) COLLATE utf8_bin NOT NULL,
  `catgapge` varchar(1) COLLATE utf8_bin NOT NULL,
  `numgapge` int(11) NOT NULL,
  `aplgapge` varchar(8) COLLATE utf8_bin DEFAULT NULL,
  `tragapge` varchar(8) COLLATE utf8_bin DEFAULT NULL,
...
  `mafgapge` varchar(1) COLLATE utf8_bin NOT NULL,
  UNIQUE KEY `gapge_nuu` (`nuugapge`),
  UNIQUE KEY `gapge_frm` (`frmgapge`,`prsgapge`,`typgapge`,`numgapge`,`tragapge`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Table "gapfl " contains 364740 rows
Table "gapge " contains 25423 rows

The following delete tooks more than 38 seconds to delete 0 rows .

DELETE
FROM gapfl
WHERE pgegapfl IN (
  SELECT nuugapge
    FROM gapge
   WHERE frmgapge =  'AMCEVA'
     AND prsgapge =  'QUALIAC.*.*')

It explain plan is :
+------+--------------------+-------+-----------------+---------------------+-----------+---------+------+--------+-------------+
| id   | select_type        | table | type            | possible_keys       | key       | key_len | ref  | rows   | Extra       |
+------+--------------------+-------+-----------------+---------------------+-----------+---------+------+--------+-------------+
|    1 | PRIMARY            | gapfl | ALL             | NULL                | NULL      | NULL    | NULL | 356417 | Using where |
|    2 | DEPENDENT SUBQUERY | gapge | unique_subquery | gapge_nuu,gapge_frm | gapge_nuu | 452     | func |      1 | Using where |
+------+--------------------+-------+-----------------+---------------------+-----------+---------+------+--------+-------------+

It's not really good and I don't understand why the subselect is consider as a "DEPENDENT SUBQUERY" (there is no correlation columns).

Now, I convert this delete into a multi_delete :
DELETE gapfl
FROM gapfl
WHERE pgegapfl IN (
  SELECT nuugapge
    FROM gapge
   WHERE frmgapge =  'AMCEVA'
     AND prsgapge =  'QUALIAC.*.*')

It tooks 0.00 second and it explain plan is :
+------+-------------+-------+------+---------------------+-----------+---------+--------------------+------+--------------------------+
| id   | select_type | table | type | possible_keys       | key       | key_len | ref                | rows | Extra                    |
+------+-------------+-------+------+---------------------+-----------+---------+--------------------+------+--------------------------+
|    1 | PRIMARY     | gapge | ref  | gapge_nuu,gapge_frm | gapge_frm | 274     | const,const        |    1 | Using where; Using index |
|    1 | PRIMARY     | gapfl | ref  | gapfl_fld           | gapfl_fld | 452     | dvt.gapge.nuugapge |    7 |                          |
+------+-------------+-------+------+---------------------+-----------+---------+--------------------+------+--------------------------+

as expected.

Is there any way to  have the right plan with single delete ?
Or do we must convert all our complex update to multi_update ?

Sorry if it's not  the right place to post this query.
Regards.

[Maria-developers] Delete vs multi_delete performance issue

jerome brauge