MySQL Forums
Forum List  »  Performance

Indexing question: Help me optimize indexes!
Posted by: Craig Matthews
Date: March 24, 2005 12:54AM


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.


Options: ReplyQuote

Written By
Indexing question: Help me optimize indexes!
March 24, 2005 12:54AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.