[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MYSQL INSERT LAST_INSERT_ID(); QUERY SLOW

Posted on 2010-09-10
21
Medium Priority
?
1,218 Views
Last Modified: 2012-05-10
I have a form that inserts records to three different tables than updates my relationship table that holds the row number for each new record with the  LAST_INSERT_ID(); function.

The problem that i am facing is that the query is a little slow. My code might not be up to par. is there  a better way to preform this task

This is what i am doing
after the first record is inserted into table one I do this  

$sql1="SELECT LAST_INSERT_ID(); ";
$sql2="INSERT INTO relate SET relate.BOND_ID=LAST_INSERT_ID();";
//$Result2 = mysql_query($sql1, $CLIENT_LIST) or die(mysql_error());
$Result2 = mysql_query($sql2, $CLIENT_LIST) or die(mysql_error());


second record insert  in table 2 than i
update the relationship table   with the second tables row number

$sql4="SELECT LAST_INSERT_ID(); ";


$sql5="UPDATE relate SET relate.ACCOUNTING_ID=LAST_INSERT_ID();";
//$Result4 = mysql_query($sql3, $CLIENT_LIST) or die(mysql_error());
$Result6 = mysql_query($sql5, $CLIENT_LIST) or die(mysql_error());


than i update the relationship table again


 $ResultX = mysql_query($insertSQL, $CLIENT_LIST) or die(mysql_error());
$sql7="SELECT LAST_INSERT_ID(); ";


$sql8="UPDATE relate SET relate.AGENCY_ID=LAST_INSERT_ID();";
$Result7 = mysql_query($sql8, $CLIENT_LIST) or die(mysql_error());

all the records get inserted in the proper tables the the relationship table is populated correctly it just seems like i am missing something or there could be a better way of doing this any suggestions?

<?php require_once('Connections/CLIENT_LIST.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}



if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO bond (BOND_NUMBER, Surety, Bond_Type, PAYING_STATE, BOND_STATE, TERM, CANCELATION_CLAUSE, Bond_Amt, `1st_Issue_Date`, Issue_Date, Exp_Date, RED_STARED, RED_STAR_DATE, PURGE_DATE) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['BOND_NUMBER'], "text"),
                       GetSQLValueString($_POST['Surety'], "text"),
                       GetSQLValueString($_POST['Bond_Type'], "text"),
                       GetSQLValueString($_POST['PAYING_STATE'], "text"),
                       GetSQLValueString($_POST['BOND_STATE'], "text"),
                       GetSQLValueString($_POST['TERM'], "text"),
                       GetSQLValueString($_POST['CANCELATION_CLAUSE'], "text"),
                       GetSQLValueString($_POST['Bond_Amt'], "text"),
                       GetSQLValueString($_POST['st_Issue_Date'], "text"),
                       GetSQLValueString($_POST['Issue_Date'], "text"),
                       GetSQLValueString($_POST['Exp_Date'], "text"),
                       GetSQLValueString($_POST['RED_STARED'], "text"),
                       GetSQLValueString($_POST['RED_STAR_DATE'], "text"),
                       GetSQLValueString($_POST['PURGE_DATE'], "text"));

  mysql_select_db($database_CLIENT_LIST, $CLIENT_LIST);
  $Result1 = mysql_query($insertSQL, $CLIENT_LIST) or die(mysql_error());

//$sql1="SELECT LAST_INSERT_ID();" ;

$sql1="SELECT LAST_INSERT_ID(); ";
$sql2="INSERT INTO relate SET relate.BOND_ID=LAST_INSERT_ID();";
//$Result2 = mysql_query($sql1, $CLIENT_LIST) or die(mysql_error());
$Result2 = mysql_query($sql2, $CLIENT_LIST) or die(mysql_error());

} 
 
 
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO accounting (QUOTED, COMM_FEE, NET_DUE, Surety_Gross, Surety_Net, AGENT_COMMISSION_PERCENTAGE, FL_FEE, NV_FEE, KY_FEE, RE_INSTATMENT_FEE, ADMIN_FEE, BROKER_FEE, CC_FEE, ON_FEE, CLIENT_NUMBER, HOW_MANY_BONDS) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['QUOTED'], "text"),
                       GetSQLValueString($_POST['COMM_FEE'], "text"),
                       GetSQLValueString($_POST['NET_DUE'], "text"),
                       GetSQLValueString($_POST['Surety_Gross'], "text"),
                       GetSQLValueString($_POST['Surety_Net'], "text"),
                       GetSQLValueString($_POST['AGENT_COMMISSION_PERCENTAGE'], "text"),
                       GetSQLValueString($_POST['FL_FEE'], "text"),
                       GetSQLValueString($_POST['NV_FEE'], "text"),
                       GetSQLValueString($_POST['KY_FEE'], "text"),
                       GetSQLValueString($_POST['RE_INSTATMENT_FEE'], "text"),
                       GetSQLValueString($_POST['ADMIN_FEE'], "text"),
                       GetSQLValueString($_POST['BROKER_FEE'], "text"),
                       GetSQLValueString($_POST['CC_FEE'], "text"),
                       GetSQLValueString($_POST['ON_FEE'], "text"),
                       GetSQLValueString($_POST['CLIENT_NUMBER'], "text"),
                       GetSQLValueString($_POST['HOW_MANY_BONDS'], "text"));

  mysql_select_db($database_CLIENT_LIST, $CLIENT_LIST);
  $Result3 = mysql_query($insertSQL, $CLIENT_LIST) or die(mysql_error());
