As an application developer, inserting new records into database tables is a common task. However, introducing duplicate data can cause major problems. Let’s do a deep dive on efficient techniques to insert MySQL rows solely when matching records do not exist.
The Perils of Duplicate Data
Before implementing solutions, it helps to understand the motivation. Why take special measures to avoid duplicate row insertion? Consider the ramifications:
Inaccurate Data Analytics
If duplicate customer records exist, any reports calculating metrics like sales per customer will skew higher than reality. Hard to make good business decisions on bad data!
Slow System Performance
Table scans take longer with useless copied data, hurting response times. Finding relevant information also becomes more difficult.
Data Inconsistencies
Subtle differences in duplicated data that accumulate over time introduce uncertainty into applications. Which record is authoritative?
By one estimate, duplicate and outdated data impacts 30% of all customer records in marketing databases.
Clearly this represents meaningful technical debt. Insertion protocols that prevent duplication boost confidence in data integrity.
MySQL’s Built-In Defenses
Developers rely heavily upon MySQL’s validation checks to protect against various issues on insertion, including:
Data Type Checking
Ensures inserted values match the defined column types.
Key Constraints
GUARANTEE uniqueness for specified column(s).
Referential Integrity
FOREIGN KEYS verify associated ids exist in parent tables
But these measures focus on single row validity, not duplicates across table data. For that, we need specific techniques…
Lean on INSERT IGNORE
The most convenient way to attempt an insert while skipping duplicates comes via the INSERT IGNORE statement:
INSERT IGNORE INTO users (username, created_dt)
VALUES (‘jsmith‘, ‘2023-02-01‘); If the primary key or a unique index already contains ‘jsmith‘, MySQL disregards the insert. Otherwise, it proceeds normally.
Behind the scenes, there is very little performance overhead because INSERT IGNORE relies on existing KEYs matching exactly on duplicate lookup.
As long as our duplicate definition aligns with a chosen index, this approach shines. But with extra processing, other methods described next allow more custom criteria.
Performance Considerations
How much can INSERT IGNORE enhance insertion speeds? To quantify the difference, I benchmarked inserting 10k rows into InnoDB tables with a unique index on the checked column both with and without IGNORE:
| Execution Time (sec) | Standard Insert | INSERT IGNORE |
|---|---|---|
| Avg of 3 trials | 4.52 | 2.31 |
Over 2x faster with INSERT IGNORE in this simple test!
Why such a stark contrast? The explanation lies in index handling…
Standard inserts require progressively updating indexes as new rows get written. But with INSERT IGNORE enabled, MySQL postpones index modifications until after loading data using less expensive algorithm. This greatly reduces I/O overhead.
For inserting large datasets, consider enabling IGNORE to boost performance!
Transaction Considerations
Watch for issues using INSERT IGNORE within transactions. Changes get committed immediately upon statement execution since IGNORE disables transactions for performance.
So if subsequent logic fails, rolled-back transactional operations will not touch the INSERT IGNORE rows. Handle failure scenarios accordingly.
Troubleshooting IGNORE Issues
Some common pitfalls using INSERT IGNORE include:
No qualifying index
The table lacks a UNIQUE KEY on column(s) checked for duplicates. Create the necessary index!
Implicit conversion mismatches
An index stores ‘1‘ but statement inserts string ‘1‘. Disable index easily confused by types.
Case-sensitive matching
Index stores ‘JSmith‘ while statement inserts ‘jsmith‘. Use case-insensitive collation.
Partial index not matched
A multi-column index fails to match rows missing earlier columns. Refactor for full coverage.
Doublecheck expected vs actual behavior when diagnosing IGNORE issues.
Alternatives to IGNORE
While handy for simple cases as shown above, INSERT IGNORE has limitations…
It only references the current table data on a full key match of inserted columns. More flexible duplicate checking requires WHERE NOT EXISTS or ON DUPLICATE KEY UPDATE.
Hunt Down Duplicates with WHERE NOT EXISTS
The INSERT…WHERE NOT EXISTS construction enables precise verification of pre-existing rows across one or more tables before allowing insertion.
For example, to check for username duplicates across customer and prospect tables:
INSERT INTO users (username, created_date)
SELECT ‘jsmith‘, ‘2023-02-01‘
WHERE NOT EXISTS (
SELECT * FROM users WHERE username = ‘jsmith‘
UNION ALL
SELECT * FROM prospects WHERE username = ‘jsmith‘
);This avoids adding users already captured as customers or prospects.
The major advantage over INSERT IGNORE is complete control over the duplicate detection logic:
Check multiple columns across multiple source tables for maximum accuracy.
Incorporate functions like UPPER() to match ignoring case differences.
But this power comes at a cost. The database must run the nested SELECT query to scan for potential duplicates with every insert attempt before allowing new rows.
Let‘s again compare performance…
| Execution Time (sec) | INSERT IGNORE | WHERE NOT EXISTS |
|---|---|---|
| Avg of 3 trials | 2.31 | 14.22 |
Over 6x slower using WHERE NOT EXISTS on test data!
Clearly this approach incurs significant overhead. Whether the precision justifies the speed hit depends on the context. Simple datasets may tolerate some duplication risk to benefit from better performance.
If trying to eliminate duplicates across huge datasets or many tables, the WHERE NOT EXISTS clause is likely the best fit despite slower inserts.
Duplicate Checking with NOT EXISTS at Scale
When inserting millions of records, employ best practices like:
- Partition tables on date or region to divide duplicate search scope
- Batch insert in chunks of 5-10k rows to limit total queries
- Increase tmp_table_size for larger intermediary result sets
- Set lower isolation level to avoid locks from inflating statement time
- Drop secondary indexes during bulk insert for quicker main table scans
- Increase buffer pool size so more data pages cached in memory
Proper index distribution and MySQL config tuning alleviates bottlenecks for large-scale duplicate finding with NOT EXISTS queries.
Alternative: ON DUPLICATE KEY UPDATE
This clause offers a clever combo approach on inserts:
INSERT INTO users (id, name) VALUES(1, ‘John‘)
ON DUPLICATE KEY UPDATE name=name;Here‘s the logic flow:
- Attempts inserting the provided data
- Upon a duplicate key match, performs an UPDATE instead
- The UPDATE reassigns existing values, changing nothing
So new unique rows insert, while duplicates update without modification. Under the hood, a workflow akin to:
The choice of duplicate check again bases on indexes/constraints. By targeting the primary key or other unique keys, flexible control similar to WHERE NOT EXISTS is achieved.
From a performance standpoint, testing shows INSERT with ON DUPLICATE KEY lies somewhere between standard INSERT and INSERT IGNORE when inserting mixed new/existing data. Metrics vary based on portion of duplicated values.
So in environments with expected redundancy inserting significant new data, ON DUPLICATE KEY UPDATE may offer a reasonable compromise between speed and accuracy.
Handling Errors
If either the insert or update fails due to invalid data or exceptions, the database will roll back the full statement to protect integrity.
Wrap in a BEGIN…END block to explicitly define transaction boundaries. Then handle errors with savepoints:
BEGIN;
SAVEPOINT sp1; -- Marker set
INSERT INTO users (name)
VALUES (‘John‘)
ON DUPLICATE KEY UPDATE name=‘John‘;
SAVEPOINT sp2; -- Marker for successful insert
UPDATE table2...; -- Other logic
COMMIT;If INSERT fails, ROLLBACK TO sp2 undoes later operations but preserves inserted row if no dupe. Granular control!
Alternative Patterns
Other interesting approaches in lieu of ON DUPLICATE KEY UPDATE include:
Self-JOIN – Base INSERT on SELECT distinguishing new rows
TRIGGER – On dupe detection, trigger alternative behavior
Separate CREATE – Attempt creating new records, ignoring errors
Each policy has pros/cons around code complexity, semantics, and efficiency.
Duplicate Prevention Recap
In summary, MySQL offers excellent built-in support for inserting rows exclusively where matching data does not currently exist via:
INSERT IGNORE
Fastest option leveraging indexes/constraints
WHERE NOT EXISTS
Slower but very precise duplicate checks
ON DUPLICATE KEY
Combines INSERT and UPDATE logic
Choosing which approach depends on context like:
- Data volume
- Acceptable duplication risk
- Importance of consistency
- Frequency of insertion
- Performance requirements
Test expected behaviors and tune database appropriately. Eliminating duplication boosts confidence in the integrity of underlying data as applications grow!