Custom order tables + new orders UI #10071

Open
mikejolley opened this Issue Jan 14, 2016 · 46 comments

Projects

None yet
@mikejolley
Member
mikejolley commented Jan 14, 2016 edited

These are the steps I think we need to cover (in sequence) in order to migrate orders to a custom DB table (#9735).

Phase 1: Data Abstraction - 2.x.x

Phase 1 will be to remove any reliance for core and 3rd party plugins on WP functions, namely post and post meta functions. Also (@claudiosmweb) we really need to start consolidating code used in the API/CLI and WP Backend to update orders. Our current codebase is not DRY at all.

  • Introduce wc_get_orders() function to replace get_posts().
  • Introduce meta setter and getter functions to avoid any kind of get_post_meta usage, where needed.
  • Expand CRUD operations for order classes.
  • Replace core usage of get_posts, get_post_meta and direct SQL queries where possible (reports cannot be changed however due to the nature of the complex queries currently required).
  • API CRUD usage for order endpoints.
  • CLI CRUD usage for order endpoints.

Phase 2: UI Refresh - 3.x.x

Next we need to redo the UI to again remove the reliance on WP core. Since we'll have a decent CRUD by this point, we can make use of React/Backbone to make something that performs well and gives a better experience for the store owner.

This will have the side affect of breaking any plugin which adds custom meta boxes, so new hooks and ways of adding custom content will be needed to ensure things are still extensible.

@jameskoster will design new screens to show order data. Without the constraints of WP admin + meta boxes we can do pretty much anything we like here.

  • Replace posts list with custom list table.
  • Replace view/edit order screen with custom UI. This will include new panels for downloadable products to get around the performance issues originality reported in #8589

Phase 3: Data Migration - 3.x.x

  • Create new tables; woocommerce_orders and woocommerce_ordermeta
  • Separate payment status from shipped status. Just throwing this on the list but needs more consideration ( @maxrice @justinstern ) to make statuses more flexible,
  • Create migration script (dedicated page with progress bar, or background operation) to migrate posts to orders.
  • Switch CRUD operations to new tables.
  • Rewrite reports to use new tables. Also a good opportunity to do a refresh of the entire reports section once data is easier to access.

I will create issues for the above points in the relevant milestones, however, I wanted to get feedback prior and ensure I have not missed anything. @pmgarman @thenbrent @maxrice @claudiosmweb @mattyza @allendav @justinshreve @jameskoster .

@mikejolley mikejolley added this to the 3.0 milestone Jan 14, 2016
@rahul286

Wow! :-) 👍

@claudiosanches
Member

😃

@lukecav
lukecav commented Jan 14, 2016

😃

@mikejolley
Member

Non-smiley feedback welcome :D

@pmgarman
Contributor

What kind of parameters will wc_get_orders take in an arguments array. My thinking is that what would be taken in while using get_posts would be pretty different from what is taken in on custom tables. Unless the initial version of it while using get_posts was pretty limited and the more advanced parameteters would be added later.

For example: status. If @ 3.0 there becomes a payment and a shipping status, will the wc_get_orders arguments taken a status or go straight to payment_status? Or will status just get deprecated pretty quick in order to move it to payment_status? Obviously it could just stay status but isn't not as obvious when looking at the array of parameters that it is the payment or shipping status.

@mikejolley
Member

We'd need some of the get_posts args, limit, per page etc + status (we can handle bw compat if we change this in the future), customer_id, etc API has 'status' https://woothemes.github.io/woocommerce-rest-api-docs/#view-list-of-orders

@mikejolley
Member

@pmgarman did you look at these functions at all - you said you were keen the other day?

@pmgarman
Contributor

I've not yet, I'm working through some unrelated reporting work today, tomorrow/Monday I'm planning to get into some abstraction. Was out of office Tuesday/Wednesday.

@mikejolley
Member

👍 I'll make a branch once I've taken care of 2.5 release

@jkudish
Contributor
jkudish commented Jan 15, 2016

API CRUD usage for order endpoints.
CLI CRUD usage for order endpoints.

The CLI and API Cruds can probably be one and the same, with the CLI just being a wrapper on top of the API.

I'm keen to help out with API and UI/React parts of this.

@mikejolley
Member
@thenbrent
Member

Scary (but exciting) stuff.

The 3 phases sounds like a sane way to tackle it.

What kind of parameters will wc_get_orders take in an arguments array.