//$sql3="SELECT LAST_INSERT_ID();" ;
$sql4="SELECT LAST_INSERT_ID(); ";


$sql5="UPDATE relate SET relate.ACCOUNTING_ID=LAST_INSERT_ID();";
//$Result4 = mysql_query($sql3, $CLIENT_LIST) or die(mysql_error());
$Result6 = mysql_query($sql5, $CLIENT_LIST) or die(mysql_error());

}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO agent (AGENCY, AGENT_Address, AGENT_City, AGENT_St, AGENT_Zip, AGENT_FIRST, AGENT_MIDDLE, AGENT_LAST, AGENT_SUFF, AGENT_Phone, AGENT_Fax, AGENTS_EMAIL) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['AGENCY'], "text"),
                       GetSQLValueString($_POST['AGENT_Address'], "text"),
                       GetSQLValueString($_POST['AGENT_City'], "text"),
                       GetSQLValueString($_POST['AGENT_St'], "text"),
                       GetSQLValueString($_POST['AGENT_Zip'], "text"),
                       GetSQLValueString($_POST['AGENT_FIRST'], "text"),
                       GetSQLValueString($_POST['AGENT_MIDDLE'], "text"),
                       GetSQLValueString($_POST['AGENT_LAST'], "text"),
                       GetSQLValueString($_POST['AGENT_SUFF'], "text"),
                       GetSQLValueString($_POST['AGENT_Phone'], "text"),
                       GetSQLValueString($_POST['AGENT_Fax'], "text"),
                       GetSQLValueString($_POST['AGENTS_EMAIL'], "text"));

  mysql_select_db($database_CLIENT_LIST, $CLIENT_LIST);
  $ResultX = mysql_query($insertSQL, $CLIENT_LIST) or die(mysql_error());
$sql7="SELECT LAST_INSERT_ID(); ";


$sql8="UPDATE relate SET relate.AGENCY_ID=LAST_INSERT_ID();";
$Result7 = mysql_query($sql8, $CLIENT_LIST) or die(mysql_error());

}


?

Open in new window

0
Comment
Question by:tomjenkins12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
  • 4
  • +1
21 Comments
 
LVL 8

Expert Comment

by:ropenner
ID: 33649012
In the segment below from your code I think your intention looks to be different than what is coded.

$sql1="SELECT LAST_INSERT_ID(); ";
$sql2="INSERT INTO relate SET relate.BOND_ID=LAST_INSERT_ID();";
$Result2 = mysql_query($sql2, $CLIENT_LIST) or die(mysql_error());

I think you mean to do this

$sql1="SELECT LAST_INSERT_ID(); ";
$sql2="INSERT INTO relate SET relate.BOND_ID=$sql1";
$Result2 = mysql_query($sql2, $CLIENT_LIST) or die(mysql_error());

Since you store the last ID in SQL1 and then don't use it I assume you wanted to use it.  This may be related to the slowness of the INSERT.  Each of your queries is done this way and you do the LAST_INSERT_ID() in the query.  I didn't try your code but I suspect that putting LAST_INSERT_ID() in an UPDATE or INSERT may be causing your slowness.
0
 
LVL 8

Expert Comment

by:ropenner
ID: 33649067
I failed to see that you didn't get the result from the first query.  I think your desired code is like this.

$sql1="SELECT LAST_INSERT_ID(); ";
$Result1 = mysql_query($sql1);
list($last_id_variable) = $Result1->fetch_row();

$sql2="INSERT INTO relate SET relate.BOND_ID=$last_id_variable";
$Result2 = mysql_query($sql2, $CLIENT_LIST) or die(mysql_error());
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 33649135
You don't have a WHERE clause on your UPDATE; therefore, you are in essence updating every row with the last inserted ID.  Additionally, the INSERT INTO ... SET syntax is not standard SQL, so I tend to shy away from that; however, it does work in MySQL.

Anyway, here is what I would expect:

// insert sql for `bond` table
// set variable $bond_id = LAST_INSERT_ID()
// insert into relate (bond_id) values ($bond_id)
// insert sql for `accounting` table
// set variable $acct_id = LAST_INSERT_ID()
// update relate set accounting_id = $acct_id where bond_id = $bond_id


