RE: [PHP] PRIMARY KEY vs. INDEX
Yes, but that's what the php-db list is for. I am sorry... Gonna get this list. 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
Re: [PHP] PRIMARY KEY vs. INDEX
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) Kind of off topic, but yes, they are both indexed. Use SHOW INDEX FROM table_name to see the indexes. ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PRIMARY KEY vs. INDEX
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
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
Re: [PHP] PRIMARY KEY vs. INDEX
On Thursday 02 May 2002 20:13, 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) Pardon, I'm new here, but what has this got to do with PHP? -- Jason Wong - Gremlins Associates - www.gremlins.com.hk Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* It's great to be smart 'cause then you know stuff. */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
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
RE: [PHP] PRIMARY KEY vs. INDEX
Yeah this isn't specifically about php but most people use databases so I think this is relevant. I like it when we have discussions like these instead of someone posting up a bunch of code and asking to find a missing ' or }. -Original Message- From: Jason Wong [mailto:[EMAIL PROTECTED]] Sent: May 2, 2002 1:21 PM To: [EMAIL PROTECTED] Subject: Re: [PHP] PRIMARY KEY vs. INDEX On Thursday 02 May 2002 20:13, 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) Pardon, I'm new here, but what has this got to do with PHP? -- Jason Wong - Gremlins Associates - www.gremlins.com.hk Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* It's great to be smart 'cause then you know stuff. */ -- 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
Re: [PHP] PRIMARY KEY vs. INDEX
On Friday 03 May 2002 03:17, SP wrote: Yeah this isn't specifically about php but most people use databases so I think this is relevant. I like it when we have discussions like these instead of someone posting up a bunch of code and asking to find a missing ' or }. Yes, but that's what the php-db list is for. -- Jason Wong - Gremlins Associates - www.gremlins.com.hk Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* Remember, UNIX spelled backwards is XINU. -- Mt. */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] PRIMARY KEY vs. INDEX
True. -Original Message- From: Jason Wong [mailto:[EMAIL PROTECTED]] Sent: May 2, 2002 3:26 PM To: [EMAIL PROTECTED] Subject: Re: [PHP] PRIMARY KEY vs. INDEX On Friday 03 May 2002 03:17, SP wrote: Yeah this isn't specifically about php but most people use databases so I think this is relevant. I like it when we have discussions like these instead of someone posting up a bunch of code and asking to find a missing ' or }. Yes, but that's what the php-db list is for. -- Jason Wong - Gremlins Associates - www.gremlins.com.hk Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* Remember, UNIX spelled backwards is XINU. -- Mt. */ -- 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
RE: [PHP] PRIMARY KEY vs. INDEX
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
