close ad
 
Important WebAssist Announcement
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

PS3 DB Queries taking too long

Thread began 2/16/2011 4:49 pm by avatar8390144 | Last modified 2/19/2011 7:50 am by hmyrick308645 | 2104 views | 6 replies |

avatar8390144

PS3 DB Queries taking too long

We're experiencing some very long DB query times on our PS3 site, and it's slowing the site down considerably. In looking at the mysql_slow_queries log files, some queries are taking over 6 seconds to complete (!!).

We've checked with the host (Bluehost) and they've confirmed that others on the same shared server are not experiencing the same problem. Would there be something about PS3 or our store that would be causing this?

The site is www.boomco.com

Try clicking on one of the items in the product menu.

Sign in to reply to this post

Jason ByrnesWebAssist

Using PHPMyAdmin or another MySQL Administrative tool, try running the following queries on the ps3 database:

ALTER TABLE ps3_productcategories ADD KEY `ProductID` (`ProductID`);
ALTER TABLE ps3_productoptions ADD KEY `ProductID` (`ProductID`);
Sign in to reply to this post

avatar8390144

Running those queries had an immediate effect in speeding up the site. What exactly did those queries do? I'm assuming they refined the DB somehow?

Will I need to run these again, or is this a permanent change?

Thanks.

Sign in to reply to this post

Jason ByrnesWebAssist

they add an index the productID columns of the ps3_productcategories and ps3_productoptions tables.

Those two columns create the relations to the products table. Adding an index to relational columns makes it easier for the database to perform queries where it needs to get data from both tables.


you should not need to run the queries again, once the index is created, it is a permanent change.

Sign in to reply to this post

hmyrick308645

Was having the same problem ...

I am working on a PS4 site with about 350+ products, most of which have 5-8 advanced options. It was excruciatingly slow to retrieve a list of products. I ran these queries (changing the table names for PS4) and the speed was improved dramatically. This should probably be posted as a known issue for sites with a lot of products that have many advanced options.

Sign in to reply to this post

Jason ByrnesWebAssist

Was your store an update from power store 3? We did add the indexes in power store 4 on new installs, but it may not create the indexes on updates.

Sign in to reply to this post

hmyrick308645

Not an update

No, it was a new install. One of the indexes may have been in place but the other query was executed successfully and the speed was instantly improved.

Sign in to reply to this post

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...