OpenCart Community

Hallo,

in the last time our Website is loading very slow and sometimes we are getting errors from My SQL. We have contacted the Hostgator where the site is hosted and they told us that a database have much querys and thats why it is very slow.

They said as following:

In order to get your site to load faster, you will need to pay for a programmer or web developer to optimize your script. We do not provide that service as we are only a hosting company. A VPS account will provide better performance than a shared account, however there are limitations based on the code that will not be corrected by simply getting a more powerful server.

SO IS THERE SOMEBODY HERE WHO CAN MAKE IT FOR US BECAUSE WE DONT HAVE A SKILLS TO DO THAT. THANKS

Hallo,

is they somebody here who can optimise a database for our Alertpay Shop TECHNODISCOUNT24.

We have a hosting by the Hostgator on the VPS server. It happend offten that the site is not available with a error message Internal Server Error. The guys from the Hostgator have told us that we need to optimise out database because they are to many querys to database or something like that. So please can somebody help us with a optimisation and we would pay for this. Thanks

If you are running a 1.5.x OC store, try running the following SQL queries in your database:

uksitebuilder wrote:If you are running a 1.5.x OC store, try running the following SQL queries in your database:

Code: Select all

ALTER TABLE `category` ADD INDEX ( `parent_id` ) ;
ALTER TABLE `category` ADD INDEX ( `top` ) ;
ALTER TABLE `category` ADD INDEX ( `sort_order` ) ;
ALTER TABLE `category` ADD INDEX ( `status` ) ;
ALTER TABLE `option` ADD INDEX ( `sort_order` ) ;
ALTER TABLE `option_description` ADD INDEX ( `name` ) ;
ALTER TABLE `option_value` ADD INDEX ( `option_id` ) ;
ALTER TABLE `option_value_description` ADD INDEX ( `option_id` ) ;
ALTER TABLE `order` ADD INDEX ( `customer_id` ) ;
ALTER TABLE `product` ADD INDEX ( `model` ) ;
ALTER TABLE `product` ADD INDEX ( `sku` ) ;
ALTER TABLE `product` ADD INDEX ( `upc` ) ;
ALTER TABLE `product` ADD INDEX ( `manufacturer_id` ) ;
ALTER TABLE `product` ADD INDEX ( `sort_order` ) ;
ALTER TABLE `product` ADD INDEX ( `status` ) ;
ALTER TABLE `product_option` ADD INDEX ( `option_id` ) ;
ALTER TABLE `product_option_value` ADD INDEX ( `product_option_id` ) ;
ALTER TABLE `product_option_value` ADD INDEX ( `product_id` ) ;
ALTER TABLE `product_option_value` ADD INDEX ( `option_id` ) ;
ALTER TABLE `product_option_value` ADD INDEX ( `option_value_id` ) ;
ALTER TABLE `product_tag` ADD INDEX ( `product_id` ) ;
ALTER TABLE `product_tag` ADD INDEX ( `tag` ) ;
ALTER TABLE `url_alias` ADD INDEX ( `query` ) ;
ALTER TABLE `url_alias` ADD INDEX ( `keyword` ) ;
ALTER TABLE `user` ADD INDEX ( `username` ) ;
ALTER TABLE `user` ADD INDEX ( `password` ) ;
ALTER TABLE `user` ADD INDEX ( `email` ) ;

Thank You very much. I will try to do this.

in my OC v.1.5.6.1-4, this Table does not exist:
Ernie

OC v.1.5.6.1 doesnt have product_tag table
In newever versoins tags are stored in the product_description table so you can ignore above sql

nvedia wrote:OC v.1.5.6.1 doesnt have product_tag table
In newever versoins tags are stored in the product_description table so you can ignore above sql

this is what I found out, when checking my DB. But I changed the setup to 'InnoDB' anyway, it's a much more efficient Solution, and indexing is done differently. But thanks for the reply, it may help others as well, looking for 'missing' Tables...

Ernie

Take a look at this script https://github.com/chrisatomix/opencart-turbo

tri1976 wrote:Take a look at this script https://github.com/chrisatomix/opencart-turbo

Have you tried this personally?

DL

cwswebdesign wrote:

tri1976 wrote:Take a look at this script https://github.com/chrisatomix/opencart-turbo

Have you tried this personally?

DL

Yes, I have one very large database setup using this and it helps.

Tri

tri1976 wrote:

cwswebdesign wrote:

tri1976 wrote:Take a look at this script https://github.com/chrisatomix/opencart-turbo

Have you tried this personally?

DL

Yes, I have one very large database setup using this and it helps.

Tri

Thanks for the feedback. I'll make a backup of a DB and try it.

DL

Thanks for the feedback. I'll make a backup of a DB and try it. DL

I's worth doing it, I use it for some time already.
I am just setting up two different (DEFAULT) shops, one is already online, containing 5'018 Products, autogenerated into the existing default Categories. It's equipped with a Msqli MyISAM DB Engine, I had it indexed by use of the MySQL Admin built-in Indexing-Option.

http://www.ipcam.li/shop/

The second shop will be a Default Shop as well, but equipped with the InnoDB-Engine, already powering my IPC.LI Test Shop. But since I have modified so many things already, I need a default shop again to really be able to compare by having all Shops physically placed in exactly the same 'technical' Server-Environment.

Ernie

ipc.li/shop/

Just installed it for v1.5.6.4 and so far so good. I'm still looking around to see if there are any issues but it seems lightning fast so far.

