Page 1 of 1

General questions about MySQL db construction

Posted: Tue May 11, 2004 7:52 pm
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.

Posted: Wed May 12, 2004 4:07 am
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

Posted: Wed May 12, 2004 8:44 am
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?

Posted: Wed May 12, 2004 8:54 am
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.

Posted: Wed May 12, 2004 8:56 am
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

Posted: Wed May 12, 2004 9:02 am
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.

Posted: Wed May 12, 2004 11:37 am
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.

Posted: Wed May 12, 2004 11:53 am
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)

Posted: Wed May 12, 2004 1:03 pm
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.)

Posted: Wed May 12, 2004 2:04 pm
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 ;).