Queries taking 18 mins

[petemc]

We've got a large store that is constantly running slow. Its hosted on Pair who are meant to be pretty good, but it takes about 18 minutes to run something like;

 

select distinct p.products_id, pd.products_name from products p,

products_description pd, products_to_categories

 

The site goes from slow to down on a good day. Any tips/ideas? As far as I'm aware its on a dedicated server.

[ozcsys]

We've got a large store that is constantly running slow.  Its hosted on Pair who are meant to be pretty good, but it takes about 18 minutes to run something like;

The site goes from slow to down on a good day.  Any tips/ideas?  As far as I'm aware its on a dedicated server.

<{POST_SNAPBACK}>

 

Have you contacted the hosting company to see if they can give you any imput? That should be the first step. There are some contributions and code changes to help speed your store up. Check in the tips section and the contribution section of the site.

[petemc]

Have you contacted the hosting company to see if they can give you any imput? That should be the first step. There are some contributions and code changes to help speed your store up. Check in the tips section and the contribution section of the site.

<{POST_SNAPBACK}>

 

We've contacted Pair and they said;

 

Hello,

 

As per your conversation via phone, your server was having problems due

to

the MySQL activity.  This query in particular took 18 minutes to

process.

 

select distinct p.products_id, pd.products_name from products p,

products_description pd, products_to_categories

 

I saw that each table involved with this query was indexed.  So that

basically eliminates that option.  Second, you could review what the

query is suppose to do and possibly revise it to speed the processing

up. 

Third you could upgrade the memory again and then allocate the newly

added memory amount to MySQL.  This should help MySQL.

 

It wasn't as helpful as I'd hoped. We currently have 512mb ram in the machine. I'll take a look in the tips section.

[Guest]

vhat version of mysql and php ison your server?

[Guest]

Install my modify tep_db_query useful data and osC-Explain contributions to help debug the nasty queries. It will allow you to see exactly which queries are causing the slow load times and also give you details on how to correct them.

 

Bobby

[petemc]

vhat version of mysql and php ison your server?

<{POST_SNAPBACK}>

 

PHP Version 4.3.10

 

MySQL Version 4.0.17

 

Install my modify tep_db_query useful data and osC-Explain contributions to help debug the nasty queries.  It will allow you to see exactly which queries are causing the slow load times and also give you details on how to correct them.

 

Bobby

<{POST_SNAPBACK}>

 

I've installed the modified tep_db_query function and pretty much every SQL command is taking forever. Theres no real pattern. I couldn't install osC-Explain as it redirected me to the admin forbidden page every time. I didn't setup this system so I don't know why its doing that.

[jamesepain]

as far as i know it sounds like a very slow server

[Guest]

...and so begins the query optimization.

 

To install the osC-Explain you can upload the install script to the catalog directory to get around the admin access.

 

Are you using osCommerce or CRE Loaded?

 

Bobby

[petemc]

...and so begins the query optimization.

 

To install the osC-Explain you can upload the install script to the catalog directory to get around the admin access.

 

Are you using osCommerce or CRE Loaded?

 

Bobby

<{POST_SNAPBACK}>

 

osCommerce. I'll try putting osC-Explain in another dir :)