Member Avatar for mpc123

I have a very large table and would need to split into lots of tables.

The large table has lots of fields of which `Model.ID` gives a number of the product and this is repeated lots of times with other fields used in the row.

I need to create lots of new tables based on the Model ID field and its associated fields in that row need to be kept together. So if `Model.ID` = 1614 then a new table will be created and inserted into that will be all the rows that have that.

Obviously i need code that does them all and the `model.ID` dont go in order and are pretty random.


Could anybody help please, as this is way above what i can do php wise.

  • 2 Contributors
  • 3 Replies
  • 109 Views
  • 20 Hours Discussion Span
  • Latest Post Latest Post by mpc123

Dani AI

Generated

Quick summary and practical notes based on this thread: wants to split one big table into many per-model tables; pointed the right direction by extracting each model's rows. Before doing that, consider whether splitting is the right move. Creating hundreds or thousands of physical tables makes backups, schema changes, and queries harder. MySQL partitioning or a proper index on the model column often gives similar performance benefits with far less maintenance.

If separate tables are required, follow a safe workflow: work on a copy/backup, enumerate the distinct model identifiers, pick a consistent, sanitized naming scheme (for example model_1614), create each new table from the original table structure, then move data in controlled batches rather than one huge transaction. Test the process on a small subset first.

Example pattern (conceptual PHP + PDO sketch):

$pdo = new PDO(...);

// get distinct ids (run safely)
$ids = $pdo->query("/* unique model ids */")->fetchAll(PDO::FETCH_COLUMN);

foreach ($ids as $id) {
  $id = (int)$id;                        // sanitize
  $tbl = "model_" . $id;                 // safe table name
  $pdo->exec("CREATE TABLE IF NOT EXISTS `$tbl` LIKE `main_table`");
  $stmt = $pdo->prepare("INSERT INTO `$tbl` SELECT * FROM `main_table` WHERE `model_id` = :id");
  $stmt->execute([':id' => $id]);
  // repeat in smaller batches if needed
}

Important caveats and troubleshooting tips: CREATE TABLE ... LIKE copies columns and indexes but not triggers or foreign-key constraints, so review constraints afterwards. If the model column name contains punctuation (for example a dot), rename it first to avoid quoting confusion. For very large data sets, insert in batches to avoid long locks and oversized transactions. Watch MySQL limits and the table cache when you create many tables — thousands of tables can hurt server stability. Finally, keep a rollback plan and test on a dump before touching production.

Recommended Answers

All 3 Replies

Member Avatar for mpc123

can anybody help with this?

Member Avatar for pritaeas

You can use a query like

INSERT INTO new_table (modelid, column2, column3) SELECT modelid, column2, column3 FROM main_table WHERE modelid = 1614

You can get the unique model id's

SELECT DISTINCT modelid FROM main_table

Hope this will get you started.

Edited by pritaeas because: n/a
Member Avatar for mpc123

that really does help thank you very much

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.