MySQL update with subselect too slow

Having an issue with an update query taking more than 20 minutes (I kill it after that).

Scenario:

Table one has some 300K records.

Table two contains the same set of records (copied over), but wi开发者_如何学Pythonth an extra field that needs to contain the id of the record that matches a number of fields, and has the highest value of another (a score). To clarify, the end result should be table two containing 300K records with each record having the id of another record that has the same set of basic properties, and the highest score within the set of records with those properties.

The below completes in ~5s when I only copy 2K records instead of the full 300k records into table two.

UPDATE vtable2 v1 SET v1.buddy = (
    SELECT v2.id FROM vtable1 v2
    WHERE
    v2.group_id = v1.group_id AND
    // 6 more basic comparisons
    ORDER BY score DESC LIMIT 1
)

I need to find buddies for the full 300K records. All fields involved in joining and sorting have indexes.

Help much appreciated.

MySQL sub-queries tend to be a little slower. I prefer using joins in such cases. I am not exactly clear on your schema design - but you can try something like this -

UPDATE vtable2 v1
[INNER] JOIN vtable1 v2 
ON v2.group_id = v1.group_id
AND //OTHER JOIN CONDITIONS IF ANY
WHERE
//any other conditions
SET
v1.buddy = v2.id

PS - Of-course you need to make sure you have proper indexes on your columns. If you need help with that, you can post the whole query with an explain plan.

you could test with numeric variable

 SELECT v2.id FROM vtable1 v2
WHERE
v2.group_id = 1 AND
// 6 more basic comparisons
ORDER BY score DESC LIMIT 1

Anyway I think use Join it's better but I don't have schema DB.
Maybe you have a trouble about index on your sql DB.

You can use an exclusion join to find the row in vtable1 such that no other row in vtable1 with a higher score can be found.

UPDATE vtable2 AS v1
INNER JOIN vtable1 AS v2a ON v1.group_id = v2a.group_id AND (...conditions...)
LEFT OUTER JOIN vtable1 AS v2b ON v1.group_id = v2b.group_id
  AND v2a.score < v2b.score AND (...conditions...)
SET v1.buddy = v2.id
WHERE v2b.group_id IS NULL;

You do have to duplicate all the other conditions in the expression for the outer join; you can't put them into the WHERE clause.