Page 3 of 3

Posted: Sat Jul 26, 2014 7:50 am
by melbagnato
Wow, the slowest? I guess marketing really is more important than reality!

Re: Need somebody to optimize database

Posted: Sat Jul 26, 2014 11:45 am
I didn't look for the problem yet, compared to the default code, it's just like Mr.French forgot to loosen up the (hidden) handbrake, Citroen's where always nice, but a little different to 'manipulate'..., compared to others. ;D ;D

I added a small cat-cache option to the stuff, but I need to double the Products to see bigger differences. ... 2bfb70b861

but for now, good night


PS: The Site uses 100x100px thumbs, the other one 80x80, added times 120 makes a REAL KB/loading-difference.., as you see!

Re: Need somebody to optimize database

Posted: Sun Jul 27, 2014 2:04 am
here is the latest, testing two sites, Category Product counting removed and Cat-Caching (Test!) enabled
by use of this:

found here: ... ding-speed

downloadable from here as well: ... 11#p500211 uses my (20hour, including making me whise!) VqMod Counting-Removal as well as Category-Caching Mod, has both Mod's implemented in Source. Both Sites NOW have 10'000 Products + ~1'500 Images installed.

But the Software is NOT default OC 1.64, for yet unknown reason, it act's slower
in general page loading. It's the 'Opencart France' Version v.
Also, not both Sites use the same physical amount of images, check the
test results for the amount of 'Server-Requests' required, as well.

They both load the same amount of Product Listings, about 250 Products each.
You will see, that has a much longer starting delay, but the GOTO-Product
Buttons are active for a long ahead of, so, something delay's it very
much, before finishing the Page. It's the start-delay difference of, resulting
in the OVERALL Pageload Difference. Me, Idiot! , I should have used the same software,
would have avoided a lot of explanations, but I figured, overclocked means OVER..., not UNDER...

See the Test Result: ... 317f42e803


And the result of with / without the 2 VqMod's working, compared with my latest OpenShop Version,
to be seen here:

all loaded with 250 Product Listings in the Cat-Page tested: ... 86f53ceb27


PS. I will make a test with by use of disabled VqMods next.

Just to mention it, I got my new DB down from ~17000 Lines to 4472 Lines, after removing all that Country/Region Code Stuff, now I'm down to 3 Countries and their Regions. Things like that should not be ignored as well, when looking for Power..., the DB needs to contain and handle valuable Data, not Configuration-Surplus.

I have to look into the speed-matter, because, other than that, the frenchmen has done a REAL good job!
Still, choosing OpenShop Admin gives me all the Tools I need to work, without searching for and installing
additional Mods. I got an unmodified engine for it. And it's much faster from scratch, compared with the fench Version , as you see in my latest Movie above. They faster two's use the same 2 Mods.

Re: Need somebody to optimize database

Posted: Mon Jul 28, 2014 9:59 am
before it get's out of my brain and hands, I want to publish my somewhat 'final' findings the the Performance Matter.
It's my Number 500 Posting as well, meaning, that I spend way to much time around here, trying to act smart...
I gotta get some rest.


Yesterday, I started up with a stock Version v.1.5.6.x, seamless and nice integrated into a OpenShop v1.7 ADMIN Surrounding, because it's, for me, the Number ONE Admin Solution, most useful things are in it alrady. Before Upload, I removed lots of Language-related Sections/Files, because the Software has many Languages included by default as well.

After Installation, I created a backup of the DB, thenI removed a lot of language/Zone/Country related (default) content, so reducing the physical size of the (default) DB down to 25 percent of is former Size, be Use of the OpenShop DB-Admin Tool.

I installed my 'Cat-Page-Counting-Preventer', in short, CPCP-VqMOD, the Category-Caching has been implemented into Code, but I am still unsure, if/what this realy add's to speed (yet).

Then, I made a DB Backup again, it's an easy Job, due to the built-in OpenShop Admin Msql-Manager.

Then, I added 5'000 Products and made some Tests, then 15'000 Products, by Use of iSense's famous Auto-ProductsGenerator. I did not touch the DB, indexwise, up to 5'000 Products, it was not required. At least not on my (shared) hosted Server.

Then, I downloaded this .htaccess-file, it works very well and it's fast. ... n_id=15448

Then, I used ATOMIX INNO-DB-Tool to FIRST INDEX ONLY my existing OC default MyISAM DB and tested it again. It's the lower button, and can be used for perfect MyISAM DB Indexing as well!!!

Then, I added and activated the 'Load jQuery from Google CDN' and used the ATOMIX DB-TOOL to change
the existing myISAM DB to INNO-DB Engine Mode, and indexed it one more time, just to make sure...