For reference, Subscriptions has a wcs_get_subscriptions() method which accepts the following args:

 * 'customer_id' The user ID of a customer on the site.
 * 'product_id' The post ID of a WC_Product object
 * 'subscription_status' Any valid subscription status. Can be 'any', 'active', 'cancelled', 'suspended', 'expired', 'pending' or 'trash'. Defaults to 'any'.
 * 'order_id' The post ID of a shop_order post/WC_Order object which was used to create the subscription
 * 'orderby' The field which the subscriptions should be ordered by. Can be 'start_date', 'trial_end_date', 'end_date', 'status' or 'order_id'. Defaults to 'start_date'.
 * 'order' The order of the values returned. Can be 'ASC' or 'DESC'. Defaults to 'DESC'
 * 'subscriptions_per_page' The number of subscriptions to return. Set to -1 for unlimited. Default 10.
 * 'offset' An optional number of subscription to displace or pass over. Default 0.

Those have been sufficient for our needs, which are obviously more limited than the use cases for wc_get_orders().

You'll also notice we opted for explicit subscription param keys for any subscription data instead of accepting the same param keys get_posts() uses (e.g. subscription_status instead of post_status). But we support the same param keys for query related args, like order and offset. We also extend some get_posts() params, like orderby to accept additional values that relate to a subscription (like ordering by trial_end_date).

I can't say whether this is the best approach for wc_get_orders(), though it works for us pretty well.

This will have the side affect of breaking any plugin which adds custom meta boxes

That doesn't necessarily have to be the case. It will be possible (albeit pretty hacky) to maintain backward compatibility even if using React components for displaying new meta boxes. Something we can look at in more detail when the time comes.

@webdados

Is this only for orders for now, or are you planning on doing the same for products down the road?

I've got a HUGE website where the products (and the WPML translations of each product) are loaded/updated via a excel file with 40+ fields per product and I use wp_update_post and update_post_meta A LOT!

@claudiosanches
Member

@webdados only orders for now.

@webdados

@claudiosmweb "for now" :-/

@BuggeringHell

This is great for stores with large order volumes, it'd be nice to eventually have the same treatment for products as stores with a large, regularly updated catalog (e.g. CSV imports) would really benefit from it.

@shivapoudel
Contributor

👍

@webdados

@BuggeringHell yes, I see it coming along the way. Huge amounts of products is what makes WC websites very slow. Moving them to separate tables would speed things up. I just hope import/export features/API are created. (I know there are tools like WP All Import, but they don't play well with WC+WPML, for example)

@mikejolley
Member

Products suit posts tables. We also don't need to run complex queries across products like we do orders. So my vote is to leave products where they are.

@BuggeringHell

Possibly not the product/post rows itself, but the transients/cache? MySQL gets swamped with queries called from delete_version_transients() located in woocommerce/includes/class-wc-cache-helper.php. Unless I am mistaken, it is called every time a product is updated? With very large product catalogues, imports become impossibly slow.

@mikejolley
Member

@BuggeringHell read the release post for 2.5. Transients have been optimised.

@BuggeringHell

Oh! I recently updated and tried an import to the same result, maybe I should clear all WC transients? My wp_options table hasn't budged much from ~1,200,000 rows.

@mikejolley
Member

Yeah, flush them all (WP normally does after updates). Sessions are also no longer stored in options table.

@maxrice
Contributor
maxrice commented Jan 19, 2016

👍, the sooner we can begin the data abstraction the better.

Separate payment status from shipped status

Absolutely agree with this. I think the way Shopify approaches order statuses is pretty rational and would make for good inspiration:

screen shot 2016-01-19 at 3 00 57 pm

(see Order API docs)

There's an overall order status, then one for financial status and one for fulfillment status.

@toddlahman
Member

Although wc_get_orders() will be required, it would be a good approach to call an abstract method that allows the WC custom core table to be queried, or third party table to be accessed with a similar structure, but a custom table name, so the same performance gain can be realized by extending WC in third party plugins when a custom table is needed. Anytime the wheel doesn't need to be reinvented in third party plugins, would mean more consistency throughout the ecosystem.

@pmgarman
Contributor

Thanks @maxrice for the notes from Shopify, I think all three of those order statuses would be relevant for WooCommerce orders. Adding a financial_status could mean too in a even further in the future update that partial payments could be supported in WooCommerce even.

@toddlahman
Member

Partial payments would be awesome! It should be possible with gateways such as Stripe.

@amjad
amjad commented Apr 7, 2016

Why don't we still have "Order Type" as a default feature in WooCommerce?

@thisisbbc

Really looking forward to see this change. It's been really a pain to work with a staging platform and try to merge everything when new orders were being registered. I wish I wasn't such a noob with php and js to help you guys. 👶 👍

@franticpsyx
Member
franticpsyx commented Jun 24, 2016 edited

Scary (but exciting) stuff.

+1

@pelmered
Member

This is very exiting stuff! 😀

Products suit posts tables. We also don't need to run complex queries across products like we do orders. So my vote is to leave products where they are.

@mikejolley: Really? Isn't it more the opposite? Products can be filtered and sorted in so many ways that makes sense. The only complex queries for orders I can think of are search queries for the admin views and reporting(but those queries shouldn't need to run often). The reason I see why you would want to put orders in a separate table is to optimise for writes. That is a problem for stores with a lot of orders.

