MySQL update query low performance - are my indexes wrong?

Question

I have two tables - a temporary table for working with imported data, and the existing accounts table. I need to update the user id in the temp table based on matching by an account number and group number in the accounts table.

CREATE TABLE `_temp` (
 `g_id` int(11) NOT NULL AUTO_INCREMENT,
 `g_group_norm` varchar(10) DEFAULT NULL,
 `g_uid1` varchar(25) DEFAULT NULL,
 `g_spid` int(11) DEFAULT NULL,
 `g_pid` int(11) DEFAULT NULL,
PRIMARY KEY (`g_id`),
 KEY `groupn` (`g_group_norm`) USING BTREE,
 KEY `guid` (`g_uid1`) USING BTREE,
 KEY `gspid` (`g_spid`) USING BTREE
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

 CREATE TABLE `accounts` (
`sa_actid` int(11) NOT NULL AUTO_INCREMENT,
`sa_grp` varchar(10) DEFAULT NULL,
`sa_account` varchar(25) DEFAULT NULL,
`sa_spid` int(11) DEFAULT NULL,
`sa_partnerid` int(11) DEFAULT NULL,
 PRIMARY KEY (`sa_actid`),
 KEY `spid` (`sa_spid`) USING BTREE,
 KEY `grp` (`sa_grp`) USING BTREE,
 KEY `act` (`sa_account`) USING BTREE,
 KEY `partnerid` (`sa_partnerid`) USING BTREE,
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

The accounts table is upwards of 5M rows. The _temp table data I'm working with now can be anywhere from 50,000 to 700,000 rows.

The query I've been using to set that g_spid = sa_spid is this:

UPDATE _temp T, accounts A SET
  T.g_spid = A.sa_spid
 WHERE T.g_uid1 = A.sa_account
   AND T.g_group_norm = A.sa_grp
   AND A.sa_partnerid = 118
   AND T.g_spid IS NULL;

The accounts table has around 3M rows, of which 2.84M are partner ID "118". The temp table update works slow, but OK on 10-20k rows, but when I have a larger set of data to work with (currently temp table is around 100k rows) it seems to never finish (it's been running for 15 minutes now).

Is there a better way to do this query? I've tried it with an inner join on g_uid1 = sa_account, g_group_norm = sa_grp etc. and that seems even slower.

This is an inner join, just written differently. So the time will be exactly the same. — Strawberry
– Strawberry, Commented May 17, 2018 at 13:05

ScaisEdge · Accepted Answer · 2018-05-17 13:09:10Z

1

You could use a composite index on _temp (g_group_norm, g_uid1, g_spid )
and a composite on accounts (sa_partnerid, sa_account, sa_spid )

UPDATE _temp T
INNER JOIN accounts A  ON  T.g_uid1 = A.sa_account 
  AND T.g_group_norm = A.sa_grp 
    AND A.sa_partnerid = 118
SET T.g_spid = A.sa_spid
WHERE  T.g_spid IS NULL

answered May 17, 2018 at 13:09

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Steve Over a year ago

This worked suitably fast once I added a composite index. I want to compare now vs. @spencer7593 s answer above to see if the correlated subquery is faster or slower than the inner join in yours.

spencer7593 · Accepted Answer · 2018-05-17 14:11:04Z

An equivalent result could be achieved:

 UPDATE _temp t
    SET t.g_spid =  ( SELECT MIN(a.sa_spid) 
                        FROM accounts a
                       WHERE a.sa_account   = t.g_uid1
                         AND a.sa_grp       = t.g_group_norm
                         AND a.sa_partnerid = 118
                    )
  WHERE t.g_spid       IS NULL
    AND t.g_uid1       IS NOT NULL
    AND t.g_group_norm IS NOT NULL

That correlated subquery is going to be executed for every row returned for the outer query, so for performance we want a suitable index, preferably a covering index.

With the equality conditions in the WHERE clause of the correlated subquery, we want those three columns first in the index, with the most selective column first. (With nearly 95% of the rows in accounts having sa_partner_id value of 118, that isn't very selective, so we put that third.)

ON accounts (sa_account, sa_grp, sa_partner_id, sa_spid)

We also include the sa_spid column, to make it a "covering" index, so that subquery can be satisfied entirely from the index, without a need to lookup pages in the underlying table.

(The indexes on the singleton columns might be useful for other queries, but they aren't suitable for this particular query.)

We might also add an index on the _temp table, if the conditions in the WHERE clause are selective enough. If we're needing to look at more than 10% or 15% of the rows in _temp, a full scan will likely be faster.

With each row updated, there will overhead for maintaining the index on g_spid column. For large sets, sometimes its faster to drop the index, perform the update, and the re-add the index.

(I suspect there are better choices for indexes on the _temp table, but that can't really be determined without knowing the other SQL being executed against the table. None of the indexes on the _temp table look to be suitable for this query, unless the conditions in the WHERE clause of the outer query are very selective.)

For a large number of rows in _temp, we might want to break up the operation into smaller sets.

Using the same exact query pattern, but adding another condition to the outer query to break it into smaller sets.

As an example (I don't know the datatypes of the columns, the ranges of values, or distribution. Just as an illustration of the idea, assuming group_norm is a DECIMAL value that ranges from 0.00000 to 0.99999 with an even distribution, breaking the UPDATE into ten "sets"...

first run

  AND t.group_norm >= 0.0 AND t.group_norm < 0.1

second run

  AND t.group_norm >= 0.1 AND t.group_norm < 0.2

third run

  AND t.group_norm >= 0.2 AND t.group_norm < 0.3

Collectives™ on Stack Overflow

MySQL update query low performance - are my indexes wrong?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related