Fast inserting or updating data from huge JSON to MYSQL with php <p>In a project of mine I have a problem updating my mysql db from a huge json. the json object in question consist of ~1000 entries like this:</p> <pre><code>[{ "uniqueId":"578742901714e", "lat": -76.760541, "lng": 121.289062, "description":"foo and bar", "visible": true }] </code></pre> <p>In order to compare the data in the json to the database, I used different statements enclosed in different loops. While this worked it took some time ~45 seconds for the whole json to be processed and written.</p> <p><strong><em>Edit:</em></strong></p> <p><strong>To clarify, I loop through the array returned by json_decode() and compare the data directly to the Database via SELECT and INSERT STATEMENTS - which works but is slow as hell, because the data is huge</strong></p> <p>Because the user has to wait for this to ensure data integrity, I decided this should be sped up.</p> <p>So I looked using INSERT INTO ON DUPLICATE KEY UPDATE, which should work with multiple data sets.</p> <p>But here's the real problem: Because some entries like "description" are saved in their own table I need something like LAST_INSERT_ID() to know the auto-increment id of the just inserted dataset. But LAST_INSERT_ID() doesn't work that way and only returns the first row if it is used in a batch INSERT. (<a href="http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_last-insert-id" rel="nofollow">Documentation</a>)</p> <p>So I'm stuck between a functioning but very slow execution and a non-functioning but (supposedly) very fast execution.</p> <p>I don't really know how to proceed, can you help me?</p> <hr> <p><strong>Edit:</strong></p> <p>The database in question consists of some tables. The most prominent ones are the following two:</p> <p>table poi:</p> <pre><code>#1 id int auto-increment primary #2 unique_id varchar(13) unique #3 date int(11) #4 visible tinyint(1)/bool </code></pre> <p>table poi_description:</p> <pre><code>#1 id int auto-increment primary #2 poi_id int(11) #3 description text #4 date int(11) #5 user int(11) </code></pre> <p>I read so much in the last two days that I'm really not sure how to update the rows if the uniqueid is already in the table. I thought, I could get it to work with INSERT ON DUPLICATE UPDATE, but that looks only for the primary key - at least that's how it looks to me.</p>