Combine tables with one-to-one relations

I have four tables which store a lot of information about something. This information can be di开发者_C百科vided into four categories. One category of information is queried always and the other three occasionally.

Since there is a one-to-one relation between all of these tables they could be stored in one table.

My question is: what would be better performance wise?

• One table with queries like SELECT id, x, y, z FROM all and SELECT id, u, v FROM all

• Multiple tables with queries like SELECT * FROM xyz and SELECT * FROM uv

(Where id, u, v, x, y and z are columns and all is a table with all columns and xyz and uv are tables with corresponding columns)

That really depends.

In theory, relational databases were made to have efficiently relations between tables. So SELECT a.x, a.y, a.z FROM a should be as fast as SELECT a.x, a.y, b.z from a JOIN b ON a.x = b.x if a.x and b.x are both the same primary key (eq_ref join).

Examples:

• Selecting multiple sequential rows from one big table will be slightly faster than from two small tables thanks to the physical location of the records on the disk.

• Unless the different tables are on different hard-disks, in which case MySQL can fetch them in parallel.

• But this can also be done with one table, using PARTITIONING.

• If certain queries are run more often than others, you might want to optimize for those first. Maybe a covering index solves your problem?

• Using multiple tables also repeats the primary key. But then again, so do indexes on InnoDB.

• With multiple tables, it's harder to maintain integrity. You'll be using FOREIGN KEYS, and transactions for your inserts (or even triggers?)

Conclusion:

it really depends on your situation. The best way to find out is to test and benchmark.

Depends upon many factors.

Normally one would not do SELECT * FROM xyz and SELECT * FROM uv when you can just as easily do SELECT * FROM xyz INNER JOIN uv ON xyz.id = uv.id when there is a 1-1 relationship. Even if a relationship is optional, you could use a LEFT JOIN just as effectively.

Normally one will not do SELECT * and instead only select columns based upon need.