CodeVoyager

Huge Commerce Site and DB

Edit Page

Huge Commerce Site and DB

Setting up HHVM with Wordpress - Affiliate Web Designers

Fuck!

Hi – I went through quite a bit of working optimising the underlying SQL queries until I then discovered you can simply remove them – they’re not required at all as all they’re doing is counting products in each category – that happens EVERY page load and updates the database – very inefficient.

Anyway – to stop this abominable code from running and seriously speed up your large woocommerce sites, add this line to your functions.php file:

remove_action( ‘woocommerce_before_shop_loop’, ‘woocommerce_result_count’, 20 );

WordPress › Support » Remove the Product Count on Woocommerce Categories

WordPress › Support » Slow DB Query

Slow Query on large WooCommerce site - Google Groups

Post meta in general seems to be the achilles heel of WordPress at scale and WooCommerce exacerbates the situation by using CPTs and post meta for everything. With the number of products you have… that’s probably where the bottle neck is – since that’s holding meta for products, orders, customers, etc.

Depending on how the site is built you can also look into: Caching some pages (like the main catalog pages) if they aren’t personalized) Fragment caching with W3 Total Cache (e.g. move to alternate hosting like WebSynthesis) if there is personalization throughout the site Serving non account pages as cached files and filling in the personalized bits via ajax call(s)

Can Wordpress Ecommerce handle large catalogs and sales? | Rise Forums - WordPress Forum, WordPress Support, Internet Marketing

http://docs.woothemes.com/document/hooks/

How to remove breadcrumbs, result count, etc from pages in WooCommerce | zach wills

Optimizing WordPress Performance - EfficientWP Speed up your WordPress eCommerce website

WooCommerce for large eCommerce websites, yes or no?

WordPress › Support » POSSIBLE? 500k Products on Woocommerce - Product Comparison Affiliate

Built With WooCommerce - A Gallery of Websites Built with WooCommerce

Caching – WordPress.com VIP

Best practice for hijacking main loop and caching the results : Wordpress

Speedier Async Transients

The idea is simple. Instead of the user waiting for new data when the Transient expires, the system will respond with outdated data but immediately start updating the Transient. This way, everything will be faster, but the first visitor will not have super up-to-date information.

The latest library is 10up/Async-Transients, which enables serving of stale data while new transients are regenerated in the background. For more information, please read the article Improving WordPress Transients on the 10up website.

Improving Third-Party API Performance in WordPress with the TLC Transient Library markjaquith/WP-TLC-Transients

? Maybe not needed ?

Fixing WooCommerce + Memcached - Remaintenance

Woow

Mark Jaquith: Next Generation WordPress Hosting Stack - Summary and highlights - Useful Snippets My WordPress Nginx setup

MySQL query cache overview - Enabling and checking status - Useful Snippets

Modifying the WooCommerce Product Query | Kathy is Awesome Extend WooCommerce Product Search to use Custom Fields | Wordpress Change the sort order of WooCommerce Grouped Products | Kathy is Awesome WooCommerce | Remi Corson Sample products loop | WooThemes Documentation WooCommerce | Document Categories | WooThemes Documentation

Why WooCommerce

Ecommerce Technologies Usage Statistics Lists of Ecommerce Websites and Internet Retailer Sales Trends | BuiltWith WooCommerce vs Magento: Which E-Commerce Platform Is Right For You?

Alternatives?

10 of the Best Self-Hosted Ecommerce Solutions Free Shopping Cart Application and Open Source Ecommerce Solution

WooCommerce Themes

Purchased Themes

Flat Responsive WooCommerce Theme 146 Modern Flat Icons | GraphicRiver Makery - Marketplace WordPress Theme Sistina - Flat Multipurpose Shop Theme SpotLight | Magazine, Reviews & News Portal Camp - Responsive eCommerce Theme

Free Themes

Storefront. The Official WooCommerce Theme Free WordPress Themes | WooThemes 5 Awesome Free WooCommerce Themes - Sell with WP Free WordPress responsive themes | Fruitful

Purchased Extensions

WC Products By Category Widget

Can Wordpress Ecommerce handle large catalogs and sales? | Rise Forums - WordPress Forum, WordPress Support, Internet Marketing 11 Awesome WooCommerce Themes | GPLclub Download Premium WooCommerce Themes For Huge Discounts!

Feeling Lucky? WooCommerce Hits 7 Million Downloads - WooThemes Još jedna statistika: http://builtwith.com/ecommerce/

From Magento to WooCommerce, part 1 | Practical Ecommerce Stock Control and Inventory Software

sample dummy data for shop - Google Search LadenZeile.de: Online-Shops für günstige Mode und Möbel

abantecart.com

WordPress › Demo Data Creator Product Add-ons | WooThemes Documentation Introduction | WooThemes Documentation

Populate with dummy test data