Repeat the bolded section for agent table insert.  Technically, if bond_id is the key you can use to filter which record to update in relate table, you can trim down the second, third, etc. calls by skipping the storage to variable of LAST_INSERT_ID() and just do this:

update relate
set accounting_id = last_insert_id()
where bond_id = $bond_id

Hope that helps.

Kevin
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33649163
Ah, I got distracted while typing my response...I see ropenner has already brought up the point that you need to get the results of last_insert_id and store it in a variable and then use it; therefore, sorry for the repeated information ... hopefully though my explanation helps to clarify the overall flow as I think the "slow" response you are seeing is due to the update statements.
0
 

Author Comment

by:tomjenkins12
ID: 33649253
those are all good suggestions i will try them out and let you know  
0
 

Author Comment

by:tomjenkins12
ID: 33649508
Call to a member function fetch_row() on a non-object in list($last_id_variable) = $Result1->fetch_row();

any suggestions
0
 
LVL 8

Expert Comment

by:ropenner
ID: 33649991
can you post your code again in its Revised form.
0
 

Author Comment

by:tomjenkins12
ID: 33650001
i did just like this

$sql1="SELECT LAST_INSERT_ID(); ";
$Result1 = mysql_query($sql1);
list($last_id_variable) = $Result1->fetch_row();

$sql2="INSERT INTO relate SET relate.BOND_ID=$last_id_variable";
$Result2 = mysql_query($sql2, $CLIENT_LIST) or die(mysql_error());
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33650259
You probably want:

$Result1 = mysql_query($sql1, $CLIENT_LIST) or die(mysql_error());

Given the assumption that $CLIENT_LIST is your connection identifier.
0
 

Author Comment

by:tomjenkins12
ID: 33650287
i just tried that and it is still saying

Fatal error: Call to a member function fetch_row() on a non-object in

list($last_id_variable) = $Result1->fetch_row();
0
 
LVL 8

Expert Comment

by:ropenner
ID: 33650386
the mysql manual says
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html

If the previous statement returned an error, the value of LAST_INSERT_ID() is undefined.

If the code just before 'what you sent us' returns an error then it would explain this error.  Can you show us the code just before or verify that the previous insert did work.

$sql1="SELECT LAST_INSERT_ID(); ";
$Result1 = mysql_query($sql1, $CLIENT_LIST);
list($last_id_variable) = $Result1->fetch_row();
0
 
LVL 2

Expert Comment

by:WilliamStam
ID: 33658138
insert into table (table2ID,table3ID) values ((select ID from table2 where *something that was submitted now* ORDER BY ID DESC limit 0,1), (select ID from table3 where *something that was submitted now* ORDER BY ID DESC limit 0,1))


last id only brings back the last id in 1 table... uness i got it wrong... lol not impossible.. ive had issues with it before
0
 

Author Comment

by:tomjenkins12
ID: 33662766
Ok i think i no what is going on. my bond id is updated with the correct number so are my other two tables but the other two tables are updating the rows below them with the new numbers so i need to do something with my where clause. so it doesn't rewrite over the existing data
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33663547
That is what I was explaining here:

http:#a33649135
0
 

Author Comment

by:tomjenkins12
ID: 33663914
i tried using the code that you provided, but when i used the last_insert_id i would get a undefined function error. i changed it to this


$bondid=mysql_insert_id();


$sql2="INSERT INTO relate SET relate.BOND_ID=$bondid";
$Result2 = mysql_query($sql2, $CLIENT_LIST) or die(mysql_error());

}

$acco=mysql_insert_id();


$sql5="UPDATE relate SET relate.ACCOUNTING_ID=$acco WHERE relate.BOND_ID=$bondid";

$Result6 = mysql_query($sql5, $CLIENT_LIST) or die(mysql_error());

}

$agentid=mysql_insert_id();


$sql8="UPDATE relate  SET relate.AGENCY_ID=$agentid WHERE relate.ACCOUNTING_ID=$acco";
$Result7 = mysql_query($sql8, $CLIENT_LIST) or die(mysql_error());

}


 and it works thank you very much. do you know if there is a problem using mysql_insert_last_id()  instead of insert_last_id ()  ?
0
 

Author Comment

by:tomjenkins12
ID: 33663922
sorry that was directed to mwvisa1
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33664041
That is just a PHP function to get at the same information, so you should be fine.  The above looks a little odd in that it doesn't have the actual inserts for the secondary fields, but assuming your real code does.  Note you can use the $bondid throughout for those so on the secondary queries, you should be able to just do this:

Ref.:
http://php.net/manual/en/function.mysql-insert-id.php

// $acco=mysql_insert_id();
$sql5="UPDATE relate SET relate.ACCOUNTING_ID=LAST_INSERT_ID() WHERE relate.BOND_ID=$bondid";

