Slow queries

[4ecomm]

Hi
Our site is hosted by rackspace and we reported that we were getting an error . . .

****** Sorry!
There seems to be a problem connecting to our database. Please give us a few
minutes to remedy the problem. Thank you. ******

They suggested we let them run "MySQL's slow query log" to identify the problem.
This they did and came back withe following query . . .

select count(*) as total from orders o left join orders_total ot on (o.orders_id = ot.orders_id), orders_status s where o.orders_status = s.orders_status_id
and s.language_id = '1' and ot.class = 'ot_total' and (o.customers_city like '%[email protected]%' or o.customers_postcode like '%[email protected]%' or o.date_purchased like '%[email protected]%' or o.billing_name like '%[email protected]%' or o.billing_company like '%[email protected]%' or o.billing_street_address like '%[email protected]%' or o.delivery_city like '%[email protected]%' or o.delivery_postcode like '%[email protected]%' or o.delivery_name like '%[email protected]%' or o.delivery_company like '%[email protected]%' or o.delivery_street_address like '%[email protected]%' or o.billing_city like '%[email protected]%' or o.billing_postcode like '%[email protected]%' or o.customers_email_address like '%[email protected]%' or o.customers_name like '%[email protected]%' or o.customers_company like '%[email protected]%' or o.customers_street_address like '%[email protected]%' or o.customers_telephone like '%[email protected]%' or o.ip_address like '%[email protected]%');
where [email protected] is the email address of one of our customers.

It seems that this query was generated when we attempted to send a newsletter from Zencart.
And it seems to do such a query for every recipient of the newsletter.

Anybody got any clues as to why the query seems to check all order fields for an email address.
And is there a more efficient piece of code that we might be able to use ?
thanks
4ec

[DrByte]

That particular query only runs when you load the Admin->Customers->Orders page.
It runs every time you refresh the page and it lists the orders in your store.

It has nothing to do with newsletters.


What version of Zen Cart?
Is this a fresh install, or an upgrade? From which version(s)?
What addons/contributions do you have installed?
What version of PHP?
What version of MySQL?
How many products in your database?
How many orders?
How many customers?
What is your site URL?
Who's your hosting company? Are you direct with rackspace or with a reseller?
Is your database hosted on the same server as your website?

[4ecomm]

Hi
Thanks for the email.
We use the Orders page quite a lot.
See below . . .
What version of Zen Cart? 1.3.6
Is this a fresh install, or an upgrade? From which version(s)?
upgrade about a year ago (I guess from previous version)
What addons/contributions do you have installed?
Gift vouchers, PDF factory plus some extra modules (like shipping, paypal etc..)
What version of PHP? 4.3.9
What version of MySQL? 4.1.20
How many products in your database? approx 1300 (2000 with attribute variants)
How many orders? 103000 (approx 300-500/day)
How many customers? approx 60,000
What is your site URL? www.vetuk.co.uk
Who's your hosting company? Are you direct with rackspace or with a reseller?
Rackspace
Is your database hosted on the same server as your website ? Yes

Hope this helps

Thanks
4ec

[DrByte]

Your site runs very fast for the most part, which is to be expected.

I've sent you a PM for more follow-up.