DL

one question: Is this going to effect future upgrades? I know these changes will be lost during an upgrade but they won't prevent an upgrade from being done, right?

DL

Shouldn't affect upgrade.

I have finished my Tests, made with 2 , just for that installed, Test Shops, I wanted to find out, in Detail,
how much difference ATOMIX DB-Mod would make, if used without ANY OTHER Changes or Mod's, I already
implemented into my (iPC.Li) Test Site. I used ATOMIX Mod for some TIme already, so I knew, it works well.
But looking at the Results, Opencart Turbo is, for me, THE NUMBER ONE Speed-MOD!

Both OC v.1.5.6.4 Shops compared, contain active 5'019 Products, each one installed on a virgin DB.
Both are fully default, use NO VQMOD, all 3 URL's/Shops tested as shown below are placed on the same Server.

My testshop ( NOW, updated ) also has 5'019 Products, and a few other (time-consuming) Things as well:
http://www.ipc.li/shop/

This Link calls a MySql Admin INDEXED MyISAM driven DEFAULT OC category Page , containing 120 Products:
http://ip-cam.li/shop/index.php?route=p ... ry&path=20

This Link calls an ATOMIX MOD enhanced DEFAULT OC category Page , containing 120 Products:
http://www.bigmax.ch/shop/index.php?rou ... path=20_26

It's done by THIS MOD, it's a very professional designed and easy to handle FREE Tool:
http://www.ipc.li/os/atomix.jpg

here is it:
http://www.ipc.li/os/opencart-turbo-master.zip

--------------------------

LOOK for the Server Page Loading Time at the bottom of the Pages!

COMPARE the amount of 'requests' made and the DATA-Volume of the Pages loaded for each URL, i.E. on the webpagetest-site! Not all Values shown on those Sites are 'easy' to interprete, but generally, it displays the reality. webpagetest.org Tests have been made with FULLY LOADED DOCUMENT Settings and FIRST LOAD Results, the SLOWEST way to get results.

Be aware, the 'Content' of an optimized .htacces file used plays an important role when 'peeking' a shop!

I've been doing lots of tests and I found some interesting details.

I will publish Results and Graphics as soon as I sorted it all. The biggest BRAKE is loose now, by use of
Atomics Mod, the Performance of your Place will increase dramatically, if you have very many Products. After
implementing Atonix Mod, no other simple and free 2-Click OC Modification will ever have a similar effect on a large Shop Performance.

What follows, will depend more and more on specific requirements as well. Other 'Places' can be indexed as well, but it only makes sense (for visitors), not eventally leading them to 'not longer valid Results'. It all depends of Shop-Use !!

Caching would then be the NEXT thing to do. But if someone uses a Multi-Language Site, it can get (a little) difficult in Place like the Page-Header, around the (, possibly pre-cached,) TOP-LINE Category Se(le)ction. Still. Bit Categories are just, what would make sense to chache..! ...to mention it, so far, I found no way around this...

You see, it's not done yet, it just started..., every 'percent' starts to count now!

Ernie
learning by (sometimes) screwing up

if product and category count are disabled, add cache rules in htaccess, optimize images using Riot your site can come up toma pagespeed of 90+ %

now oc need image dimensions out of the box, and opencart would be blazing fast.

done this on my sites and they reach, depending on time on gtmetrix between 89 and 91%

I was just looking into this last night, and I downloaded an ImageCompressor,
in Combination with my Stripper, I was able to reduce the Image Size of large Images to
500 pixel width and reduce the Image Quality to 25-30 percent, still getting a good quality.
This way, I have all 1'300 uploaded Test Product Images done nice and well in a few steps.

BUT FIRST, one should CLEAN OUT all images from not used,
hidden, 'Source' Information, be using the famous STRIPPER.
It removes certain 'ID-Content', sure not used by you, and so shrinks the image volume.

A few more advises:

DO NOT USE long Image Title Names, keep 'em as short as possible. They may contain numbers and/or Characters.
AVOID ANY EMPTY TITLE LINE SPACES in Image Title Names under ALL Circumstances!.
DO NOT MIX UPPERCASE/lowercase characters in Image TITLE-Names.
Remember the Keyboard- Click/hold to set Uppercase? Be aware, Things like (_) are UPPERCASE as well.
Servers act alike, every single 'switch' means TWO more (unneeded) Tasks, one for up, and one for down again....
DO NOT have Image Tltle-Names such as this.isit.jpg, using two (.) as Image Name.

It helps, piece by piece... !!

To make it easier for interested peoples, I put 'em on my Server, they are free of Viruses...

downladed as well, but yet untested:
http://www.ipc.li/os/FILEminimizerPictu ... -Setup.exe

2 of my old favoured tools:
http://www.ipc.li/os/PIXresizer.zip
http://www.ipc.li/os/Stripper.zip

got this last night, works well:
http://www.ipc.li/os/MassImageCompressorV1_0.zip

Good Luck

Ernie

Again, love your work Ernie.

- Mel


Sent from my iPad using Tapatalk

Tested all of those image compressors as well, did not use stripper, as i have my information in image files,
ended up with Riot in order to compress pictures, great reduction and no quality loss at all
http://luci.criosweb.ro/riot/?ref=RIOT

@Ernie, did you use a vqmod to reduce database queries, by disabeling category and product count wich makes a heck of a difference?

also put these in your .htaccess and retest speed
much interested in what id is doing on your sites