CODEWITHSUNDEEP
mysqldatabase
H123321
H123321

Reputation: 148

Using MySQL's INSERT IGNORE to prevent dumplicate entries (performance issues?)

I have a table where measurements of a sensor are saved. A row contains the value of the measurement, the id (pk and auto increment) and a random number = num (about 10 digits long or even longer). 

CREATE TABLE `table` (
    `id` int(10) UNSIGNED NOT NULL,
    `value` float NOT NULL,
    `num` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Now after some weeks/months the table could contain thousand and thousand of rows.

My Question: My system requires that the random number is unique (two or more measurements/rows with the same random number are not acceptable). Now I have done some research and there is the neat INSERT IGNORE statement. But I'm not sure if it's smart to use it in my case as there might be many many rows after some time and checking all rows in the table for the random number and if it matches the one that has to be newly inserted might be overkill after some time and drastically impact performance?

Any thoughts?

Upvotes: 0

Views: 597

Answers (1)

TanishGupta
TanishGupta

Reputation: 26

Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error. And also use unique UNIQUE constraints on the Random number column. For increasing the performance when you try to retrieve data from table create INDEX for that.

https://www.w3schools.com/sql/sql_create_index.asp

Upvotes: 1

Related Questions