I was having problem with database overload.My hosting provider saying that my website having heavy SQL query. So,how to optimize the database?
Your question by itself is endless to discuss. How did your provider proof his assertion(the answer must be there)? Maybe optimization must be performed on queries, not only on database. Regards.
Check your SQL queries and optimize then. If necessary then optimize the table structures also Delete unnecessary data and tables.
Waht kind a script are you running? If it's a self made script you should try to delete whatever you don't need, or try to use the same information two or three times. Let's say you store the same names and phone numbers three times, you could delete two of the tables to optimize it!
Check all of your queries on the database to try to find a solution. Thats how you are going to optimise your database.
The following articles will help you to optimize your queries and application http://www.sitepoint.com/article/optimizing-mysql-application/ http://www.informit.com/articles/article.asp?p=377652&seqNum=2&rl=1
Making some indexes and ajusting sql queries will see a big difference.Although if a query still is hard and painfull consider making smart tables as i refer to them. Example: if you have 1 million items and you have a 2000 items additions per day but you only want to show 100 most wanted from lasts week additions you can limit the query to select from 7x2000=14000 latest id's.If these is heavy too,insert these ids to another table every week (deleting the old ofcourse) and do your queries there,this is an example more sofisticated things can be done if background scripts make selections and update tables for often use.Mysql is getting slower if has a lot of rows to check so small ammount of rows faster results,indexes are for this purpose but sometimes cannot be used for our needs so small tricks will give us what we want.