On Sunday 14 February 2010 03:15:16 am Rene Veerman wrote: > On Sat, Feb 13, 2010 at 3:46 PM, Joseph Thayne <webadmin@xxxxxxxxxxxxx> wrote: > > In order to make this as "sql server independent" as possible, the first > > thing you need to do is not use extended inserts as that is a MySQL > > capability. If you are insistent on using the extended inserts, then > > look at the mysql_info() function. That will return the number of rows > > inserted, etc. on the last query. > > But as previous posters had pointed out (thanks) i can't see which rows > failed. As i'm dealing with 3rd-party data, that's an issue. > > I also didn't know it was mysql-specific, that multi-insert.. > > And i tried looking up the sql-standard docs, only to find that they > cost over 200 euro per > part (14 parts). > I've sent angry emails to ansi.org and iso.org (commercial lamers > operating under .org, yuck), about how cool a business model that > charges a percentage of profits per implementation would be, instead > of charging high prices up-front for a potentially bad/complicated > piece of spec. > > But back to the problem at hand; it looks like i'll have to forget > about using 100s of threads for my newsscraper at the same time, and > settle for a few dozen instead. > Then i can just do single inserts (per hit) and retrieve the > last_insert_id(). > > One question remains: it is probably not (concurrently-)safe to do a > sql-insert from php and then a last_insert_id() also from php..? > I still have to build a stored procedure to do-the-inserting and > return the last_insert_id()? That's perfectly safe to do as long as it's within the same PHP request. (Well, the same DB connection, really, which is 99% of the time the same thing.) last_insert_id() is connection-specific. I believe (it's been a while since I checked) the MySQL documentation says that last_insert_id() with a multi-insert statement is not reliable and you shouldn't rely on it having a worthwhile meaning anyway. Or at least it said something that made me conclude that it's safest to assume it's unreliable for a multi-insert statement. If you're concerned about performance of that many bulk writes, there's 3 things you can do to help: 1) Use InnoDB. It uses row-level locking so lots of writes doesn't lock your whole table as in MyISAM tables. 2) Disable indexes on the table in question before running your bulk insert, then re-enable them. That's considerably faster than rebuilding the index after each and every insert as they only need to be rebuilt once. 3) If you're on InnoDB, using transactions can sometimes give you a performance boost because the writes hit disk all at once when you commit. There may be other side effects and trade offs here, though, so take with a grain of salt. --Larry Garfield -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
