Search code examples
mysqloptimizationdatabase-performance

What works faster "longer table with less columns" or "shorter table with more columns"?

I have to make decision how to plan table that will be used to store dates.

I have about 20 different dates for each user and guess 100 000 users right now and growing.

So question is for SELECT query what will work faster if I make table with 20 fields? e.g.

"user_dates"

userId, date_registered, date_paid, date_started_working, ... date_reported, date_fired 20 total fields with 100 000 records in table

or make 2 tables it like first table "date_types" with 3 fields and 20 records for above column names.

   id, date_type_id, date_type_name

    1       5        date_reported
    2       3        date_registerd
    ...

and second table with 3 fields actual records

"user_dates"

userId, date_type, date
   201       2      2012-01-28
   202       5      2012-06-14
 ...

but then with 2 000 000 records ?

I think second option is more universal if I need to add more dates I can do it from front end just adding record to "date_type" table and then using it in "user_dates" however I am now worried about performance with 2 million records in table.

So which option you think will work faster?

Solution

  • A longer table will have a larger index. A wider table will have a smaller index but take more psychical space and probably have more overhead. You should carefully examine your schema to see if normalization is complete.

    I would, however, go with your second option. This is because you don't need to necessarily have the fields exist if they are empty. So if the user hasn't been fired, no need to create a record for them.