[leomoon]

5 years, 4 months ago #12453

Tab 2.3. takes about 22 minutes to load. My site may be pushing the limits of Woocommerce as a platform, but this long of a loading time started somewhere about version 21.4 or so.

Since wordfence won’t let me copy/paste sql statements here, here is a link to a text file containing three super-slow queries:

https://1drv.ms/t/s!Ah6mDhPS2VbWiKpKTi5lO5EiepH2lQ

Also, the indexing itself is super-slow. I used to be able to index 65000 products in a matter of minutes, but now it takes like half an hour for each 300 chunk.

[wpsolr]

5 years, 4 months ago #12454

Can you deselect all taxonomies in screen 2.1, to keep only your post types, and see if things are faster?

Or you can deselect taxonomies in the indexing screen to check the indexing speed.

Which screen 2.3 ? (“Search” or “suggestions”)

[leomoon]

5 years, 4 months ago #12455

Sorry, not 2.3, but just tab 3: Send your data.

I’ll try 2.1. deselections now (waiting for that page to load too).

[leomoon]

5 years, 4 months ago #12456

Indeed, upon deselecting taxonomies and leaving only the Product type, both tab 3 and indexing is super fast as before. I’ll try to add taxonomies one at the time.

[leomoon]

5 years, 4 months ago #12457

Adding just a single taxonomy, such as a book author increases 3. tab loading time to almost 10 minutes and slows down indexing to the point of unusability.

[wpsolr]

5 years, 4 months ago #12458

I guess “book author” is a product attribute. How many terms in it?

[leomoon]

5 years, 4 months ago #12460

Yes, it is an attribute. It has some 25000+ terms. There are about 65000 products themselves.

[wpsolr]

5 years, 4 months ago #12461

I will make stress tests.

The difference between taxonomy and post types indexing is that taxonomy terms does not have a publication date. Hence, no way to perform incremental indexing. So, the query that retrieves taxonomy terms to index is a join with a custom table that stores the last indexed date for the taxonomy terms. This join could be the reason of the slowness.

[leomoon]

5 years, 4 months ago #12462

I understand. If you have a query I could execute to test if this is a problem, please do send it to me and I’ll run it against my DB.

[wpsolr]

5 years, 4 months ago #12463

The queries are the ones you sent me already.

[wpsolr]

5 years, 4 months ago #12499

I made my tests with 1000 terms, and everything was good.

But with 10,000 terms, the SQL to retrieve terms is really slow. It appears a “sql inner join” between 10 thousands and 10 thousands terms is not viable.

I made some preliminary tests to replace it with a slightly modified ” sql union”, and it’s very fast for the same 10 thousand terms.

After verification of the speed, I’m now checking if all the other parts of the indexing code can be adapted too. Not trivial, because the code was custom built around the “sql inner join”.

[wpsolr]

5 years, 4 months ago #12500

Here is a new version with the fix: https://www.dropbox.com/s/r9zyw53xx2kat03/wpsolr-pro-taxonomies-indexing-fast.zip?dl=0

Tested with 10,000 terms indexed in Elasticsearch. At least as fast as with post types indexing.

[leomoon]

5 years, 4 months ago #12503

Thanks for working on this so quickly. It looks like this table:

WordPress database error: [Table ‘woo.wpsolr_acceptance_wpsolr_index_history’ doesn’t exist]

Is not being created at any point. I tried removing/re-adding plugin, but it still gives that error on send data.

[wpsolr]

5 years, 3 months ago #12516

Sorry for that. Here is the fix: https://www.dropbox.com/s/hjs2j9am5pi7lom/wpsolr-pro-taxonomies-indexing-fast-1.zip?dl=0

[leomoon]

5 years, 3 months ago #12532

Send data loads in seconds now and tables are there so no errors about that.

• This reply was modified 5 years, 3 months ago by wpsolr. Reason: Asked author to move text to another topic