I removed some (hidden/inactive by default) VqMods, built-into OpenShop, then, regardless of beeing active or not, they are, at least countwise, part of the Game, if they are placed 'on Board'.

Then I tested a little, it usually takes some time, 'til the indexing of the stuff tarts to 'act', after checking tough all Cat's and SubCat's with the highest Selection number used, one actually indexes everything, then, all of a sudden, you're gonna be surprised.

MOVIE LEFT: 5'000 items, ISAM DB, believed indexed, but not sure...
MOVIE CENTER: 15'000 items, ISAM DB, indexed
MOVIE RIGHT: 15'000 items, INNO-DB, indexed, Google JS active ... 067f04f7b0

Much more could be done, but the 'steps' will get 'smaller' as well. But, who really need's 15'000 Products to show? Most Users will never have such an amount of Item available, and for them, it's not a Problem to do the same I did. But it takes a reliable, solid and efficient Server, it's the only way to go...

'rph - OpenCartHelp_com' has created a very good Mod, believed, doing most what mine does too: ... 0Disabling

'Cache category data to speed up page load for store with many categories and sub categories' bei 'Weismann Web'
as well as 'Load jQuery from Google CDN' from 'TransPacific Software' can be found somewhere around here, and ATOMIX and the link to his place has been mentioned before. ... ogle%20CDN

Conclusion: A Shut-off-Switch should shut off, not just supress..., it's like on a TV, still hot!

You all have a good week !
Will be back..

250 Products on Page, good enough...I'm now going back to Basics, trying to get rich quick!

Re: Need somebody to optimize database

Posted: Thu Jul 31, 2014 4:14 am
by villagedefrance

Very interesting speed tests in this topic.
I am also working on improving page loading time in Opencart all the time. I admit that I don't have it always right, but I'm getting there. It is an on-going project of mine.

I see you have tested my first Overclocked Edition, and yes, maybe it wasn't up-to-scratch, but I think that I have improved it big time in the last few weeks. I have just completed a new version today ;D

Here is a preview :

Let me know if you need the packaged zip to run your tests (if you do).
This version might surprise you.

The Frenchman

Re: Need somebody to optimize database

Posted: Thu Jul 31, 2014 12:23 pm
sorry, I forgot your name. Long ago, when I spent two years in Canada, we called 'em so..., don't take it personal, it sounds so nice! To your Release, let me see it packed with Products, anything else is just smoke in the sky. Since I use this nice little (iSense) Mass-ProductsGenerator, I started to have fun, again, after slowly feeling, it would be better to clean out my Server, this Product-Generator sure is one of the best tools available for a honest Shop-Builder. This way, real tests are possible, and it only cost some 25 bucks or so. Not much money, compared to a small Steak and Fries, no Drink included, in Restaurants where I poison the Earth...


One more hint:
Look on this Page, mostly at the bottom end, you see a piece of Script, enabling one to have
a Cache Killer Button in the Admin Main Section. It's very need to have this. I only had to disable the
Image Cache Function, because I did not want the server to redoo them every time after a click, but the other
Cache Sections are nice and clean after. IDEAL for 'cached' multi-language Shop-Developpers/TestersI
I was looking for this for some time, and was required to use Google to translate a lot of russian,
but it worked on spot. ... stit-kesh/


UPDATE 15.Aug.2014

The OC France Test Shop contains 12'000 active items now.
It's equipped with 1'880 sample Product images in multiple (shared) use.
Therefore, my Tests do NOT represent a real Shop Setup, using many more
individually -loaded/cashed/used- images per Product.

Most Images are beeing called trough a '' URL.
Took me a day to make it work, after reading myself trough uncounted Q&A Sites and Error-Messages...

DB-Driver used:

Code: Select all

define('DB_DRIVER', 'mysqli');
define('DB_CACHED_EXPIRE', 120);
With the exeption of Switzerland and Liechtenstein, all Countries and Zones have been removed from the Database,
so removing ~4'200 never used, just space- and time- consuming DB-Code-Lines.

Google JS:
I replaced the internal against an external JS-Routine from Google in
the >template/common/header.tpl<:

Code: Select all

<script type="text/javascript" src="catalog/view/javascript/jquery/jquery-1.11.1.min.js"></script> 
with the one below, it was the only one matching I found:

Code: Select all

<script type="text/javascript" src="//"></script>
this also saves (usually) a couple hundred of milliseconds. So far, I did not run into problems...

Besides of using a sligtly modified RABBIT .htaccess file in the shop section, I usually have a 'general' (1795 Lines!) 70kB .htaccess file installed in my Server Root Directores, to avoid 'unpleasant' visitors to enter my Sites. When removing it, Page Execution is about 1.5 Seconds faster. The Tests shown on video have been made without this 'Big Mother of Security' in Place.

