I was just doing some reading on indexes when I saw this post and I have a question about "Over Indexing". I have a MySQL table that stores inventory transactions, in, out etc. I mainly store fields that are primary indexes in other tables, Paul DuBois' MySQL book says that anything used in a where clause or join clause should be indexed. So it turns out that 7 out of eight fields fit this description, 1 primary key, 4 smallints, and 2 are datetime fields.
Should I use all these indexes or are they not needed? Sorry about asking this in a php list but I saw the topic, and there's no dba where I work. Thanks -Steve. -----Original Message----- From: SP [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 02, 2002 3:11 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: [PHP] PRIMARY KEY vs. INDEX Indexes are good but the only thing you have to look out for is not over using it. So don't index all your fields because it will make your database a lot bigger and add more time when inserting and updating. -----Original Message----- From: Dan Hardiker [mailto:[EMAIL PROTECTED]] Sent: May 2, 2002 8:41 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [PHP] PRIMARY KEY vs. INDEX To clarify, an indexed field is *not* inheriently unique. You can have an indexed field which is not unique, and a unique field which is not indexed (hence the options being available). A primary key is both indexed and unique (with the slight adaption of compound keys)... but this is majorly off topic. http://www.mysql.com/doc/ - Dan > Christoph, > > Indexes are built on key fields, so yes. When a field is identified as > a key it is indexed. > > To answer your second question, it is the index which maintains the > uniqueness of a field. > > A book is a really good analogy. If you wanted to look up the > references to "string", you can do it very quickly in the index. Even > if you don't come close to the "s" section, you immediately know > whether to look next to the right or to the left. If you push the > issue, and act extremely dumb (like a computer), you'll find the page > containing "string" within 7 hits; once you're on that page it's a > short read to find the term. That's what makes searching on indexed > fields so fast. > > To find the term in the book requires you to start reading on page 1 > and to read text until you find the term. Which why searches which do > not take advantage of key fields are so slow. > > Indexes are your friend, they don't slow down the addition of records > that much, and make retrieval of records extremely fast. > > Regards - Miles Thompson > > > > At 02:13 PM 5/2/2002 +0200, Christoph Starkmann wrote: >>Hi there! >> >>I guess I got a very easy question for the pros here... >>I've been searching the documentaion of mySQL, but didn't >>find the answer (even though I'm sure it's out there >>somewhere :))... >> >>Is a primary key in mySQL automatically indexed? And is a >>unique field indexed automatically ?(I don't think and >>don't hope so, but one never knows) >> >>Thanx, >> >>Kiko >> >>-- >>It's not a bug, it's a feature. >>christoph starkmann >>mailto:[EMAIL PROTECTED] >>http://www.gruppe-69.com/ >>ICQ: 100601600 >>-- >> >>-- >>PHP General Mailing List (http://www.php.net/) >>To unsubscribe, visit: http://www.php.net/unsub.php > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php -- Dan Hardiker [[EMAIL PROTECTED]] ADAM Software & Systems Engineer First Creative Ltd -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
