# Speed up insert query mysql to use with wordpress

want to insert about 10000 new rows to an existing table which contain 10000 rows already. I need to get the insert id for the query and to use that id in another function inside the loop as in the code below.

foreach($values as$val){
$sql = "INSERT INTO wp_posts (post_author, post_date, post_content, post_title, post_excerpt, post_status,post_modified,post_type) VALUES('".$val[author]."',NOW(),'".$val[content]."','".$val[title]."','".$val[excerpt]."','published',NOW(),'post')";$result = mysql_query($sql);$insertid = mysql_insert_id();
add_post_meta($insertid,$val[metakey], $val[metaval], true ); }  I am fetching the values for$values array from a CSV file. As of now, I can insert only 20 new rows per 3 minutes. Is there anyway to speed up this insertion?

-

Before i continue, the following must be said:

# Stop using mysql_query.

Seriously. There have been very good replacements for going-on a decade now. And considering this code does not appear to escape anything at all, you'd do well to learn to use parameterized queries so that it doesn't have to.

With all that said, though, the SQL might not even be necessary. Is there a good reason you're not using wp_insert_post to, well, insert a post? Looks like it lets you set everything you want to set, and then some. It also returns the post ID, so you don't even have to touch the database directly to get it.

It sounds like the problem is not SQL or WordPress, really. This code should not be as slow as you're describing. If you have a DB server that's not your web server (which is generally the case in shared hosting and such), there will naturally be a bit of slowdown. Generally not that much delay, though, unless the DB is hosted in a totally different place (ie: not from your web hosting company). That, and the possibility that the server (DB or web server, either one) is simply overloaded, are more likely possibilities.

-
I have tried with wp_insert_post but same result as I said then I have replaced wp_insert_post with mysql_query() –  Arun V Kumar Nov 8 '12 at 22:18
@ArunVKumar: mysql_query is about ten steps backwards. Regardless, though, it's not really the SQL or WP that's causing things to be slow. Hold on... –  cHao Nov 8 '12 at 22:22
this is the actual code pastebin.com/NTRzMRBu –  Arun V Kumar Nov 8 '12 at 22:44
@Arun: You do realize this is "Code Review", right? Posting your real code in the question is pretty much the whole point. Truth is, though, this sounds more like a problem for SO. If the code is abnormally slow, that's more a bug than an optimization thing. –  cHao Nov 8 '12 at 22:50
Sorry cHao.. It was possible 900 insertion into wp_posts table as the data in the wp_posts increased its dropped into 30-40 insertions to wp_posts table –  Arun V Kumar Nov 8 '12 at 22:59

I have resolved the issue. It was actually a plugin that made the problem. I have been using "remove custom post plugin"

-