I don't think that someone can give you magic numbers like the acceptable number of columns, or information too strictly related to your workload, like if it is a good idea to split the table. There are too many variables: number/types of existing columns and indexes, number of queries, how many columns you read per query, and so on. Proper tests will give you a good answer. All we can say is that, yes, common sense says that such a table should be split if possible.
But then, every query will need to read from multiple partitions? Every new row will have matches in all partitions? This could slow down your application. Ideally, most of the queries should be able to read from only one partition. So the first suggestion would be to check with developers if some queries can be rewritten so that they will read less columns - possibly by rewriting some SELECT *
.
You also ask about the best possible match condition. Here the answer is easy: join by primary key. All matching rows should have the same id
. It should be an AUTO_INCREMENT
columns on only one table. You first insert new rows into the table, then to the others, in this way:
INSERT INTO t1 (...) VALUES (...);
INSERT INTO t2 (id, ...) VALUES (LAST_INSERT_ID(), ...);
INSERT INTO t3 (id, ...) VALUES (LAST_INSERT_ID(), ...);
Also don't add foreign keys, because they will slow down your writes.