mySQL col types -- which to choose???

[Anon]

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

[Anon]

Yes, it is a large table, and I think that might be part of the problem.

Take a look at this article here as it covers Database Normalization

http://www.newbienetwork.net/sections.php?op=viewarticle&artid=17

After you have read it, go back through your Database, and Normalize your table. Then repost your question, as then you might have actually solved some of your problems.

And for Yes No stuff, I do

ENUM ('0','1') with 0 being No, and 1 Being Yes

If you have 150 Fields, that is definetly, at least IMO, something that should be taken care of.

[Anon]

Hi there,

Thank you for your reply. I have gone through the normalization document and although it made sense to me I am still unsure of my next move.

For me to take my table all the way through to 4th normal form would mean I would end up with a huge number of very small tables which at first glance looks good BUT .... is this not going to produce a lot of additional load on my cpu resources when each query (and there will be a lot) now has to span (join) probably close to 100 tables ??? The largest 4NF I have ever had to do resulted in maybe 4 or 5 lookup tables.....

It would be easy enough for me to create lookup tables which would link the member number to the answer table for each question instead of placing the chosen answers directly into the member table but the sheer quantity of tables this would produce scares the hell out of me 😉

Thanks again
Grant