MySql: ResultSet clarification

[Chad Clites]

In populating my database, I have the following field and method:

 
private static final String SQL_SELECT_LAST_KEY = "SELECT last_insert_id() AS lastKey FROM term_ids";
 
.....
 
public void insertTerm(Statement stmt) {   
    
   try{
            stmt.executeUpdate("INSERT INTO " + TERM_IDS + " (accession, 
name, definition, ontology, description) VALUES ('" + accessionValue + "', 
'" + nameValue + "','" + definitionValue + "','" + ontologyValue + "','" + 
descriptionValue + "')");   
 
      ResultSet myRecordSet = stmt.executeQuery(SQL_SELECT_LAST_KEY);
            
      while(myRecordSet.next()) {
         id = myRecordSet.getInt("lastKey");
         pr("" + id);  //check output
      }
   }catch(Exception e){
      System.out.println("Term parse error at: " + accessionValue);
      e.printStackTrace();
   }
}

On the first insert, the method prints out:
1.
The second time through the loop, it prints out:
2
2
The third time through the loop:
3
3
3

By the time I get up to 15,000, my app starts getting very slow. I have looked at the docs for ResultSet, but I can't find anything to explain that behavior. As far as I can tell, a new ResultSet should be getting created each time, but it appears that the last_key just gets added to the result set, and all of the elements of the result set are changed to be equal to the last key. Is there a better way of doing this, or some explanation for why the ResultSet acts like it does?

[ July 17, 2006: Message edited by: Chad Clites ]

[edited to add line breaks]
[ July 17, 2006: Message edited by: Jeanne Boyarsky ]

[Jeanne Boyarsky]

Chad,
Suppose you had the main query being "select name from term_ids". You would expect to get one row back for each row in the table, right? That's just what your query is doing. It is calling last_insert_id() once for each row you have in the table.

last_insert_id() returns the same value each time per the reference documentation within the same query. So you get duplicate results. To avoid this, you could add a clause to the sql query to only return one row. Or you could run the query against a system table instead of your table.

[Chad Clites]

Thank you, that makes perfect sense. I am trying an alternate solution. I changed my query to:
"SELECT DISTINCT last_insert_id() AS lastKey FROM term_ids"

That seems to be going quite a bit quicker. I think I will try your idea too, just for comparison, and building my sql vocabulary.

(Thanks for editing my post also)
[ July 17, 2006: Message edited by: Chad Clites ]

[stu derby]

Originally posted by Chad Clites:
Thank you, that makes perfect sense. I am trying an alternate solution. I changed my query to:
"SELECT DISTINCT last_insert_id() AS lastKey FROM term_ids"

That seems to be going quite a bit quicker. I think I will try your idea too, just for comparison, and building my sql vocabulary.

(Thanks for editing my post also)

[ July 17, 2006: Message edited by: Chad Clites ]

SELECT DISTINCT is still wrong; the database is still fetching every row in the term_ids table into memory in order to calculate a result that doesn't at all depend on any of the values in the table. It will get slower and slower as the table grows.

As the MySql documentatation makes clear, here:
http://dev.mysql.com/doc/refman/5.1/en/information-functions.html
you should simply be doing:

SELECT last_insert_id();

MySql is relatively unusual in its allowed SQL syntax.

[Chad Clites]

Interestingly enough, using the following code from the JConnector docs:

 rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");
 
    if (rs.next()) {
        autoIncKeyFromFunc = rs.getInt(1);
    } else {
        // throw an exception from here
    }
 
    rs.close();

Takes the same amount of time as the initial code I was using. In the beginning, roughly 100 inserts were accomplished each second. By 50,000 inserts, it was down to 15 inserts per second. It appears (to my limited understanding) that the same process is still occuring in the background.

Cheers.
[ July 18, 2006: Message edited by: Chad Clites ]

[stu derby]

Interesting. I would guess (and guess is exactly what it is) that it wouldn't be any better with the DISTINCT, or any other way, that the issue is either with the function itself, or with your table definition.

Just exactly what column is your id column, and is it a primary key? Or an indexed column? If it's neither, that's probably why it's slowing down as the table gets beigger.

[Chad Clites]

The id column is the primary key, and it is auto-incremented. My guess is that the system table(s?) holds what I need (as Jeanne already suggested), and I just need to investigate it further.

It's not that I need my app to be overly efficient, but it just makes sense to me that somewhere the last insert id should be stored.

Thanks for both of your input.

[Jeanne Boyarsky]

I agree with sut about select distinct being inefficient. It's not that the data you need is in the system table. It's that you are calling a function that isn't tied to any table. So if you can run it against a table with only one row, it will save processing.