what are the disadvantages of INDEXES?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
eshban
Forum Contributor
Posts: 184
Joined: Mon Sep 05, 2005 1:38 am

what are the disadvantages of INDEXES?

Post by eshban »

what are the disadvantages of INDEXES?
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

You use more space because of the way the tree structures are stored internally. Your database will also take longer to initialize the tables upon initial startup and when restoring from backup. Don't index anything you don't need, mostly becuase as I say, it uses a lot more storage space (and memory if I remember correctly).
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

For high insert and update volume tables, ones where records are added and changed many times every second, an index can have a negative impact on performance. Other than that though, indexes are pretty much always a good idea where you need a boost on selects. Just remember to lock your tables properly, and use transactions if they're available.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

onion2k wrote:Just remember to lock your tables properly, and use transactions if they're available.
Does this relate to indexes in any way?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

d11wtq wrote:
onion2k wrote:Just remember to lock your tables properly, and use transactions if they're available.
Does this relate to indexes in any way?
Updating or inserting into an unlocked table increases the risk of corrupting the an index.
Post Reply