How to make the operation of price update faster?

[rp.s]

ZC version：1.5.7
PHP version:7.4

When I manage the price of the product and click the update button, it takes more than 10 seconds to complete. I checked the file products_price_manager.php, and after updating the price, there will be the following SQL statement：

The result sets of these SQL are very large. Is this the cause of the problem?

$productsFeatured = $db->Execute("SELECT p.products_id,
f.featured_id, f.expires_date, f.featured_date_available, f.status
FROM " . TABLE_PRODUCTS . " p,
" . TABLE_FEATURED . " f
WHERE p.products_id = f.products_id
AND f.products_id = " . (int)$_GET['products_filter']);

$productsSpecial = $db->Execute("SELECT p.products_id,
s.specials_id, s.specials_new_products_price, s.expires_date, s.specials_date_available, s.status
FROM " . TABLE_PRODUCTS . " p,
" . TABLE_SPECIALS . " s
WHERE p.products_id = s.products_id
AND s.products_id = " . (int)$_GET['products_filter']);

$products = $db->Execute("SELECT p.products_id, p.products_model,
p.products_price, p.products_date_available,
p.products_tax_class_id,
p.products_quantity_order_min, products_quantity_order_units, p.products_quantity_order_max,
p.product_is_free, p.product_is_call, p.products_quantity_mixed, p.products_priced_by_attribute, p.products_status,
p.products_discount_type, p.products_discount_type_from, p.products_price_sorter,
pd.products_name,
p.master_categories_id, p.products_mixed_discount_quantity
FROM " . TABLE_PRODUCTS . " p,
" . TABLE_PRODUCTS_DESCRIPTION . " pd
WHERE p.products_id = " . (int)$_GET['products_filter'] . "
AND p.products_id = pd.products_id
AND pd.language_id = " . (int)$_SESSION['languages_id']);

[mc12345678]

Each of those queries appear to be seeking a single product, yet do not have a limit of 1 either in the query itself or in the call to execute.

As a result, all product are searched for each query instead of stopping when the one match is found.

How many product are in the database?
Also how many featured items are there?

[rp.s]

Each of those queries appear to be seeking a single product, yet do not have a limit of 1 either in the query itself or in the call to execute.

As a result, all product are searched for each query instead of stopping when the one match is found.

How many product are in the database?
Also how many featured items are there?

Table of products: 480,000 lines
featured：80,000

[pilou2]

If you look at zc v1.5.8 (same file) these queries all use inner join or left join which is are more efficient. Queries from 1.5.7 you posted here are going through all database before applying Where clause.
It looks to me that new queries from v1.5.8 could be easily uses in v1.5.7 too.
You could although use STRAIGHT_JOIN and place smaller table first and most restrict condition first. With STRAIGHT_JOIN order is important.
For example:

$productsFeatured = $db->Execute("SELECT p.products_id,
f.featured_id, f.expires_date, f.featured_date_available, f.status
FROM " . TABLE_FEATURED . " f STRAIGHT_JOIN " . TABLE_PRODUCTS . " p 
ON p.products_id = f.products_id
WHERE f.products_id = " . (int)$_GET['products_filter']); 

This query will go through left table first, feature table which is far smaller than you product table. This is done like this for 'big data' as I know but with your table size standard INNER JOIN like used in zc v1.5.8 should be good too.

[rp.s]

If you look at zc v1.5.8 (same file) these queries all use inner join or left join which is are more efficient. Queries from 1.5.7 you posted here are going through all database before applying Where clause.
It looks to me that new queries from v1.5.8 could be easily uses in v1.5.7 too.
You could although use STRAIGHT_JOIN and place smaller table first and most restrict condition first. With STRAIGHT_JOIN order is important.
For example:

$productsFeatured = $db->Execute("SELECT p.products_id,
f.featured_id, f.expires_date, f.featured_date_available, f.status
FROM " . TABLE_FEATURED . " f STRAIGHT_JOIN " . TABLE_PRODUCTS . " p 
ON p.products_id = f.products_id
WHERE f.products_id = " . (int)$_GET['products_filter']); 

This query will go through left table first, feature table which is far smaller than you product table. This is done like this for 'big data' as I know but with your table size standard INNER JOIN like used in zc v1.5.8 should be good too.

Thank you for your opinion. The query of zc1.5.7 is also very fast, but the front end is very slow.

[RixStix]

DBIO is a great plugin that will speed things up once you get things setup for you particular needs. Do everything in a properly formatted spreadsheet & upload.

https://www.zen-cart.com/downloads.php?do=file&id=2091

[rp.s]

DBIO is a great plugin that will speed things up once you get things setup for you particular needs. Do everything in a properly formatted spreadsheet & upload.

https://www.zen-cart.com/downloads.php?do=file&id=2091

Thank you for your help. I will try, but this is not my first choice.