I need a little bit of help from a MySQL-savvy person, so please forgive me the technical mumbo-jumbo following below.

The database update which is necessary for the Antville 1.2 upgrade takes long. Very long. Too long. I started it yesterday at 1:30 in the night and it is still running.

Well, you might say, haven't you written the update script yourself? Aren't you the guy who should know why it's running so slowly?

But I am really pretty clueless right now. Because all I am doing is issueing quite simple select, update and insert statements.

The problem might be, though, that it's Millions of them. During the first few hours everything goes quite smoothly, but after that each bulk of inserts or updates to a table (especially the one containing all the lovely postings of the Antville.org community) is interrupted with a break of a few minutes at first which in the meantime even exceeds ten minutes.

Thus, the update is taking longer and longer and longer. And of course I don't want Antville.org to be down for such a long time.

I already tried different things I found on various websites and in the depths of the MySQL documentation, even with some success in speeding up things a little bit.

But it's still not working out...

So my urgent request is: if you know how to optimize MySQL performance pretty good or you know someone else who does, please please please get in touch with me as soon as possible.

von Inserts bzw. Updates sollten Sie vorher alle Indexe, die nicht unbedingt gebraucht werden, löschen.

Zwar keine Erfahrung mit MySQL, aber sowas ist allgemein. Aber das wußten Sie sicherlich schon, ich wollts aber trotzdem gesagt haben, weil es genau danach klang.

not that i would like to apply for this job (i'm not more than an ambitious amateur in this area, and certainly there are people out there knowing much more), but i guess that upgrading to innodb would probably be a good move before doing the update. mainly because afaik innodb doesn't lock the whole table during inserts/updates/deletes (as myisam does). in addition iirc antville uses a "text" column in AV_TEXT, which might prevent mysql using a much faster in-memory table (more on this).

Was genau hast du denn vor? Kann man irgendwo dein derzeitiges Update-Skript/Query einsehen?