I think that it's only a matter of time before the need to move products into a new table as well comes and I think it would make a lot of sense to do that at the same time as the orders. A lot of the code could be reused and you wouldn't need to do a large breaking change like this again any time soon.

@webdados

@pelmered I understand your point, and I do agree with it, but keep in mind that at the frontend, a product is a post like any other. This makes it possible to take advantage of all the plugins out there that interact with "posts", like SEO stuff (as an example).

@dima-stefantsov

"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.

All 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.

There 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".

@pmgarman
Contributor

As someone who has had over 100M postmeta rows, I disagree with your statement. It's a large database yes, and you just need to have a hosting setup suitable for your site. Products run just fine as post types but with large data sets you do need to be more careful with the plugins and other site setups than just installing whatever plugin you find.

Though at this point this issue is around orders, not products. To keep things on topic if you still disagree perhaps it is best to create. New issue for products and include data and reasoning around why you disagree not just "it's slow" as that's not going to get things anywhere quickly.

@dima-stefantsov

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;

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)

http://4.stefantsov.com/s/?2016.09.13_16.35.14_a0dwycvp.png

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?

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?

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?

My current understanding is that it's impossible with EAV ~29 postmetas per product.
Please open my eyes, tell me I'm wrong. Your 100M postmeta sure sounds impressive.

@webdados

@dima-stefantsov Just open a new thread for "products" as @pmgarman told you to.

@mikejolley
Member

All 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.

If you want to experiment and send a PR I'd love to see that. The CRUD classes in 2.7 should allow abstraction of where data gets stored, but this will of course have serious BW compatibility consequences if not careful.

@lkraav
Contributor
lkraav commented Sep 13, 2016

If you want to experiment and send a PR I'd love to see that. The CRUD classes in 2.7 should allow abstraction of where data gets stored, but this will of course have serious BW compatibility consequences if not careful.

This is very interesting. If I want to experiment with master on a project, is there any introductory document to get me started with this new technology?

@dima-stefantsov

Will I really be the first one making such a thread? There's plenty of them. I've read quite few of them.

General contents:
person A: WC EAV is slow
person B,C,D,E: you should know how to cook it (no real links or hints what should be done)
devs: this is wordpress way, nothing will be done (even though it's unusably slow on many products), closed.

Wanna see? Sure, done.

@mikejolley
Member
mikejolley commented Sep 13, 2016 edited

@dima-stefantsov The issue you've just raised just reads like a rant and invites "me too" posts which helps no-one.

All 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.

^ Post things like this. Offer solutions. Contribute. Thats how to get more traction. As a seasoned developer your input would be welcome. To quote myself:

If you want to experiment and send a PR I'd love to see that. The CRUD classes in 2.7 should allow abstraction of where data gets stored, but this will of course have serious BW compatibility consequences if not careful.

We're focusing on CRUD classes and orders right now which are higher priority.

I'll create a new issue with the important parts if you don't edit it when I get a chance. Will be travelling over the next week.

@danielhuesken
Contributor

Is it posible to get the order table global on multisites? I think it we can do than some crasy thinks with it like one Invice number circit for invoice plugins or summed up charts for the complete multisite.

@webdados

Introduce meta setter and getter functions to avoid any kind of get_post_meta usage, where needed.

Any idea on which version will these new functions be available?

@mikejolley
Member

@webdados 2.7

@webdados

@mikejolley Nice. I'm looking into my (few) plugins that mess around with order meta and want to make them use these new functions as soon as they exist. (Of course that I will test for WC version >=2.7 and then use them or the "old" update_post_meta and get_post_meta functions).

@mikejolley mikejolley removed this from the 3.0 milestone Nov 22, 2016
@mikejolley mikejolley removed the CLI label Dec 16, 2016
@UserName011
UserName011 commented Dec 25, 2016 edited

It is good that you work on normalized WC database but in my point of view there are errors in the design which will put you in the same place again.

Databases don't like lists. The don't know how to work with them, they can't optimize queries using it, they can't index it. they can do nothing with it.
When you consider optimization you only optimize the PHP side.. you don't consider how the database optimizer works (AKA query planner) for most of WC queries it has to do FULL TABLE SCAN for anything and more than once. This is horrible. And this is why you see more and more topics of people that claim huge slowdown working with giant tables. A properly defined DB can handle millions of records in table... here you get questions for 400 records (see the 400 categories topic).

for example: subscription_status as you define it: 'any', 'active', 'cancelled', 'suspended', 'expired', 'pending' or 'trash'.

Why do you do it like this?
Why not creating a table for subscription_status with status_id and status_name and link this table to whatever table needed it with foreign key ? You should normalize the database as much as possible.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment