There is a page on my clients website which he has asked me to make faster. It's a profile page and queries the db about 100 times to get user data (...!). I didn't code this page so it is a bit hard for me to work out if I can optimise it much. Are there any ways to increase the load time for mysql queries that do not require script/code modification?
WTF??? what on earth is it doing to do 100 queries on a single page load???? Rewrite the page... at a minimum move to stored procedures but look at correcting the underlying problems (though of cause it may require other changes too - though if the DAL is separate as it should be then it shouldnt be too time consuming to do)
100 queries to get the user data for one page is very excessive and should be the main target of optimisation. Other than that you can: - check that db has appropriate indexes. - Increase RAM
If there is a way you can spread the load out across multiple servers, you can use database replication in MySQL to have several servers in sync with the same data so that queries can be spread across them.
You've got to rewrite it, anything else if just storing up problems for the future. The only alternative to that I'd consider would be to cache the page i.e. generate the page and store it then server that stored page to the users ... that will only work if the page is relatively static and the stored page doesn't need refreshing often.
While I agree that you should re-write the code, I would bet that you could fix the worst of the SPEED problem with the proper use of indexes. PM me if you would like further help.
best speed fix with least work is writing caching for the pages; even with optimizing and removing most queries, under heavy load database backed pages are not really scalable without proper caching.
Unacceptable. You need to structure this db properly and have it carry ID' and indexed entries to pull the data. Having 10 mysql calls on a single page is just silly & embarassing.