MySQLers: Which method is best ?
 

Let's say I have a simple table containing two columns: id (auto_incremented) and type.

Now I have another table, with 10 fields. I need to add a field to show what 'type's this person has.

The person can have more than one type.

I can think of 4 methods to use here:

1) Add a boolean field per possible type in my bigger table

2) Add one field in the bigger table that is of type 'set'

3) Have a table with id and a boolean field for each possible type, and insert rows to cover each possible combination. Add field in my bigger table which will contain this id of the right combination.

4) Have a table with primary key of my big table and id fo type. Each person may have upto x rows in this table, where x is the number of types they have.

Which would be the best performance-wise and following good sql normalization/structuring? Let's assume some things to see a better picture:

- The large table has 1 million rows, getting many selects using WHEREs on the type (among other things).
- There are 5 different types


Now personally, I would definately eliminate number 1, no questions asked.

The other 3 options however, all seem to have their advantages and disadvantages.

Which one would you use, why....or offer a better method :winkwink:

PS: I'm currently using method number 2, and using FIND_IN_SET when doing my selects. Having no problems, but always curious if things can be done better.

4) Have a table with primary key of my big table and id fo type. Each person may have upto x rows in this table, where x is the number of types they have.

Is the option if your going for a proper relational data structure.

Third Normal Form and All that crap.

With only 1 mill row using any other kind of strucutre isn't going to maxamize performance.

But don't forget that the queries may end up a bit complicated if your using an older version of MySQL that doesn't allow nested SQL

oh and number 3 is the most bonkers thing I've ever seen in relation to databases'

hehe yeah...I never sued it, but thought it up as a possibility. Since the version I'm using of MySQL doesn't support sub-selects, I figured it might be faster to select the combination id from that table, then use it in my select from the big table instead of doing any complicated join queries.

There are some cases I've had where doing one mini-select and then another select comes out faster than doing a complicated JOIN query.

NUmber 4 I can see it being the most standardized...however if I don't have sub-selects, I think it might be worse than the set method...

Thanks for your input =)

Yeah if you've not got sub-selects I'd probably stick with your number 3.


MySQL sucks, but it's pretty much the most supported free version avilable

sub-selects are available as of 4.1 right? Maybe I should just upgrade, I'm at 4.0.20 right now

number 4 .. normalized and will make your queires simpler and efficient.

ad a varchar type field 10 lon, set an index on it and put in the field for example 1100000000. Means he only has the first two types. quick and small. Old school i presume :-)

personally i'd use the set method.

I think after asking few others, I will make the switch to 4.1 so I can use sub-selects. It's almost production-ready anyways.

With that, I shall switch to method 4 as well, as some fo you suggested :thumbsup

I would too... The set method is very quick as it only performs an assembler logical AND to find which types are there and returns them like "typeA,typeB,etc". I can't find something faster than that.

The drawback would be scalability as in order to add types one must modify the structure of the table (which if does not happen too often is okay).

Altering a table with 1M rows takes about 30 seconds. Adding indices though takes quite some time. (tried many times with tables 3M rows containing GPS data).

I am currently using the SET method without any trouble....however, I do think of it like this:

using set as a type in large tables is adding unnecessary size to the table. The same way that defining a field as varchar(255) when you never use more than 20 characters in that field would.....because if I define it as set {1,2,3,4,5}, wouldn't it just be wasting space for those who only have 1 or 2 types ??

What about simply using an int column in your big table and using some basic maths... you say you have 5 types, so you assign a number to each type:
type 1 = 1
type 2 = 2
type 3 = 4
type 4 = 8
type 5 = 16
so if your entry has all 5 types, the total to enter in the int column is 31, if it has type 1, 3 and 5, the value is 21, etc.

tihis way you do not have to use any joins, you do not make your table much bigger, and you can add more types in the future without any modification to your table structure...

that's the way i'd do it:)

My problems with that method is it requires extra parsing in my code, as well as not being very standardized for someone who might code after me to follow....