Dummy Content Filler.

Initial import of data:

Import CSV & XML to WooCommerce Products - WP All Import

Free alternative: Woocommerce CSV importer

There are a lot of non-free import/export extensions for WooCommerce dgrundel/woo-product-importer

REST API for Legacy PHP Projects | Toptal Integrating External APIs with WordPress Thermal: A RESTful API for WordPress. json - Wordpress plugin to connect to a REST API? - Stack Overflow WordPress: Interacting with External APIs | Ben Lobaugh Online https://github.com/MartyThornley/yoda-speak?files=1 woocommerce external api - Google Search Integrate WooCommerce & an External Order Management System WooCommerce-REST-API-Client-Library/ at master · kloon/WooCommerce-REST-API-Client-Library · GitHub Aaron Campbell: Integration of WordPress With External APIs | WordPress.tv Getting started integrating WordPress with other APIs woocommerce-external-product-embed/class-woocommerce-external-product-embed-transients.php at master · WPprodigy/woocommerce-external-product-embed · GitHub

SQL Query

The Woocommerce way, from its

  1. Remove pages for Woocommerce: shop_page cart_page checkout_page myaccount_page edit_address_page view_order_page change_password_page logout_page

  2. Purge tables, if they exist:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37


  SET foreign_key_checks = 0;

  TRUNCATE TABLE etkwp_woocommerce_api_keys;
  TRUNCATE TABLE etkwp_woocommerce_attribute_taxonomies;
  TRUNCATE TABLE etkwp_woocommerce_downloadable_product_permissions;
  TRUNCATE TABLE etkwp_woocommerce_termmeta;
  TRUNCATE TABLE etkwp_woocommerce_tax_rates;
  TRUNCATE TABLE etkwp_woocommerce_tax_rate_locations;

  # Clear options
  # DELETE FROM etkwp_options WHERE option_name LIKE 'woocommerce\_%';

  # Remove any Woocommerce relationships
  #
  SELECT relations.*, taxes.*, terms.*
    FROM etkwp_term_relationships AS relations
    INNER JOIN etkwp_term_taxonomy AS taxes
      ON relations.term_taxonomy_id=taxes.term_taxonomy_id
    INNER JOIN etkwp_terms AS terms
      ON taxes.term_id=terms.term_id
    WHERE object_id IN (SELECT ID FROM etkwp_posts WHERE post_type='product');

  # Delete posts + data
  DELETE FROM etkwp_posts WHERE post_type IN ( 'product', 'product_variation', 'shop_coupon', 'shop_order', 'shop_order_refund' );

  # Remove disconnected metadata
  DELETE meta FROM etkwp_postmeta AS meta
    LEFT JOIN etkwp_posts AS posts
    ON posts.ID = meta.post_id
    WHERE posts.ID IS NULL;

  # Delete orders
  TRUNCATE TABLE etkwp_woocommerce_order_items;
  TRUNCATE TABLE etkwp_woocommerce_order_itemmeta;

  SET foreign_key_checks = 1;

Server System Variables - MariaDB Knowledge Base

SET @@session.unique_checks = 0; SET @@session.foreign_key_checks = 0; SET @@global.innodb_autoinc_lock_mode = 2;

mysql -e "set foreign_key_checks = 0;" dbname
mysql -Nse 'show tables' dbname | while read t;
do mysql -e "set foreign_key_checks = 0; truncate table $t" dbname; done
mysql -e "set foreign_key_checks = 1;" dbname



mysql-e "SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0; USE ${db}; SOURCE cleanup.sql; COMMIT; SET autocommit=1; SET unique_checks=1; SET foreign_key_checks=1;"

Clean up a bloated wp_term_relationships table - Scott Nelle

Configuring caching plugins | WooThemes Documentation

Troubleshooting a Slow Site | WooThemes Documentation

Optimizing COUNT queries

WooCommerce – Remove category products count | Remi Corson

remove_action( ‘woocommerce_before_shop_loop’, ‘woocommerce_result_count’, 20 );

WooCommerce extremely slow after WordPress 4.2 update

optimization - Speeding up row counting in MySQL - Stack Overflow

Woocommerce Themes with microdata support:

Purchased themes:

I like also:

Product Importer Deluxe

Consuming external API

WordPress HTTP API Basic Authentication with the WordPress HTTP API

More advanced:

API: The Deal with WordPress Transients

Wordpress REST API

WP API Documentation Extending WP API: eduardoboucas/wp-api-post-groups · GitHub

WordPress › Datafeedr WooCommerce Importer Supported Networks & Merchants | datafeedr

Blog - Affiliate Web Designers

Drop Shipping integration with product synchronization via XML feed

icecat

ICEcat product specifications drawer - PHP - Snipplr Social Snippet Repository php - What is a good model, using Open IceCat to import data and populate products in a a catalog’s database - Stack Overflow

