{"action":"created","issue":{"url":"https://api.github.com/repos/woothemes/woocommerce/issues/11913","repository_url":"https://api.github.com/repos/woothemes/woocommerce","labels_url":"https://api.github.com/repos/woothemes/woocommerce/issues/11913/labels{/name}","comments_url":"https://api.github.com/repos/woothemes/woocommerce/issues/11913/comments","events_url":"https://api.github.com/repos/woothemes/woocommerce/issues/11913/events","html_url":"https://github.com/woothemes/woocommerce/issues/11913","id":176663949,"number":11913,"title":"Slow on 100k+ Products","user":{"login":"dima-stefantsov","id":1370909,"avatar_url":"https://avatars.githubusercontent.com/u/1370909?v=3","gravatar_id":"","url":"https://api.github.com/users/dima-stefantsov","html_url":"https://github.com/dima-stefantsov","followers_url":"https://api.github.com/users/dima-stefantsov/followers","following_url":"https://api.github.com/users/dima-stefantsov/following{/other_user}","gists_url":"https://api.github.com/users/dima-stefantsov/gists{/gist_id}","starred_url":"https://api.github.com/users/dima-stefantsov/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dima-stefantsov/subscriptions","organizations_url":"https://api.github.com/users/dima-stefantsov/orgs","repos_url":"https://api.github.com/users/dima-stefantsov/repos","events_url":"https://api.github.com/users/dima-stefantsov/events{/privacy}","received_events_url":"https://api.github.com/users/dima-stefantsov/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2016-09-13T14:56:41Z","updated_at":"2016-09-13T20:41:09Z","closed_at":null,"body":"## WP/WC is slow for both read and write when products count is more than 100k.\r\n\r\nLatest WP, WC. For each Product in wp_posts, there are ~29 rows in wp_postmeta. When postmeta have more than 1M rows, certain queries become slow. Really slow. Unusably slow.\r\n\r\nI've heard \"it's not a bug, it's a feature\", \"it's as intended\", \"it's wordpress way\". But my understanding of \"wordpress way\" is when everything is easy and fast. Not when page takes seconds to load.\r\n\r\n**I expect WC to work fast, even with millions of products. And it would be fast, if meta would be stored in one row per product, in a custom table. This is how I see it can be fixed. Is there other ways? Will it be fixed?**\r\n\r\nI was advised to make new issue, original comment with explanation is here https://github.com/woothemes/woocommerce/issues/10071#issuecomment-246581179 \r\n> \"Products\" should stay in wp_posts, what makes WooCommerce slow on 50k+ products is huge wp_postmeta. WC creates ~29 meta rows per every post/product. Everything gets slow when wp_postmeta have few millions rows.\r\n>\r\nAll that needs to be done is flatten this postmeta EAV hell somehow. Move it to new table wp_woocommerce_product_meta, where it takes 1 row per product, not ~29 in common table.\r\n>\r\nThere is kind of solution that does exactly this https://wordpress.org/plugins/horizontal-meta/ , but it's abandoned and \"doesn't work well past wp 4.1\".\r\n\r\nand here https://github.com/woothemes/woocommerce/issues/10071#issuecomment-246684666\r\n\r\n> SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 AND ( ( wp_postmeta.meta_key = '_visibility' AND wp_postmeta.meta_value IN ('visible','catalog') ) ) AND wp_posts.post_type = 'product' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.menu_order ASC, wp_posts.post_title ASC LIMIT 48, 12;\r\n> \r\n> Above is the code WP/WC generates for page 5 root of shop products. It joins with postmeta and checks if each product should be counted/displayed or hidden. It takes about 0.8 seconds on ~93k products, on 4.2GHz top i7 PC. Not much faster CPU can get. (ssd, innodb, db warm in cache)\r\n> \r\n> http://4.stefantsov.com/s/?2016.09.13_16.35.14_a0dwycvp.png\r\n> \r\n> You say I am doing it wrong, and there is a way to make browsing faster? Currently page loads more than a second, on too fast pc, taking 100% CPU of one core for the time. You say it can become 10-100 times faster?\r\n> \r\n> I'm not DBA, but I'm seasoned developer, in many languages. General phrases like \"you do need to be more careful with the plugins and other site setups than just installing whatever plugin you find\" are not about me. Naked WP. WC. 100k products. Opening full products list. You say it can be fast? How? What about 3 000 000 products?\r\n> \r\n> Even more, what about adding products? Adding 50-100k products is not DB-capped on my 4GHz cpu, it's about 300 products/minute with default setup. After that, things become exponentially slower. If WP would use horizontal table for product meta, it would work with millions products no problem. Can I make it work this way now? How does \"hosting setup suitable for my site\" looks in my case?\r\n> \r\n> My current understanding is that it's impossible with EAV ~29 postmetas per product.\r\n> Please open my eyes, tell me I'm wrong. Your 100M postmeta sure sounds impressive.\r\n\r\n## STEPS TO REPRODUCE THE ISSUE\r\n\r\n1. Import 500k products with wp all import from any csv\r\n2. (few days later)\r\n3. Now you have 500k posts, try browsing your products catalog root. Page1, page2, page3.\r\n4. It takes forever.\r\n\r\n## SYSTEM STATUS REPORT\r\n\r\n```\r\n### WordPress Environment ###\r\n\r\nHome URL: http://wooshop.local\r\nSite URL: http://wooshop.local\r\nWC Version: 2.6.4\r\nLog Directory Writable: ✔\r\nWP Version: 4.6.1\r\nWP Multisite: –\r\nWP Memory Limit: 256 MB\r\nWP Debug Mode: –\r\nWP Cron: ✔\r\nLanguage: en_US\r\n\r\n### Server Environment ###\r\n\r\nServer Info: Apache/2.4.20 (Win64) OpenSSL/1.0.2h PHP/7.0.8\r\nPHP Version: 7.0.8\r\nPHP Post Max Size: 8 MB\r\nPHP Time Limit: 120\r\nPHP Max Input Vars: 2500\r\ncURL Version: 7.49.1\r\nOpenSSL/1.0.2h\r\n\r\nSUHOSIN Installed: –\r\nMySQL Version: 5.7.9\r\nMax Upload Size: 8 MB\r\nDefault Timezone is UTC: ✔\r\nfsockopen/cURL: ✔\r\nSoapClient: ✔\r\nDOMDocument: ✔\r\nGZip: ✔\r\nMultibyte String: ✔\r\nRemote Post: ✔\r\nRemote Get: ✔\r\n\r\n### Database ###\r\n\r\nWC Database Version: 2.6.4\r\n: \r\nwoocommerce_sessions: ✔\r\nwoocommerce_api_keys: ✔\r\nwoocommerce_attribute_taxonomies: ✔\r\nwoocommerce_downloadable_product_permissions: ✔\r\nwoocommerce_order_items: ✔\r\nwoocommerce_order_itemmeta: ✔\r\nwoocommerce_tax_rates: ✔\r\nwoocommerce_tax_rate_locations: ✔\r\nwoocommerce_shipping_zones: ✔\r\nwoocommerce_shipping_zone_locations: ✔\r\nwoocommerce_shipping_zone_methods: ✔\r\nwoocommerce_payment_tokens: ✔\r\nwoocommerce_payment_tokenmeta: ✔\r\nMaxMind GeoIP Database: ✔\r\n\r\n### Active Plugins (2) ###\r\n\r\nWooCommerce: by WooThemes – 2.6.4\r\nWP All Import: by Soflyy – 3.3.7\r\n\r\n### Settings ###\r\n\r\nForce SSL: –\r\nCurrency: RUB (руб.)\r\nCurrency Position: right_space\r\nThousand Separator: \r\nDecimal Separator: ,\r\nNumber of Decimals: 0\r\n\r\n### API ###\r\n\r\nAPI Enabled: ✔\r\n\r\n### WC Pages ###\r\n\r\nShop Base: ❌ Page ID is set\r\nbut the page does not exist\r\n\r\nCart: ❌ Page ID is set\r\nbut the page does not exist\r\n\r\nCheckout: ❌ Page ID is set\r\nbut the page does not exist\r\n\r\nMy Account: ❌ Page ID is set\r\nbut the page does not exist\r\n\r\n\r\n### Taxonomies ###\r\n\r\nProduct Types: external (external)\r\ngrouped (grouped)\r\nsimple (simple)\r\nvariable (variable)\r\n\r\n\r\n### Theme ###\r\n\r\nName: Storefront Child\r\nVersion: 2\r\nAuthor URL: \r\nChild Theme: ✔\r\nParent Theme Name: Storefront\r\nParent Theme Version: 2.1.2\r\nParent Theme Author URL: https://woocommerce.com/\r\nWooCommerce Support: ✔\r\n\r\n### Templates ###\r\n\r\nOverrides: storefront-child/woocommerce/loop\\add-to-cart.php\r\nstorefront-child/woocommerce/single-product\\add-to-cart\\simple.php\r\nstorefront-child/woocommerce/single-product\\price.php\r\n```\r\n"},"comment":{"url":"https://api.github.com/repos/woothemes/woocommerce/issues/comments/246816757","html_url":"https://github.com/woothemes/woocommerce/issues/11913#issuecomment-246816757","issue_url":"https://api.github.com/repos/woothemes/woocommerce/issues/11913","id":246816757,"user":{"login":"dima-stefantsov","id":1370909,"avatar_url":"https://avatars.githubusercontent.com/u/1370909?v=3","gravatar_id":"","url":"https://api.github.com/users/dima-stefantsov","html_url":"https://github.com/dima-stefantsov","followers_url":"https://api.github.com/users/dima-stefantsov/followers","following_url":"https://api.github.com/users/dima-stefantsov/following{/other_user}","gists_url":"https://api.github.com/users/dima-stefantsov/gists{/gist_id}","starred_url":"https://api.github.com/users/dima-stefantsov/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dima-stefantsov/subscriptions","organizations_url":"https://api.github.com/users/dima-stefantsov/orgs","repos_url":"https://api.github.com/users/dima-stefantsov/repos","events_url":"https://api.github.com/users/dima-stefantsov/events{/privacy}","received_events_url":"https://api.github.com/users/dima-stefantsov/received_events","type":"User","site_admin":false},"created_at":"2016-09-13T20:41:09Z","updated_at":"2016-09-13T20:41:09Z","body":"http://nathanfranklin.com.au/plugins/introducing-horizontal-meta-performance-benchmarks/ and other posts there = elaboration on how horizontal meta (plugin mentioned in comment above) was able to drastically speed WC up."}}
{"actor":{"display_login":"dima-stefantsov"}}