urgent help needed.
it looks like every database query goes through the entire database:
Rows_sent: 123570 Rows_examined: 371018 (see mySQL log below).

Anybody can help with this?

Query_time: 5 Lock_time: 0 Rows_sent: 123570 Rows_examined: 371018

select distinct p.products_id, p.products_image, p.products_tax_class_id, pd.products_name,
p.products_date_added, p.products_price, p.products_type, p.master_categories_id
from zen_products p, zen_products_description pd
where p.products_id = pd.products_id
and pd.language_id = '1'
and p.products_status = 1;

Maybe you've done something to delete all the indexes on your tables?
Or maybe your hosting service is incapable of providing the services you require.

how do i confirm/check?
If that is the case, how do i restore the indexes?

w2e:

how do i confirm/check?
You can see the indexes via phpMyAdmin when looking at each table's structure.
Or you can export structures and compare against the original Zen Cart files.
Or you can hire a professional who handles this sort of thing all the time.

w2e:

If that is the case, how do i restore the indexes?You'll have to manually build them all based on the original Zen Cart structure, or again, hire a professional who is familiar with the process.

update:

We have since moved the site from inmotionhosting to another, and after 1 month, we are now running our own dedicated server.

The conclusion is that the problem is not about if zen cart can handle how many items. The problem is about the hosts! All the hosting companies want is money from you - the more the better! different companies use different tactics.

The highest item count on our site so far is over 103K without a problem. The google webaster tools show our load speed at 0.7 - higher than 93% of the sites. All this has been done without making any changes to zen cart.