Hank's Place

I've been working on a CGI script that needs to write anywhere from a few to a few thousand entries into a few tables in a MySQL database. The fun part is that I'm tracking foreign keys between the tables (ie, tableA.id can be found in tableB.tableAId and tableB.id can be found in tableC.tableBId). So I need to know the last id value inserted into tableA or tableB before I can write into tableB or tableC. Luckily for us this can be obtained with the following query: "SELECT LAST_INDEX_ID() FROM tableA". This is supposed to give the last id value written into tableA.

When I was testing the performance of the page, it was inconceivable slow. The page was timing out before I could even write 20 values to the database. That's just weird. I thought it might be something to do with the way I was formatting my INSERT statement. But doing an INSERT by hand from the MySQL prompt showed that a typical INSERT from my CGI was about as fast as you would expect. So I tried retrieving the lastest id from tableA and it took a while. And was, in fact, returning a list of all the ids from tableA with the latest one as the first in the list. So a table with around 70,000 entries was returning a list of 70,000 numbers. This isn't, as far as I know, what is supposed to come back from that query. So in my usual kludgy style I changed it to "SELECT MAX(LAST_INDEX_ID()) FROM tableA" and that's really fast. Now the CGI is usable.

I should, in fairness mention that I'm using the MySQLdb module to connect to MySQL from Python. But even from the MySQL prompt itself I'm getting that behavior so I don't think it's a synergistic reaction between Python and MySQL.