General questions about MySQL db construction
Moderator: General Moderators
- 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
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.
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.
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
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
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.
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
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
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
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
just like for any other column type:Bill H wrote:One last question: how to set a default value for a VARCHAR field.
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)If I remember correctly, phpmyadmin allows arbitrary queries to be executed. Execute the following code from SQL window:
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
.
Code: Select all
create table tt (a varchar(255) default '' not null)Sorry, I don't have phpmyadmin, so I know no way to get such a column created via its 'click&pray' interface