Member Avatar for an00p

I am working on a PHP project in which a mysql table needs to be updated with lots of rows in a single update.

A set of parameters will be assigned for a product.

Please find my present steps below:
1. Delete all rows with the product_id in the table
2. Insert data into it. (Say 10no of rows)

I am looking forward to have the following setup
1. Create a temporary table.
2. Insert data into the temp table.
3. Update the original table

Which method do you think is better? Which one uses less resource?

  • 2 Contributors
  • 2 Replies
  • 287 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by an00p

Dani AI

Generated

Short summary and recommendation: for a small set of rows (around ten) the simplest option—DELETE the product's rows and do a single multi-row INSERT—is perfectly acceptable and often fastest to implement. For anything larger, concurrent, or where row identity/foreign keys/triggers matter, ’s three-way sync (delete only removed rows, update existing, insert new) is the safest and most efficient long-term approach. The three-way sync can be implemented either with a temporary table plus three set-based statements inside a transaction, or with a bulk “upsert” (INSERT ... ON DUPLICATE KEY UPDATE) if a unique key exists on (product_id, detail_id).

Temp-table + transactional sync (robust; avoids row churn)

CREATE TEMPORARY TABLE tmp_params LIKE MyTable;
-- bulk-load new parameter set into tmp_params (INSERT or LOAD DATA)

START TRANSACTION;

-- remove rows that are no longer present
DELETE m
FROM MyTable m
LEFT JOIN tmp_params t
  ON m.product_id = t.product_id
 AND m.detail_id  = t.detail_id
WHERE m.product_id = ? AND t.product_id IS NULL;

-- update values that exist in both
UPDATE MyTable m
JOIN tmp_params t
  ON m.product_id = t.product_id
 AND m.detail_id  = t.detail_id
SET m.value = t.value;

-- insert new rows
INSERT INTO MyTable (product_id, detail_id, value)
SELECT t.product_id, t.detail_id, t.value
FROM tmp_params t
LEFT JOIN MyTable m
  ON m.product_id = t.product_id
 AND m.detail_id  = t.detail_id
WHERE m.product_id IS NULL;

COMMIT;

Bulk upsert (simpler, fast when a unique key exists)

ALTER TABLE MyTable ADD UNIQUE KEY ux_product_detail (product_id, detail_id);

INSERT INTO MyTable (product_id, detail_id, value)
VALUES (?, ?, ?), (?, ?, ?), ...
ON DUPLICATE KEY UPDATE value = VALUES(value);

Practical notes and cautions: prefer InnoDB so transactions work and row-level locks reduce contention; create the composite unique index needed for upserts; temp tables are session-scoped and can be indexed for faster joins; avoid REPLACE if preserving row_id or foreign-key relations matters (REPLACE deletes then inserts). For heavy deletes consider table fragmentation (OPTIMIZE TABLE if needed). Measure with EXPLAIN and test under realistic load. Given ’s small set, a simple DELETE+multi-row INSERT will work now; if that grows or concurrency issues appear, move to the temp-table or upsert pattern described above.

Recommended Answers

All 2 Replies

Member Avatar for AleMonteiro

I'd say that the first on consume less resources, but the second is more reliable.

And I will suggest a third:
- Delete only the records that will no longer exists
- Update the records that already exists but need to be updated
- Insert the new records

The implementation is a little more complex, but the result is the best in my opnion.

Also, you should create the sql stataments and execute them only once. In example:

DELETE FROM MyTable WHERE Product_ID = 1 AND Detail_ID IN ( 1, 2, 3, 4 );

UPDATE MyTable SET Value = 'Something' WHERE Product_ID = 1 and Detail_ID = 5;
UPDATE MyTable SET Value = 'Something Else' WHERE Product_ID = 1 and Detail_ID = 6;

INSERT INTO MyTable(Product_ID, Detail_ID, Value)
    (1, 7, 'Value...'), (1, 8, 'Value...'), (1, 9, 'Value...');
Edited by AleMonteiro
Member Avatar for an00p

Thanks for your help :) . Would definitly try the third one.
With regards to sql statements, I always try to make the sql statements to execute them only once.

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.