Cost of MySQL Indexes?

[DoubleDee]

I'm curious what impact Indexes have on the performance of MySQL and ultimately my website...

For example, what is the "cost" of a Standard Index in MySQL?

And what is the "cost" of a Unique Index in MySQL?

And how about the "cost" of a Primary Key/Index?


For example, I have a table with the following Fields and associated Indexes...

- id (PK)
- slug (Unique Index)
- name (Unique Index)
- created_on
- updated_on

I feel guilty having 3 Indexes of a Table with only 5 Fields, but what are you going to do?!

Sincerely,


Debbie

[felgall]

indexes slow the updating of the content of the data because the indexes need to be updated as well as the content.

indexes speed up the reading of the data by providing quicker ways of locating what is wanted.

the cost of having or not having an index depends on the ratio of the time saving for reads that are speeded up by it compared to the extra time required by updates that are slowed down by it.

[DoubleDee]

indexes slow the updating of the content of the data because the indexes need to be updated as well as the content.

indexes speed up the reading of the data by providing quicker ways of locating what is wanted.

the cost of having or not having an index depends on the ratio of the time saving for reads that are speeded up by it compared to the extra time required by updates that are slowed down by it.

Well, the table I described is really a "lookup" table, so it will rarely get updated.


Debbie

[felgall]

Well, the table I described is really a "lookup" table, so it will rarely get updated.

So whatever indexes you add that will speed up those lookups will be making things faster. You only need the ones that are actually going to get used though.

[r937]

I feel guilty having 3 Indexes of a Table with only 5 Fields, but what are you going to do?!

if slug and name are supposed to be unique, you aren't going to touch them at all

imagine ensuring their uniqueness in some other way -- you'll end up doing one or two SELECTs before the INSERT, to see if the values are already in the table, and then of course you'll have to wrap them in a transaction to prevent race conditions, etc.

you ~could~ get rid of the id, and use either slug or name as the PK