0

I have a MySQL database with 40 columns (int, float, text, enum, timestamp) and about 1 million rows. When I add a new data array of about 1000 rows with PHP, i need to check the database for existing rows with the exact same data before I insert a new row. What's the best way to do that?

Steven
  • 1
  • 3
  • https://stackoverflow.com/questions/4165195/mysql-query-to-get-column-names you can use COUNT() on this query. – Adam abdul shakoor Jul 11 '19 at 14:50
  • 1
    Create a UNIQUE index. Then execute INSERT IGNORE. – Strawberry Jul 11 '19 at 14:58
  • @strawberry insert ignore can be a bad suggestion as ignore ignores anny possible error.. not something you might want – Raymond Nijland Jul 11 '19 at 15:11
  • @Steven Why not first create an array of unique columns data set in PHP and then simply insert? – nice_dev Jul 11 '19 at 15:17
  • @RaymondNijland Surely better than the race condition which exists in Steven's presently envisaged scenario. – Strawberry Jul 11 '19 at 15:28
  • I agree @strawberry but i wasnt talking about the unique index? – Raymond Nijland Jul 11 '19 at 15:43
  • I thinks it's not clear what i mean. My database has columns like "name", "street", "zip" and 37 more. When i add a new data set, i want to check my database for an existing entry before. I have columns with just 1 different value. So i could do a `SELECT id FROM table WHERE name = $newdata[name] && street = $newdata[street] && zip = $newdata[zip] ... and 37 more` but i have 1 million rows in my database and sometimes i need to insert a new dataset with 1000 rows. I think searching the whole database one thausend times and copare all columns is not the best way. – Steven Jul 12 '19 at 15:11

0 Answers0