Re: [PHP] MySQL and indexes
On Monday, March 18, 2002, at 03:40 PM, Jennifer Downey wrote: > Just wondering, does a table have to have an index? If so what should I > consider when making a colum an index? You're not required to have an index, as far as I know. It's just that an index is a way of optimizing retrievals to and from the table. Think of an index as a mini-table or meta-table that keeps track of a specific column in your actual table -- the RDBMS prefers to store all of the data in its own order, but the index stores a map of one of those columns according to a more search-friendly order. The rule of thumb with indexes is to use indexes on columns that you search frequently. So, if you rarely do any searches based on pet_hunger, then don't index that. But you might often do lookups for pet_name, so that would be a good one to index. Although you have a petid column, it's VARCHAR and thus I have a feeling it's not a "traditional" ID column. By that I mean it's not a column with a unique value for each row in the table. If you really want to take advantage of the true power of a RDBMS, you should create a "traditional" ID column like the following: pet_uniq_id PRIMARY KEY AUTO_INCREMENT NOT NULL which will automatically generate a new unique value each time you add a record. This will help you immeasurably if you ever decide to normalize this table. From what I can tell, your table as is does not appear to be normalized, which means that while it may be functional, you're not making the most of what your RDBMS gives you to work with. Strongly recommended book: "MySQL" by Paul DuBois (New Riders). Erik Erik Price Web Developer Temp Media Lab, H.H. Brown [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL and indexes
I have seen this problem before and, yes, an index field with
autoincrement is always a good thing to have.
Perhaps, this is not necessary, but usually works.
John
On Mon, 18 Mar 2002, Jennifer Downey wrote:
>-Hi all,
>-
>-Just wondering, does a table have to have an index? If so what should I
>-consider when making a colum an index?
>-
>-Here is the table I am using but I can't get php to update it.
>-Is it because there is no index?
>-
>-
>-CREATE TABLE wt_pet (
>- petid varchar(100) default NULL,
>- pet_user int(10) NOT NULL default '0',
>- petname varchar(50) NOT NULL default '',
>- pet_sex enum('Male','Female') NOT NULL default 'Male',
>- pet_equip varchar(50) NOT NULL default '',
>- pet_state varchar(20) NOT NULL default '',
>- pet_hunger varchar(20) NOT NULL default '',
>- pet_date date NOT NULL default '-00-00',
>- num_pets int(1) NOT NULL default '0',
>- ) TYPE=MyISAM;
>-
>-
>-Thanks
>-Jennifer Downey
>-
>-
>-
>---
>-PHP General Mailing List (http://www.php.net/)
>-To unsubscribe, visit: http://www.php.net/unsub.php
>-
**
John Huggins
VANet
[EMAIL PROTECTED]
http://www.va.net/
**
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL and indexes
You don't have to have an index in order for an update to work, although indexes do
help performance a great deal when you are dealing with a lot of records. Do you have
"Update" permissions on the particular table/database you are working with?
Brian Drexler
-Original Message-
From: Jennifer Downey [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 18, 2002 3:41 PM
To: [EMAIL PROTECTED]
Subject: [PHP] MySQL and indexes
Hi all,
Just wondering, does a table have to have an index? If so what should I
consider when making a colum an index?
Here is the table I am using but I can't get php to update it.
Is it because there is no index?
CREATE TABLE wt_pet (
petid varchar(100) default NULL,
pet_user int(10) NOT NULL default '0',
petname varchar(50) NOT NULL default '',
pet_sex enum('Male','Female') NOT NULL default 'Male',
pet_equip varchar(50) NOT NULL default '',
pet_state varchar(20) NOT NULL default '',
pet_hunger varchar(20) NOT NULL default '',
pet_date date NOT NULL default '-00-00',
num_pets int(1) NOT NULL default '0',
) TYPE=MyISAM;
Thanks
Jennifer Downey
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySQL and indexes
Hi all,
Just wondering, does a table have to have an index? If so what should I
consider when making a colum an index?
Here is the table I am using but I can't get php to update it.
Is it because there is no index?
CREATE TABLE wt_pet (
petid varchar(100) default NULL,
pet_user int(10) NOT NULL default '0',
petname varchar(50) NOT NULL default '',
pet_sex enum('Male','Female') NOT NULL default 'Male',
pet_equip varchar(50) NOT NULL default '',
pet_state varchar(20) NOT NULL default '',
pet_hunger varchar(20) NOT NULL default '',
pet_date date NOT NULL default '-00-00',
num_pets int(1) NOT NULL default '0',
) TYPE=MyISAM;
Thanks
Jennifer Downey
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