Besides of an efficient .htaccess file (like the Rabbit-one) it's not the DB Engine (myISAM / InnoDB), it's the (FULL DB) indexing, what makes the biggest difference. I just tested the french Version with it.

@villagedefrance: Put the 'Engine Chooser' back into your free v. package, but add Atomix Full DB Indexer as well, and you got it all made with your lovely OC-Version! Your nice v. 'Engine-Chooser' allowed me to make the tests! Thank you.

Latest test Results of a category Page on each Site, containing 300 Product-listings.
Movies can be found here: ... 5c0bd49562


You see, with a few, relatively easy Changes done, one just needs a decent shared Server, and a Shop works well.
Just don't overload the Place with timconsuming Sidebar-Stuff (like LATEST), check on everything, after you installed it.


This is the latest OC-France 240 Product-Listing Category Page Test, (without the BIG MOTHER ROOT HTACCESS INSTALLED)

That's it, from my place, to this Matter, but I wanted to show the results to make you feel better..., after 'beeing' not so nice further up with your work... :D

Good Luck to all from the rainy Switzerland!
All the best



Category Page tested below with 220 Product Listings. (BIG MOTHER HTASSESS INSTALLED)
Average Results, depending on traffic, I took the second best out of 5 Test-Results to display it here.

Re: Need somebody to optimize database

Posted: Tue Sep 02, 2014 7:23 am
by helloyall
Hi, how do you install image optimizer into Opencart, like Stripper or Riot There's no tutorial out there and I looked everywhere without luck. Any help is appreciated. Thanks!!

Re: Need somebody to optimize database

Posted: Tue Sep 02, 2014 9:46 am
Your'e talking about different things, you cannot install Windows Programs/Tools onto a Server Shop Software.

OpenCart has it's own built-in 'image-reducer', every 'image setting' made in the Admin System Section will have OC to create size-matching Product Image-Files. So, basically, you don't need any WINDOWS image tools, if you already have Images, sized anywhere from 300 - 700 Pixel Width, with file sizes not larger than 100KB (300px max.width or height) up to 200KB (700px max.width or height)

If you have larger Images (in Size or data-volume) to be used, then read here on how to REDO them locally, before uploading 'em to the server:

If anything is still unclear, then, you'll need to make yourselfs familiar with details, lots of Information is available at the Web.
Good Luck


Re: Need somebody to optimize database

Posted: Tue Sep 02, 2014 11:39 am
by helloyall
Ah, I see. It makes sense now. Thank you very much!! ;D

Re: Need somebody to optimize database

Posted: Sun Sep 28, 2014 11:23 am
ok, ladies and gentlemen, I have some more News!

First, I found some more information on use of SubDomain-Names to better 'balance' the Browser-Loading.
Since I already have my product images 'subdomain - called', I added another Subdomain-Name, called '', to my Domain-Sub-List.
Then, I changed the

Code: Select all


content as such:

Code: Select all

<link rel="stylesheet" type="text/css" href="" />
<?php foreach ($styles as $style) { ?>
<link rel="<?php echo $style['rel']; ?>" type="text/css" href="<?php echo $style['href']; ?>" media="<?php echo $style['media']; ?>" />
<?php } ?>
<script type="text/javascript" src=""></script>
<script type="text/javascript" src=""></script>
<link rel="stylesheet" type="text/css" href="" />
<script type="text/javascript" src=""></script>
<?php foreach ($scripts as $script) { ?>
<script type="text/javascript" src="<?php echo $script; ?>"></script>
<?php } ?>
thereby, momentarely unaware of simultaniously disabling my VQMOD-controlled query-api's, and it produced,
at the end, better results, compared with the same, subdomain-modified setup, but by use of Google JS-Api's,
as it can be seen here:
(left Video Subdomain JS+CSS - right Video Subdomain JS/CSS BUT Google 2x Jquery API)

The result was this, a very impressive 100%, well balanced result: ... ry&path=25

