We have a database with some large tables, and we're having trouble getting all the processes involved to all run quickly simultaneously.We have three different queries for pages that we need to run more quickly, balancing that with one import script. For instance, adding some indexes to our largest table speeds up the page loads, but slows down the import. We're looking for an approach that will let all the processes run as quickly as possible.
We also recently changed our tables from myISAM to innoDB, which has caused some other problems to arise. Now we're getting locking errors that we didn't get before. We're also getting another strange error: we have a Perl import script that reads from several data files into our database. There's one section of code that checks whether a given record already exists, and if not, inserts it. Now, early during the import script we usually get an error that we've tried to insert a given record twice (the table has a unique index using two of the columns). This may also be related to some changes we made to the Perl script, but given the nature of the changes made, that doesn't seem likely.
We'd like someone to take a look at our database, page queries, and import script and advise on whether we need to take a different approach, or how we can improve our existing one.