Understanding multiple indexes with MySQL (and performance)

Question

I have a "phones" table with around 120 000 records, it contains phone_id, phone_number, phone_user_name, phone_last_contacted, etc.

The phone_id is index and looks like: 1,2,3,4 ... 120 000.

But I often update different fields like "phone_last_contacted":

UPDATE phones SET phone_last_contacted = '01-01-2013' WHERE phone_number = '+123456789'

The "slow query" log says that query read many records before finding the right one to update;

Would it be a performance upgrade if I set the phone_number to be the index? Considering that all phone_numbers are unique in this table. If the phone_number is the index, does this mean that mysql will know how to go directly to the row to update instead of reading many rows and find the right row ? Is that the purpose of an index or multiple indexes ?

Edit: I just changed the "UPDATE" query to find the row by phone_id (which was primary key and unique), it was a huge difference. Makes sense for me now, it no longer has to look in each row in order to find the phone_number, it can find it faster by phone_id:

Answer 1

You speak of "the index" so I assume you mean "the clustered index." I'd actually start by just slapping a unique index on phone_number - if you're confident that it really is and always will be unique, and if it's the field you most often filter by.

If it is unique, it's a good example of a "candidate key" - you could, technically, use it as a primary key. However, it's also a "business key" - driven by human logic, and therefore susceptible to change, unlike an IDENTITY or other machine-generated sequence, like your phone_id field. Biz keys are generally poor candidates for the PK, thus sticking with your current PK/clustered key.

Answer 2

Jon of All Trades already covered most of the question with good points of picking the key field(s), but I'll add something about the purpose of indexing in DBs, since that was also asked...

The index is used for making the life easier for the DB engine by enabling it to have a concise presentation of column's data with references to the actual rows in the table. Thus it is generally always favorable to have indices on those columns that the table is queried by (i.e. those columns that appear in the where, group by, having and order by expressions). Then the DB engine doesn't need to scan through all the rows of the table but it can more efficiently find the matching rows.

To be able to have an index on a column (or a set of columns) it is not needed that the column (or the set of columns) has unique value. If the value is not unique, the index at least points the DB engine to the right parts of the table which still is a performance gain. The primary key of a table usually has a (unique) index since that is the field which is used in joining the table to other tables and thus it certainly will be queried by a lot (and the values also should be unique). The rest of the possible indices are usually left for a DB admin or designer to add after analysing the queries (unless the DB automatically adds them due to the constraints that have been defined on the tables).

The size of the performance improvement for a operation due to adding a suitable index can be very dramatic (most likely this is the case in the case you brought up here), but that does not apply to all cases, some queries just can't be helped with much other than rewriting them or redesinging the DB to better suit the queries thrown at it.