A simple MySQL update query takes ages. Indexing problem?

Question

UPDATE ps_product_check SET `google_shopping_link`= '777' WHERE `google_shopping_link`= '778'

635 rows affected. (Query took 452.8805 seconds.)

The table has 55000 rows, 39 columns. There are some indexes, one of which is featuring the column name in question.

There are lot of tutorials on HOW to create the indexes technically but couldn't find any which do explain the logic behind it.

This particular query used to take a fraction to complete, now it is 8 minutes.

Is it indexing, or could it be something else? Any reply is valued, can provide more data if needed.

635 rows affected. (Query took 0.0351 seconds.) Thank you, really saved the day. — popkutt
– popkutt, Commented Dec 10, 2019 at 21:24

ScaisEdge · Accepted Answer · 2019-12-10 19:40:35Z

1

An index lead the query to work only on the subset of table rows really matching the query oeration so

You have where condition on google_shopping_link column then proper index is based on this column ..

be sure you have a index o

table  ps_product_check column google_shopping_link

answered Dec 10, 2019 at 19:40

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

Comments

Marlin Pierce · Accepted Answer · 2019-12-10 19:40:56Z

1

An index on the google_shopping_link field would help.

As for the logic of how to use indexes, there are whole books on that topic.

answered Dec 10, 2019 at 19:40

Marlin Pierce

10.1k5 gold badges33 silver badges56 bronze badges