Post by lovac24 » Sat Aug 20, 2011 11:51 pm

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

TECHNODISCOUNT24 - BUY WITH ALERTPAY AND MONEYBOOKERS
Free Shipping Worldwide and Cheapest Prices
Buy Gadgets on the best Alertpay Shop


New member

Posts

Joined
Wed Mar 31, 2010 5:59 pm

Post by lovac24 » Wed Aug 31, 2011 8:09 pm

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

TECHNODISCOUNT24 - BUY WITH ALERTPAY AND MONEYBOOKERS
Free Shipping Worldwide and Cheapest Prices
Buy Gadgets on the best Alertpay Shop


New member

Posts

Joined
Wed Mar 31, 2010 5:59 pm

Post by uksitebuilder » Wed Aug 31, 2011 8:17 pm

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` ) ;

ImageImageImageImageImage

For Friendly Professional Support - Click Here


User avatar
Guru Member

Posts

Joined
Thu Jun 09, 2011 11:37 pm
Location - United Kindgom

Post by lovac24 » Thu Sep 01, 2011 2:20 pm

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.

TECHNODISCOUNT24 - BUY WITH ALERTPAY AND MONEYBOOKERS
Free Shipping Worldwide and Cheapest Prices
Buy Gadgets on the best Alertpay Shop


New member

Posts

Joined
Wed Mar 31, 2010 5:59 pm

Post by IP_CAM » Wed Jul 16, 2014 1:43 am

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

Code: Select all

ALTER TABLE `product_tag` ADD INDEX ( `product_id` ) ;
ALTER TABLE `product_tag` ADD INDEX ( `tag` ) ;
Ernie

I am no longer active at this Forum.
Please don't send Personal Messages, contact: jti@jacob.ch
---
Github OC Downloads: https://github.com/IP-CAM
2'000+ FREE OC Extensions from the World's largest Github OC Archive.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by nvedia » Thu Jul 17, 2014 7:38 pm

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

Donate here to show support if you think I have helped you today!

Opencart Documentation


User avatar
Active Member

Posts

Joined
Sun May 22, 2011 12:54 pm

Post by IP_CAM » Thu Jul 17, 2014 8:54 pm

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

I am no longer active at this Forum.
Please don't send Personal Messages, contact: jti@jacob.ch
---
Github OC Downloads: https://github.com/IP-CAM
2'000+ FREE OC Extensions from the World's largest Github OC Archive.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by tri1976 » Thu Jul 17, 2014 11:06 pm


email: trile7 at gmail dot com
Checkout My Extensions


User avatar
New member

Posts

Joined
Mon Mar 08, 2010 2:48 am

Post by cwswebdesign » Sat Jul 19, 2014 9:22 pm

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

Have you tried this personally?

DL

This account is inactive. Look for us under the name 'EvolveWebHosting' and contact us under that username.

Thanks!


User avatar
Active Member

Posts

Joined
Sun Dec 11, 2011 12:26 am
Location - USA

Post by tri1976 » Sat Jul 19, 2014 9:26 pm

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

email: trile7 at gmail dot com
Checkout My Extensions


User avatar
New member

Posts

Joined
Mon Mar 08, 2010 2:48 am

Post by cwswebdesign » Sat Jul 19, 2014 10:13 pm

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

This account is inactive. Look for us under the name 'EvolveWebHosting' and contact us under that username.

Thanks!


User avatar
Active Member

Posts

Joined
Sun Dec 11, 2011 12:26 am
Location - USA

Post by IP_CAM » Sat Jul 19, 2014 11:30 pm

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/
Last edited by IP_CAM on Sun Jul 20, 2014 12:54 am, edited 1 time in total.

I am no longer active at this Forum.
Please don't send Personal Messages, contact: jti@jacob.ch
---
Github OC Downloads: https://github.com/IP-CAM
2'000+ FREE OC Extensions from the World's largest Github OC Archive.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by cwswebdesign » Sat Jul 19, 2014 11:49 pm

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

This account is inactive. Look for us under the name 'EvolveWebHosting' and contact us under that username.

Thanks!


User avatar
Active Member

Posts

Joined
Sun Dec 11, 2011 12:26 am
Location - USA

Post by cwswebdesign » Sun Jul 20, 2014 3:00 am

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

This account is inactive. Look for us under the name 'EvolveWebHosting' and contact us under that username.

Thanks!


User avatar
Active Member

Posts

Joined
Sun Dec 11, 2011 12:26 am
Location - USA

Post by tri1976 » Sun Jul 20, 2014 3:02 am

Shouldn't affect upgrade.

email: trile7 at gmail dot com
Checkout My Extensions


User avatar
New member

Posts

Joined
Mon Mar 08, 2010 2:48 am

Post by IP_CAM » Sun Jul 20, 2014 3:33 am

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
Last edited by IP_CAM on Fri Feb 19, 2016 5:16 am, edited 4 times in total.

I am no longer active at this Forum.
Please don't send Personal Messages, contact: jti@jacob.ch
---
Github OC Downloads: https://github.com/IP-CAM
2'000+ FREE OC Extensions from the World's largest Github OC Archive.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by victorj » Tue Jul 22, 2014 11:06 pm

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%

Koeltechnische deurrubbers eenvoudig online op maat bestellen.
Alle niet stekplichtige onderdelen zoals scharnieren, sloten, randverwarming en verlichting voor alle typen koelingen en vriezers.
https://koelcel-onderdelen.com


User avatar
Expert Member

Posts

Joined
Sat Jun 25, 2011 4:09 am
Location - Alkmaar Holland

Post by IP_CAM » Tue Jul 22, 2014 11:57 pm

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
Last edited by IP_CAM on Wed Jul 23, 2014 3:33 am, edited 15 times in total.

I am no longer active at this Forum.
Please don't send Personal Messages, contact: jti@jacob.ch
---
Github OC Downloads: https://github.com/IP-CAM
2'000+ FREE OC Extensions from the World's largest Github OC Archive.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by melbagnato » Wed Jul 23, 2014 12:20 am

Again, love your work Ernie.

- Mel


Sent from my iPad using Tapatalk

http://online.enterpriseconsulting.com.au

Site with OpenCart extensions & code downloads, many new extensions coming soon!
Follow us on twitter for more updates

Image


User avatar
Active Member

Posts

Joined
Wed Jan 13, 2010 1:39 pm
Location - Melbourne

Post by victorj » Wed Jul 23, 2014 3:13 am

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

Code: Select all

##enable compression
SetOutputFilter DEFLATE

<FilesMatch "\.(css|js)$">
Header set Cache-Control "max-age=172800, public, must-revalidate"
</FilesMatch>

<IfModule mod_expires.c>
ExpiresActive On
ExpiresByType image/jpg "access 2 month"
  ExpiresByType image/gif "access 2 month"
  ExpiresByType image/jpg "access 2 month"
  ExpiresByType image/jpeg "access 2 month"
  ExpiresByType image/png "access 2 month"
  ExpiresByType text/css "access 2 month"
  ExpiresByType application/x-javascript "access plus 2 month"
  ExpiresByType text/javascript "access plus 2 month"
  ExpiresByType application/javascript "access plus 2 month"
  ExpiresByType image/x-icon "access plus 12 month"
  ExpiresByType image/icon "access plus 12 month"
  ExpiresByType application/x-ico "access plus 12 month"
  ExpiresByType application/ico "access plus 12 month"
</IfModule>
## EXPIRES CACHING ##



# set conenection keep-alive
<ifModule mod_headers.c>
    Header set Connection keep-alive
</ifModule>
much interested in what id is doing on your sites

Koeltechnische deurrubbers eenvoudig online op maat bestellen.
Alle niet stekplichtige onderdelen zoals scharnieren, sloten, randverwarming en verlichting voor alle typen koelingen en vriezers.
https://koelcel-onderdelen.com


User avatar
Expert Member

Posts

Joined
Sat Jun 25, 2011 4:09 am
Location - Alkmaar Holland
Who is online

Users browsing this forum: No registered users and 9 guests