mysql query speed

Question

I just want to ask which out of the two ways of storing data would give my better results

A. Storing data in a single table with over 20+ columns

OR

B. Distributing the data into two tables of 15 and 6 columns each

one more thing, even if I distribute the data I still might have to access both the tables at the same time in 60% of cases...

So, which is the best bet out of the two...

Thanks

Answer 1

A JOIN, even on a PRIMARY KEY, requires several times as much time as a sequential scan.

Of course it's better to see the table, but my educated guess is that keeping them together will be better.

If the columns you want to split are small and have high cardinality (lots of distinct values), then leave them in the table.

However, if what you do is normalizing (as opposed to mere table split), then normalized design can be faster.

For instance, if your data looks like this:

id  value

1   Really really long value 1
2   Really really long value 1
3   Really really long value 1
…
1000   Really really long value 2
1001   Really really long value 2
1002   Really really long value 2
…

, it takes long to scan this table, since the values are large.

Normalizing the table will give the following:

id  value

1   1
2   1
3   1 
…
1000   2
1001   2
1002   2
…

val_id  value
1       Really really long value 1
2       Really really long value 1

Scanning rows of 4-byte integers is much faster than thousand-byte VARCHAR's, and the query on the second design will complete faster even despite the extra JOIN.

Answer 2

I guess it depends on the database schema. If there are only small fields, method A is probably better. But if there are large fields that can be put to the less used table, it might make sense to store them separately as suggested in method B.

Answer 3

Without giving us more details our answers can't possibly be definitive. Would you be splitting the columns to avoid duplication? If so, you'll likely see a speed increase for simple queries. However, if you have queries that involve complicated joins, you may see a performance hit. A lot of it depends on how you index your tables too.

There just isn't enough information in your question to give a better answer, without diving into a 2 hour discussion on database design.

I suggest reading how-do-i-ask-for-help-optimizing-fixing-queries-in-mysql on stackoverflow to learn how to ask for help with MySQL.