Jump to content

 

poppedweb

Member Since 02 Aug 2016
Offline Last Active Yesterday, 07:11 PM
-----

Posts I've Made

In Topic: Webp Images - Talk To Me!

14 January 2019 - 02:45 PM

How can I test if its working with CS Cart properly?  Thanks

 

Also, do mind that many browsers do not support this filetype. Therefore you should store a seperate jpg and webp image and only server webp when the browser supports it.

 

If you need any help, please contact us.


In Topic: My Cs-Cart Is To Slow

13 January 2019 - 05:44 PM

Yep, i'm thinking the index type and sorting will be a key.  Clustering should help as well, hard part is getting that all figured out and the best way of getting this done without needing application changes.

 

We do offer Amazon services. Seeing that you want the simplest solution, I suggest using a Aurora Serverless application. Once that is configured accordingly it will be automatically scaled and optimized accordingly to your actual needs. The best yet, it requires no application changes.


In Topic: My Cs-Cart Is To Slow

12 January 2019 - 09:44 PM

 

I didn't want to do that since it was a key element of the query, but did it to see what would happen.  Query time went from about 8 seconds (for the stripped down query above) to just under 3 seconds.   So that does help -- BUT, i also have to take out the ORDER BY PRODUCT.   Below is the EXPLAIN plan for the query without the Product Description table.

 

Well, seeing that it is still not using an index, I suggest you contact us here, so we can take a look at what we can do for you: sales@poppedweb.com

 

With 15M products, you will need a quantum computer...

 

No, he should actually add indices / indexes to sort his table and use a cluster to distribute them across the stack accordingly


In Topic: My Cs-Cart Is To Slow

12 January 2019 - 07:13 PM

 

Unfortunately we are not wanting to edit the actual code for the product.   We had to use an Addon for the Search portion of the site and they set hooks to update the query and do some other optimizations.

 

I have checked and the database is not actually hitting the disk from what i can tell -- except to write out during the GROUP BY and ORDER operation (I have even removed that from the direct DB query to remove the write).

 

I have been tinkering with the INDEXES and creating new, testing, removing, etc, just nothing really dramatic occurs.   I have even used the FORCE INDEX.   Below is my stripped down query and I think the bottom line is there is a lot of data, but this still should be much quicker... 

 

Time to try some different Types of indexes and the sorting order with them....

SELECT  SQL_CALC_FOUND_ROWS products.product_id, descr1.product as product, companies.company as company_name 
FROM cscart_products as products  LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id 
AND descr1.lang_code = 'en'  
LEFT JOIN cscart_companies AS companies ON companies.company_id = products.company_id  
WHERE 1  
 AND products.product_id NOT IN (183291, ...<about 100 productids> ) 
AND companies.status = 'A'  
AND (products.usergroup_ids = '' 
	OR FIND_IN_SET(0, products.usergroup_ids) 
	OR FIND_IN_SET(1, products.usergroup_ids)) 
AND products.status IN ('A') 
AND products.approved = 'Y' 
AND products.product_type IN ('P', 'C') 
GROUP BY products.product_id   
ORDER BY product asc, products.product_id ASC  
LIMIT 0, 12;

 

Could you try running the query without the product descriptions table?


In Topic: My Cs-Cart Is To Slow

12 January 2019 - 06:43 PM

I wish it was as simple as that.   I am running the query above and one that i stripped down to just the 3 needed tables (in the SELECT statement) and still see some pretty bad performance.   The EXPLAIN plan indicates it has to iterate over all 1.2M records.... 

 

They key is in not selecting any tables containing TEXT columns in the first place since they will require to be read from disk. For that reason, it is recommended that you first query all the products and after that all the appropriate product descriptions.

 

Anyhow, you could also try optimizing by means of indices, this would involve modifying the query to use the FORCE INDEX query parameter in some cases.