Hi there,
I am in my 6th database redesign phase *grin* and really need some input here!!
I have a member table which currently contains over 150 colums, some of which are simply answers to yes/no questions, and most are answers to multiple choice questions where the person could choose one OR MORE of any number of options .
This table will be used 98% of the time for QUERIES and most of the queries will need to test against at least 50% of the colum values. I am expecting a minimum of 10K rows in this table and even that will grow over time.
The reason for the following questions are thus based on the need for (1)performance and (2)keeping the database as small as possible so as to limit the total resource requirements.
Using mySQL db on a SUN SPARCserver running Solaris8 with PHP and Apache. Note: initial memory in this server is limited to 128MB so I do not want to use methods which are going to require huge amounts of memory resources.
1. For the boolean y/n fields, should I use ENUM or SET col type or ?
2. If I use ENUM, should I use the (0) length feature allowing the 2 possible values NULL and "", or should I use physical y/n characters?
3. For the multiple choice fields, I am thinking of changing my currently specified VARCHAR's to SET fields. Good or bad idea?
4. For SET fields, what do I set as the length of the field? (8) to allow for the full 64 possibilities, or (64) to describe the number of elements or ?
5. If the answer to the above questions is (64) then is there any performance or other hit I am going to take if I specify the max (64) for every such column knowing full well that some columns may only end up having 6 or 12 or 20 or whatever number of elements?
6. I have been using VARCHAR fields for all of my text fields but now read that a query on a CHAR field is faster. Whats the story here?
7. Assuming the scenario laid out above and the answers to the questions ... which columns _should_ be indexed? ie. Can ENUM and/or SET fields be indexed effectively?
Last question ....
8. Having never dealt with such a large table before the idea of a 150+ column table scares me. Is this indeed an exceptionally large table which should be split into many smaller tables (which will all need to be hit in a single query) or am I simply being a wimp?
Best regards
Grant
I am in my 6th database redesign phase *grin* and really need some input here!!
I have a member table which currently contains over 150 colums, some of which are simply answers to yes/no questions, and most are answers to multiple choice questions where the person could choose one OR MORE of any number of options .
This table will be used 98% of the time for QUERIES and most of the queries will need to test against at least 50% of the colum values. I am expecting a minimum of 10K rows in this table and even that will grow over time.
The reason for the following questions are thus based on the need for (1)performance and (2)keeping the database as small as possible so as to limit the total resource requirements.
Using mySQL db on a SUN SPARCserver running Solaris8 with PHP and Apache. Note: initial memory in this server is limited to 128MB so I do not want to use methods which are going to require huge amounts of memory resources.
1. For the boolean y/n fields, should I use ENUM or SET col type or ?
2. If I use ENUM, should I use the (0) length feature allowing the 2 possible values NULL and "", or should I use physical y/n characters?
3. For the multiple choice fields, I am thinking of changing my currently specified VARCHAR's to SET fields. Good or bad idea?
4. For SET fields, what do I set as the length of the field? (8) to allow for the full 64 possibilities, or (64) to describe the number of elements or ?
5. If the answer to the above questions is (64) then is there any performance or other hit I am going to take if I specify the max (64) for every such column knowing full well that some columns may only end up having 6 or 12 or 20 or whatever number of elements?
6. I have been using VARCHAR fields for all of my text fields but now read that a query on a CHAR field is faster. Whats the story here?
7. Assuming the scenario laid out above and the answers to the questions ... which columns _should_ be indexed? ie. Can ENUM and/or SET fields be indexed effectively?
Last question ....
8. Having never dealt with such a large table before the idea of a 150+ column table scares me. Is this indeed an exceptionally large table which should be split into many smaller tables (which will all need to be hit in a single query) or am I simply being a wimp?
Best regards
Grant