EAN-Search.org - Barcode database with over 80 million products

data request - Does there exist a global database of all products with EAN 13 barcodes? - Open Data Stack Exchange

Woow: 4003537810179

Commercial - best: Pricing & Plans | Semantics3

Vladan Colovic completely free for both commercial and non-commercial use EAN 4003537810179 | Barcode Lookup SimpleUPC - Product UPC, Info, Nutrition, and Images GEPIR | Services | GS1 - The global language of business

POD - Product Open Data - navigate Small: Datakick: The Open Product Database

Master MySQL | Blog of Morgan Tocker

my.cnf/my.cnf_32GB at master · RodrigoViolante/my.cnf · GitHub The best MySQL Settings for 32GB RAM on a Dedicated Server - Database Administrators Stack Exchange XtraDB/InnoDB Server System Variables - MariaDB Knowledge Base

MariaDB I/O capacity with innodb_io_capacity parameter - Database Administrators Stack Exchange mysqlslap

echo '
[mysqld]
innodb_buffer_pool_size = 18G
query_cache_size = 256M
' >/etc/mysql/mariadb.conf.d/99-mysqld_tuned.cnf

Note: There is no guarantee about the order in which the option files in the directory will be read.

So better is to do it like this:

mkdir -p /etc/mysql/overrides.conf.d
echo '!includedir /etc/mysql/overrides.conf.d/' >>/etc/mysql/my.cnf
# then add anything to /etc/mysql/overrides.conf.d/mysqld.cnf

MySQL Query Cache Size and Performance

The perfect PHP clean url generator

function slugify($text, $delimiter = ‘-’) { $url = iconv(‘UTF-8’, ‘ASCII//TRANSLIT’, $text); $url = preg_replace("/[^a-zA-Z0-9/|+ -]/", ‘’, $url); $url = strtolower(trim($url, ‘-’)); $url = preg_replace("/[/|+ -]+/", $delimiter, $url); return $url; }

Or just use Wordpress’s:

sanitize_file_name( $filename )

Developer Documentation - Executing PHP Functions On Data - WP All Import

[sanitize_file_name({post_title[1]})] [slugify({post_title[1]})]

Ne znam kako ovo da proradi: [sanitize_file_name({post_title[1]}) . “_” . sanitize_file_name({sku[1]})]

function slugify_product_image_filename($title, $sku) { return slugify($title) . ‘_’ . slugify($sku); }

Increase

  1. Not really needed: Put in /etc/hhvm/php.ini max_execution_time = 300

  2. I think this solved it: Also in directive for HHVM in nginx: fastcgi_read_timeout 1200; # in second

Updating products with WP All Import is unusably slow.

Alternatives are: WooCommerce - Product Importer Woocommerce CSV importer dgrundel/woo-product-importer · GitHub

Try nulled Product CSV Import Suite - WooThemes

Optimization ideas?

Q: Separating product data in different tables? A: Impossible for now, due the Woocommerce using post_id everywhere.

Speed up WooCommerce by using asynchronous PHP execution

Using cURL?

Another, better, HHVM aproach?

Advanced: Elasticsearch

Elasticsearch + ElasticPress + WordPress Improving WooCommerce Order Search with Elasticsearch | Pressjitsu

Two of the plugins:

Elasticsearch + WordPress: made for each other! - Jonathan Bardo

Advanced: Hooking directly in WP_Query

It is probably achievable as plugins are doing it.

Iceshop documents & downloads Open ICEcat - Wikipedia, the free encyclopedia

How to integrate IceCat product catalog with WordPress, WooCommerce? IceCat product information for woocommerce - WordPress | CodeCanyon The product of Icecat XML processing

E-commerce content for webshops and distributions channels | Get more – www.icecat.biz

Icecat: Full Icecat

WETTKAMPFRING adidas |

[14:43:23] Vladan Čolović: Inače, trebalo bi da imam ovde još dosta lufta za ubrzanje.

  1. SPDY (multiplexiranje učitavanja fotki
  2. CDN (isto to + CDN)
  3. Optimizacija upita. Videćeš. [14:43:30] Vladan Čolović: BTW. Mislim da odmah sve treba staviti na SSL [14:45:33] Vladan Čolović: Kad sve to “popalim”, biće odlično. Ala će se naše “devojkice” nakucati ispravki :)

Iako, možda to i ne treba ispravljati. Samo izbacivati loše uređene proizvode.

To mogu analizom opisa (da brojim čudne znake i slično, ako je sve velika slova, itd) Pa da samo to devojkama iskače za izmenu! Da! To je to! Ček da stavim u Trello. [14:45:36] Vladan Čolović: Kasnije sve to.

date 21. Jun 2017 | modified 10. Jun 2024
filename: Woo » Task » Huge Product DB
Article Content