Will adding index solve lock wait timeout exceeded error

Question

We have a table with 2 million + records. The records are inserted through two sources, from UI and from AWS lambda/step function. The issue is that when records are inserted/updated via lambda, DB errors out with "General error: 1205 Lock wait timeout exceeded; try restarting transaction". There is no index added to the table. Following is the output explain statement which causing the issue.

The following update query is causing issue.

UPDATE xxx SET 
is_primary = 0, 
updated_at = '2022-11-23 09:36:11' 
WHERE x.id = 1684585 AND 
xxx.id is not null AND 
is_primary = 1 AMD 
xxx.deleted_at IS NULL;

As you can see query scans through 1246661 records. My questions are

1. Will adding index on xxx_id will speed up the update query?
2. Is it safe to add a index on a table with 2 million + records?
3. Will this have any advert effect, I mean will this slow down the update instead of speeding up?

EDIT : I have renamed table name and id field name

Answer 1

If you want to update a single row quickly, and there's a column on it which is a unique or primary key, then you can use that key in the WHERE clause. In your example, if 'id' is not a unique key then you need to check the data and application logic to see if a unique index can safely be created for it. It really depends on more than the information that you have supplied. If a unique key is not appropriate, then a non-unique key might help but then you might update multiple rows at once which might not be what you want. Just adding indexes in the hope of improved performance is not generally a good idea. Database design is not simple.

In general, adding indexes will speed up queries that use them but slow down, only a bit usually, those that don't. Adding an index to a large table can take some time, so be aware that the DB performance could be affected while it is being created. If you can do it while the DB is otherwise idle, that would be better.