General questions about MySQL db construction

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

General questions about MySQL db construction

Post by Bill H »

Perhaps someone could point me to a worthwhile tutorial, because my searching has come up with zillions of links but none that were very illuminating. My questions are two in nature:

1st: what is the nature of defining a field as "null" or "not null" by definition? (I'm referring to the column in phpmyadmin.) What effect does that definition have, and what are the advantages/disadvantages of each?

2nd: is a little more broad, some guidance on defining indexes. For instance when a table has an auto-increment int field one rather automatically makes that an index. But in my usage the table is never sorted on that field and rows are never retrieved by it. It's used only as a reference in other tables to identify that those rows "belong" to this record. I question that the index is really serving any useful purpose.

More often I am retrieving records that have some other common factor, such as all records where "iref=24" for instance. It seems to me that indexing the table on the "iref" field would speed up that retrieval? (Note, that was a question.)

Anyway, I find all sorts of references that say indexes are important, but no studies describing how to set them up. So far my db's are pretty small, but I would like to get started improving their efficiency against the day that efficiency becomes a factor.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

OK First off let me say that I use POSTGRES but my responses should also be applicable for MYSQL.

1 NULL AND NOT NULL.

When inserting data you have the option to insert all columns or only some. By setting a column to NOT NULL you are forcing the creation of that field. If the column is not created when performing an insert the insert will fail and an error message created. It is good practice to use NOT NULL and a sensible DEFAULT on columns. (Most integer columns would default to 0 for example). This makes querying easier and clearer as you never need to check for null values.

2. INDEXES
OK So it is for POSTGRES but to give you an idea for indexes....
http://www.postgresql.org/docs/7.4/inte ... dexes.html
Last edited by CoderGoblin on Wed May 12, 2004 8:56 am, edited 1 time in total.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

Thanks, the article on indexes seems to confirm my thinking.

To clarify on null/not-null:
If I create the field with a default value then is the null/not-null specification irrelevant?
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

If you set a default on a field/column, NOT NULL is not required, although it is good practice to put it in. It prevents somebody setting the value to NULL through an update or somesuch.
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

true, the default value overrides the null/not null spec since then by definition, it CAN"T be null.

Indeces on fields contained in the where clause can dramatically speed up queries...so if your query consistently looks for 'iref' and there is a lot of variance in the data, then an index is useful.

If the column is og high cardinality (lots of values the same, like Male/Female Gender columns where there are only 2 values) indeces make no sense as the index engine can't find enough differences to make it work...

hth
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

If Default is set you can still set the field to NULL.

INSERT INTO table (testnull) VALUES (NULL); would insert a NULL into the column testnull even if it had a default value Hence the "practice" of always putting not null in.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

Things are much more clear now. Thanks for all the input.
:)
One last question: how to set a default value for a VARCHAR field.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Bill H wrote:One last question: how to set a default value for a VARCHAR field.
just like for any other column type:

Code: Select all

mysql> create table tt (a varchar(255) default 'asd' not null);
Query OK, 0 rows affected (0.68 sec)

mysql> insert into tt values();
Query OK, 1 row affected (0.46 sec)

mysql> select * from tt;
+-----+
| a   |
+-----+
| asd |
+-----+
1 row in set (0.46 sec)
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

Sorry, my bad.

I meant to say a default that would be a non-value but not a null.
The varchar equivalent of zero for an int field. (Using phpmyadmin.)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

If I remember correctly, phpmyadmin allows arbitrary queries to be executed. Execute the following code from SQL window:

Code: Select all

create table tt (a varchar(255) default '' not null)
It would create table named tt with one varchar column named a with default value '' (empty string). If I understand you correctly, it is varchar analog of 0 for an int field.

Sorry, I don't have phpmyadmin, so I know no way to get such a column created via its 'click&pray' interface ;).
Post Reply