I have to be honest, the speed itself does not come from this alone, a short while ago, a fine Contributor has donated two very interesting Cache-related Mod's, it's fantastic, how easy they are implemented and how well and efficient they work. because of this, they need to be mentioned here:
This should round up this Discussion well, cached Product Counting, cached Pages, indexed Databases, I get 250 Products (out of 10'000 in the DB) one a Page in 2 Seconds now, even with a lot of (usually rather loadtime-consuming) Specials included, this is all most of us ever need. Adding an efficient htaccess file and rework all images, before beeing uploaded, everybody can operate a very fast OpenCart Shop.

DB-Engine InnoDB had a slight speed-Advantage over MyISAM-DB during my Tests, BUT MUCHO MUCHO More important, try to set ALL Table-Sections to be driven by the same Engine, combined MyISAM/InnoDB Operation seems obviously to be possible and it even MAY be 'the Case' (I found one!) by use of Mod's with hard-coded DB-Installation Information. This certainly would not add to general DB-Performance anyway, so check on this, I found the Tool in the Free Mod OC Section.
Good Luck to all

Test Shop:

Re: Need somebody to optimize database

Posted: Sun Sep 28, 2014 10:26 pm
by Cue4cheap

While I am interested is speeding up my site, I am also a bit overwhelmed by all the text.

Do you have a point by point of what you found? Something like:
1) Run Turbo = it does...
2) Change this database setting = it does...
3) Add xyz = it does...



Re: Need somebody to optimize database

Posted: Mon Sep 29, 2014 2:07 am
Cue4cheap wrote:IP_CAM... While I am interested is speeding up my site, I am also a bit overwhelmed by all the text.
I am not a solution provider, I just collect 'common, found' knowledge, test Stuff and publish my findings and/or results here. This, to make it easier for other Open Source GPL 'Hobby-Type-Scripters' like me, to speed up their Software. But this is, where it ends as well, it's neither purpose nor goal for anyone to build turnkey solutions and spread 'em, just for the fun of it, all over the world, for free. Especially under the aspect, that, installing OC on a Server, one sure plans to create Income, not just fun.

OC-Users without Programming Knowledge can chose from a Variety of professional Supporters, ready to serve their Needs. This is, how Enonomy works. Fortunately, this Place here is not much 'knowledge-regulated', we are very free to publish even relatively 'classified' Information and Knowledge. But this does not mean, that everything here should be presented in a simple >PHP for Dummie's< like, 'copy/paste/work' Scheme, it would, as a consequence, just kill the entire Concept, noone ever would care to invest time and brain into an ever empty basket. To reach a Goal, One must pay or one must learn. It's no way around it.

Nothing personal, just trying to 'explain' (in a foreign Language!) my attempts, and expectations, as well, assisting others...

Re: Need somebody to optimize database

Posted: Mon Sep 29, 2014 2:22 am
by Cue4cheap
IP_CAM wrote:
Cue4cheap wrote:IP_CAM... While I am interested is speeding up my site, I am also a bit overwhelmed by all the text.
I am not a solution provider, I just collect 'common, found' knowledge, test Stuff and publish my findings and/or results here. This, to make it easier for other Open Source GPL 'Hobby-Type-Scripters' like me, to speed up their Software. But this is, where it ends as well....................

Nothing personal, just trying to 'explain' (in a foreign Language!) my attempts, and expectations, as well, assisting others...
I agree but you fill your posts with so much info and reading older post and new posts they read to conflict or overlap with different directions. So I figured if you wouldn't mind putting them in a "To this point I have done... and it is the best practices I have found so far". But I guess you are not that type of person. No offense.


Re: Need somebody to optimize database

Posted: Mon Sep 29, 2014 5:37 am
your'e absolutely right, this was an enormous one, I try to get shorter, next time!
best regards,

Re: Need somebody to optimize database

Posted: Fri Oct 10, 2014 10:10 am
by gtoc
Hi, old thread but after some info on indexing. Prob a very noob question but i'm new to database and coding and all my searching didn't clear this up for me.

I have run the ADD INDEX sql queries posted by uksitebuilder and my site had considerable improvement. I would like to know what the correct method is for keeping these up to date? as time goes on, new and deleted products, orders etc do I need to run the same query again or is the a 'RE INDEX', or do i just run it once?

Thanks in advance for any help.

Re: Need somebody to optimize database

Posted: Tue Oct 14, 2014 8:23 am
You should reindex from time to time, especially, if you added DB-Tables or so. It will not do any harm.

Re: Need somebody to optimize database

Posted: Mon Oct 20, 2014 3:15 pm
by gtoc
Thanks, ernie.

Do I just run the same ADD INDEX or is there another command when reindexing?

Re: Need somebody to optimize database

Posted: Thu Oct 23, 2014 3:45 am
Just press the Atomix turbo.php Index DB Button again, it does no harm...


Re: Need somebody to optimize database

Posted: Mon Dec 07, 2015 5:39 am
oops, this is an old Section, somehow, I somehow have missed your reply...

But just take a look at one of my Sites, I have placed most of the MOD's, to
get top Results, on their front Pages. The best Product Counting is one, that exists,
visibly, for the good of the Visitors, but caching this loosens up all the brakes, related with such. ... 45#p593279
More informations on Speed and Performance: ... 37#p592237

Good Luck
check Page Source header Content for some insight info on linking that stuff...