General questions about MySQL db construction
Posted: Tue May 11, 2004 7:52 pm
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.