Splitting tables by field to optimize MySQL?

Do splitting fields into multiple tables ever yield faster queries? Consider the follo开发者_如何学运维wing two scenarios:

Table1
-----------
int PersonID
text Value1
float Value2

or

Table1
-----------
int PersonID
text Value1

Table2
-----------
int PersonID
float Value2

If Value1 and Value2 are always being displayed together, I imagine the first scenario is always faster because the second schema would require two SELECT statements.

But are there any situations where you would choose the second? If the number of records were expected to be really large?

This is called Vertical partitioning, and in some specific situations, may yield better performance.

From Wikipedia:

Vertical partitioning goes beyond normalization and partitions columns even when already normalized. Different physical storage might be used to realize vertical partitioning as well; storing infrequently used or very wide columns on a different device, for example, is a method of vertical partitioning.

A common form of vertical partitioning is to split (slow to find) dynamic data from (fast to find) static data in a table where the dynamic data is not used as often as the static.

Creating a view across the two newly created tables restores the original table with a performance penalty, however performance will increase when accessing the static data e.g. for statistical analysis.

You may be interested in checking out the following article, on how, when, and for how long MySpace used vertical partitioning to tackle its scalibiliy issues:

• High Scalability: MySpace Architecture

Consider your schema 1: with 2 tables. If you were to insert a lot of data with just personID and Value2 (you will end up inserting null for value1), it would be sub-optimal. Schema2 would be better in that case. (Think of School academic departments/students for the above mentioned example where each student has a department associated with him. Which one is better? Schema1 ? Schema 2)

If both value1 and value2 go together (say SSN,FirstName,LastName) Schema1 (one table) is ideal

The general rule of thumb is you would use a denormalized schema(table1) for readonly/or mostly read only Tables and schema2 (2 tables) for write only/mostly write (or the number of writes exceed reads by a very large value) tables

If text was a very large document on a regular basis and even some of the time it did not need to be access with the other two columns I would seperate them. For a very small number of rows you won't see much difference. Try several million rows and the amount of work it takes to scan a table become significanly different. None of the rules for data normalization would cause you to split them. http://www.bkent.net/Doc/simple5.htm is an execellent reference. Like so many questions the answer is dependent on the details. If all these questions had a simple answer the DBMS could do and you would not have to even think about it.

Vertical partitioning does improve performance: http://www.ovaistariq.net/2010/06/performance-tuning-using-vertical-partitioning/