MY site was hosted by Bluehost.com.
bluehost suspended mysite because my site causing performance problems)
The bluehost supporter tell me that i can check ~/tmp/mysql_slow_queries to see which queries are running too long and then i will need to optimize queries, properly index tables, set up caching and fix any errors.
then i checked the mysql_slow_queries.

the log is

Mon Jan 12 23:41:19 2009

Query_time: 14 Lock_time: 0 Rows_sent: 1575 Rows_examined: 1575

use database_ej;
SELECT /*!40001 SQL_NO_CACHE */ * FROM zen_orders

Mon Jan 12 23:41:32 2009

Query_time: 2 Lock_time: 0 Rows_sent: 5109 Rows_examined: 5109

SELECT /*!40001 SQL_NO_CACHE */ * FROM zen_orders_products

Mon Jan 12 23:41:46 2009

Query_time: 14 Lock_time: 0 Rows_sent: 1234 Rows_examined: 1234

SELECT /*!40001 SQL_NO_CACHE */ * FROM zen_orders_products_attributes

Mon Jan 12 23:41:55 2009

Query_time: 9 Lock_time: 0 Rows_sent: 5533 Rows_examined: 5533

SELECT /*!40001 SQL_NO_CACHE */ * FROM zen_orders_status_history

Mon Jan 12 23:42:34 2009

Query_time: 38 Lock_time: 0 Rows_sent: 1530 Rows_examined: 1530

SELECT /*!40001 SQL_NO_CACHE */ * FROM zen_paypal

Mon Jan 12 23:43:59 2009

Query_time: 80 Lock_time: 0 Rows_sent: 2599 Rows_examined: 2599

use database_ej;
SELECT /*!40001 SQL_NO_CACHE */ * FROM zen_paypal_session

Mon Jan 12 23:44:19 2009

Query_time: 7 Lock_time: 0 Rows_sent: 2112 Rows_examined: 2112

SELECT /*!40001 SQL_NO_CACHE */ * FROM zen_products_description

I don't know what happened and how to fix the problem?

Any help will be appreciated

There are few bad things here:

• SQL_NO_CACHE queries
• retrieving all records from tables (although it shouldn't take so much time, those queries should take up to 0.5 sec)
• simple queries take waaaaay too much time

What contributions You have installed? Zen Cart doesn't send SQL_NO_CACHE queries by default. As far as I know using SQL_NO_CACHE should be used only for debug/performance measures.

But, even in case of queries with SQL_NO_CACHE option, it seems that Your database server is overloaded or hard drive where Your database resist is broken. Retrieving 1500 rows from orders table takes 14 sec, it's impossible (really, really IMPOSSIBLE), especially that this table isn't locked by other query (take a look at Lock_time, it's always 0).

Unless script that executes that queries is called few times per second, performance problems are not Your fault. This query:
"select * from orders"
means more or less "read 500kb of data from some file and send it to user", no computation is done on retrieved data. As You probably guess, retrieving 0,5mb on modern servers shouldn't present any performance issues.