mySQL slow to UPDATE on a simple WHERE query

Question

I have an InnoDB database table with about 160,000 records and a simple UPDATE query like:

UPDATE table SET field='12345' WHERE order_number=102192817

is taking a long time (over half a second) to update:

# Query_time: 0.609242 Lock_time: 0.000118 Rows_sent: 0 Rows_examined: 165359

The problem I have is these updates are running about every 2-3 seconds and it's causing the overall performance of the server to slow down.

The server has 8GB memory and 4.5GB of that is allocated to mySQL with mysqltuner showing everything quite ok (in terms of settings).

Would creating an INDEX on order_number help in this case?

Please post TEXT results of SHOW CREATE TABLE table; so we can look at what you are dealing with. — Wilson Hauck, Feb 01 '20 at 15:23

Milad Nouri · Answer 1 · 2020-02-01T12:21:10.403

2

you can increse query speed by indexing 'order_number' field.

if 'order_number' is unique, use unique index.

if ypu want only update one row, add "LIMIT 1".

UPDATE table SET field='12345' WHERE order_number=102192817 LIMIT 1

this query will be faster because your database won't look through all your records.

edited Feb 01 '20 at 12:21

answered Feb 01 '20 at 12:16

Milad Nouri

1,587
1
21
32

If the table does not have an index on column `order_number`, adding `LIMIT 1` reduces the time to execute to half, in average. It doesn't really solve the problem. – axiac Feb 01 '20 at 12:20
Thank you, I will add `LIMIT 1` to improve efficiency a bit. Didn't think of that! – moo moo Feb 01 '20 at 14:09

score 1 · Accepted Answer · answered Feb 01 '20 at 12:19

Updating the data doesn't take that long. Finding the record(s) to be updated is the time consuming operation.

If your table does not have an index on the column used in the WHERE clause, MySQL needs to check all the rows in the table to find the one(s) to update.

You can check how it finds the rows by running

EXPLAIN SELECT * from table WHERE order_number=102192817

(Use the WHERE clause of the UPDATE query).

Check the column key of its output. If key is NULL, MySQL found no index to use for executing the query more efficiently.

Use the CREATE INDEX command to create an index on column order_number to improve the speed of your query.

Read more about CREATE INDEX and the output of the EXPLAIN command.

Indeed, it's `NULL`. I will use your suggestion and the one above (both combined) and hopefully that will give it some improvement. Thank you! :) — moo moo, Feb 01 '20 at 14:09

score 1 · Answer 3 · answered Feb 01 '20 at 16:08

Importance of Indexes

By not having an index your update statement will have to scan through the whole table. It's the where condition that is causing the delay.

In general, you should consider adding an index under the following conditions:

Indexes are best used on columns that are frequently used in where clauses, and in any kind of sorting, such as "order by".

You should also pay attention to whether or not this information will change frequently, because it will slow down your updates and inserts.

In your specific case, order_number does not seem like a field that will change.

Reference: https://www.howtoforge.com/when-to-use-indexes-in-mysql-databases

How to create an index

You can create an index in mysql with the create index command. There are various types of indexes so you may have to decide on which type to use.

Syntax:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

Example - Adding a Simple Index:

ALTER TABLE `your_table_name` ADD INDEX order_number_index(`order_number`)

Example - Adding an Index and specifying type:

create index order_number_indexon your_table_name(order_number) using HASH;
or
create index order_number_indexon your_table_name(order_number) using BTREE;

Note: Supported index types change depending on the storage engine. See Table 13.1 Index Types Per Storage Engine in create-index documentation.

References:

mySQL slow to UPDATE on a simple WHERE query

3 Answers3

Importance of Indexes

How to create an index