However, if it is working (i.e., it ain't broke)...as they say, no need to fix it. :)
0
 

Author Comment

by:tomjenkins12
ID: 33664210
I did it like this


if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO bond (BOND_NUMBER, Surety, Bond_Type, PAYING_STATE, BOND_STATE, TERM, CANCELATION_CLAUSE, Bond_Amt, `1st_Issue_Date`, Issue_Date, Exp_Date, RED_STARED, RED_STAR_DATE, PURGE_DATE) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['BOND_NUMBER'], "text"),
                       GetSQLValueString($_POST['Surety'], "text"),
                       GetSQLValueString($_POST['Bond_Type'], "text"),
                       GetSQLValueString($_POST['PAYING_STATE'], "text"),
                       GetSQLValueString($_POST['BOND_STATE'], "text"),
                       GetSQLValueString($_POST['TERM'], "text"),
                       GetSQLValueString($_POST['CANCELATION_CLAUSE'], "text"),
                       GetSQLValueString($_POST['Bond_Amt'], "text"),
                       GetSQLValueString($_POST['st_Issue_Date'], "text"),
                       GetSQLValueString($_POST['Issue_Date'], "text"),
                       GetSQLValueString($_POST['Exp_Date'], "text"),
                       GetSQLValueString($_POST['RED_STARED'], "text"),
                       GetSQLValueString($_POST['RED_STAR_DATE'], "text"),
                       GetSQLValueString($_POST['PURGE_DATE'], "text"));

  mysql_select_db($database_CLIENT_LIST, $CLIENT_LIST);
  $Result1 = mysql_query($insertSQL, $CLIENT_LIST) or die(mysql_error());
 
$bondid=mysql_insert_id();


$sql2="INSERT INTO relate SET relate.BOND_ID=$bondid";
//$Result2 = mysql_query($sql1, $CLIENT_LIST) or die(mysql_error());






$Result2 = mysql_query($sql2, $CLIENT_LIST) or die(mysql_error());

}


if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO accounting (QUOTED, COMM_FEE, NET_DUE, Surety_Gross, Surety_Net, AGENT_COMMISSION_PERCENTAGE, FL_FEE, NV_FEE, KY_FEE, RE_INSTATMENT_FEE, ADMIN_FEE, BROKER_FEE, CC_FEE, ON_FEE, CLIENT_NUMBER, HOW_MANY_BONDS) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['QUOTED'], "text"),
                       GetSQLValueString($_POST['COMM_FEE'], "text"),
                       GetSQLValueString($_POST['NET_DUE'], "text"),
                       GetSQLValueString($_POST['Surety_Gross'], "text"),
                       GetSQLValueString($_POST['Surety_Net'], "text"),
                       GetSQLValueString($_POST['AGENT_COMMISSION_PERCENTAGE'], "text"),
                       GetSQLValueString($_POST['FL_FEE'], "text"),
                       GetSQLValueString($_POST['NV_FEE'], "text"),
                       GetSQLValueString($_POST['KY_FEE'], "text"),
                       GetSQLValueString($_POST['RE_INSTATMENT_FEE'], "text"),
                       GetSQLValueString($_POST['ADMIN_FEE'], "text"),
                       GetSQLValueString($_POST['BROKER_FEE'], "text"),
                       GetSQLValueString($_POST['CC_FEE'], "text"),
                       GetSQLValueString($_POST['ON_FEE'], "text"),
                       GetSQLValueString($_POST['CLIENT_NUMBER'], "text"),
                       GetSQLValueString($_POST['HOW_MANY_BONDS'], "text"));

  mysql_select_db($database_CLIENT_LIST, $CLIENT_LIST);
  $Result3 = mysql_query($insertSQL, $CLIENT_LIST) or die(mysql_error());
//$sql3="SELECT LAST_INSERT_ID();" ;
$acco=mysql_insert_id();


$sql5="UPDATE relate SET relate.ACCOUNTING_ID=$acco WHERE BOND_ID=$bondid";
//$Result4 = mysql_query($sql3, $CLIENT_LIST) or die(mysql_error());
$Result6 = mysql_query($sql5, $CLIENT_LIST) or die(mysql_error());

}

so i should have a problem with multiple people inserting data at the same time?
0
 

Author Comment

by:tomjenkins12
ID: 33664243
I meant shouldn't not should sorry
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33664568
I don't believe so as you should get the id for your session, but I might be thinking of MS SQL's scope_identity, but if you do find this to be a problem later you can always look at MySQL transaction and/or moving this logic to a stored procedure and simply calling the procedure from the PHP page.  This way all the actions happen in one SQL server call and the collisions will be controlled by transaction and locking/isolation level associated.
0
 

Author Comment

by:tomjenkins12
ID: 33664622
thank you very much
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question