Indexing question: Help me optimize indexes!
Hello,
I notice that my indexes are now bigger than my database itself! I guess we must be doing something wrong.
My questions:
(1) Firstly, our DBA has setup several compound indexes. Some of these are probably wrong but he says we need an index for almost every sorting (or searching) function we need, some examples:
Let's say the table has the columns id, link_id, status, private, alias, create_date, link.
Here are some of our indexes (number in brackets is number of affected rows)
- id PRIMARY (635736) -- id
- link_id UNIQUE (635736) -- link_id
- alias_2 UNIQUE (635736) -- alias
- link_id_3 INDEX (635736) -- link_id, private, user_id, status
- url INDEX (635736) -- url 100, alias, title
- alias_3 INDEX (635736) -- alias, private_key, status
- link_3 INDEX (635736) -- link_id, private, status
- private INDEX (635736) -- private, status, create_date
- ..etc.
Does this make sense? How can we strip some of these indexes? We run a very busy website and I know we need good response speed on our queries but I need some advice or pointers to convince our DBA that this index-mania is not really helping us much!
(2) Secondly, and probably related to above, our web based "PhpMyAdmin" interface to the server shows me these messages:
- UNIQUE and INDEX keys should not both be set for column `link_id`
- More than one INDEX key was created for column `link_id`
- UNIQUE and INDEX keys should not both be set for column `alias`
(3) I guess some of these compound indexes are to ensure some business rules. What is the typical recommendation -- should such checking of data integrity be done at software level or at the database level?
Would greatly appreciate any input or advice.
Thanks!