Does indexing make sense to speed up the use of UPDATE?

Question

I have a MyISAM table in MySQL with three columns - an auto-incrementing ID, an integer (customer id) and a decimal (account balance).

At this time, whenever I initialize my system, I completely wipe the table using:

truncate table xyz;
alter table xyz auto_increment=1001;

Then I repopulate the table with data from PHP. I usually end up having up to 10,000 entries in that table.

However, due to new requirements to the system, I now need to also be able to update the table while the system is running, so I can no longer wipe the table and have to use UPDATE instead of INSERT and update the balances one by one which will be much slower than inserting 20 new records at a time as I'm doing now.

PHP only sends the customer id and the amount to MySQL - the other id is not actually in use.

So my question is this: Does it make sense to put an index on the customer id to speed up updating given that the input from PHP is most likely not going to be in order? Or will adding the index slow it down enough to not make it worthwhile?

I also don't know if the index is used at all for the UPDATE command or not ...

Any other suggestions on how to speed this up?

Answer 1

It depends on what your update query is. Presumably it is like:

update xyz
    set val = <something>
    where id = <something else>

If so, an index on id will definitely help speed things up, because you are looking for one record.

If your query looks like:

update xyz
    set val = 1.001 * val;

An index will neither help, nor hurt. The entire table will need to be scanned and the index does not get involved.

If your query is like:

update xyz
    set id = id+1;

Then an index will be slower. You have to read and write to every row of the table, plus you then have the overhead of maintaining the index.