Large ENUM database, (77) Best way to do "select" "where"

[Dross]

My database has 77 ENUM datatypes.

I need to run a select statement on all 77 ENUMs.

Seems awfully inefficient and it'll probably run awfully slow.

Is there a better way to set up the database and run the query?

 

version: mysql 5.0.67

 

Thanks

Bob

[Mchl]

You mean a table with 77 ENUM fields, or a field with 77 possible ENUM values?

 

Posting table structure would help.

 

Did you try running

SELECT * FROM `table` PROCEDURE ANALYSE ( )

 

for some hints?

[Dross]

One table, 77 ENUM fields, each field has two possible values, '1','0'.

 

Lots of yes or no questions.

 

 

You mean a table with 77 ENUM fields, or a field with 77 possible ENUM values?

 

Posting table structure would help.

 

Did you try running

SELECT * FROM `table` PROCEDURE ANALYSE ( )

 

for some hints?

[Mchl]

ENUM for that is an overkill. TINYINT would be better I think... but that's not the point here, is it?

 

I suppose there's nothing wrong with

SELECT * FROM table WHERE
field1 = 1 AND
field2 = 0 AND
...
field77 = 1

 

I mean... you can't really do it any other way without restructuring your table.

[Dross]

Right now that is exactly how I am going to do it.

 

SELECT * FROM table WHERE

field1 = 1 AND

field2 = 0 AND

...

field77 = 1

 

I have not launched the application so I can restructure the table.

 

The above just seems so non-elegant, is there a better way?

Each field is really independent of the other fields, so I don't see how I could combine them.

 

Thanks

 

 

 

ENUM for that is an overkill. TINYINT would be better I think... but that's not the point here, is it?

 

I suppose there's nothing wrong with

SELECT * FROM table WHERE
field1 = 1 AND
field2 = 0 AND
...
field77 = 1

 

I mean... you can't really do it any other way without restructuring your table.

[Mchl]

You could do it this way

 

Create a table called 'yesNoQuestions'

 

table yesNoQuestions
question_ID, questionText
1, "text1"
2, "text2"

 

I assume you have your current table... I don't know how it's called... but let's say for this example, that it's called 'users' and stores information about users

 

table users
user_ID, userName, password, ...
1, "Nick", "5A462CA5019..."

 

Now you just need one more table, that will store information, about what answers did users provide

 

table answers
user_ID, question_ID
1,1
1,3
1,8
1,77
2,1
2,2
2,3
2,8
2,32

 

In example above, user with user_ID 1 answered 'yes' to questions # 1,3,8 and 77. User with ID = 2 answered 'yes' to questions 1,2,3,8 and 32

 

Now if you want to select all users who answered yes to questions 1 and 2 you would do...

 

hmm... I have no idea for query right now, but I'll post whatever I've come up with so that you have something to think about

 

[fenway]

Yes/no fields are not going to use an index anyway.

[Mchl]

That's why I think splitting it into a many to many relation could be a better design. I just got carried away by real life, and can't come up with the query...