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.
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
