Unanswered: Increase speed and decrease resource usage
I have a site that runs a pretty extensive database using MySQL. It is a search engine of sorts and is coded in perl. The backend makes, on average, 5 to 10 database calls per second. The majority of these calls being UPDATE's. The rest would be either, SELECT, DELETE or INSERT, etc..
The problem I am having is that, at times, the mysql database chews up 99% of my cpu for an extended period of time. I am looking for ways to possibly illiviate this. I have already added LOW_PRIORITY to my UPDATE calls so they only happen when no other calls are being made. I currently do not use indexes because I am under the impression that indexes speed up SELECT calls but slow down UPDATE calls. Is this true?
I also have a front end website written in perl that provides a search interface to the database. So at any one time, I probably have UPDATE's, SELECT's, DELETE's and INSERT's all happening at the same time. I regularly OPTIMIZE my tables to remove overhead.
The system that is running all this is an AMD 3500+ 64 bit processor with 1 gig of PC2700 DDR RAM. The database has 11 tables with 218,000 records currently. The size is only 22.2 MB as of now.