includes/modules/sideboxes/manufacturers.php

ZC V2.10

original code;

if ((int)PRODUCTS_MANUFACTURERS_STATUS === 1) {
    $manufacturer_sidebox_query =
        "SELECT DISTINCT m.manufacturers_id, m.manufacturers_name
                    FROM " . TABLE_MANUFACTURERS . " m
                            LEFT JOIN " . TABLE_PRODUCTS . " p ON m.manufacturers_id = p.manufacturers_id
                   WHERE p.products_status = 1
                   ORDER BY manufacturers_name";
} else {

causes:

# Query_time: 0.079040  Lock_time: 0.000055  Rows_sent: 131  Rows_examined: 170248
# Rows_affected: 0  Bytes_sent: 2384
SET timestamp=1752453859;
SELECT DISTINCT m.manufacturers_id, m.manufacturers_name
                    FROM manufacturers m
                            LEFT JOIN products p ON m.manufacturers_id = p.manufacturers_id
                   WHERE p.products_status = 1
                   ORDER BY manufacturers_name;

suggested improvement;

if ((int)PRODUCTS_MANUFACTURERS_STATUS === 1) {
    $manufacturer_sidebox_query =
        "SELECT DISTINCT m.manufacturers_id, m.manufacturers_name
                    FROM " . TABLE_PRODUCTS . " p
                    JOIN " . TABLE_MANUFACTURERS . " m ON p.manufacturers_id = m.manufacturers_id
                   WHERE p.products_status = 1
                   ORDER BY m.manufacturers_name";
} else {

results in;

# Query_time: 0.001224  Lock_time: 0.000053  Rows_sent: 131  Rows_examined: 413
# Rows_affected: 0  Bytes_sent: 2384
SET timestamp=1752456011;
SELECT DISTINCT m.manufacturers_id, m.manufacturers_name
                    FROM products p
                    JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
                   WHERE p.products_status = 1
                   ORDER BY m.manufacturers_name;

as this query is used tenthousand times a day (at least on my template) making this one more efficient helps the climate

Nice! Could you check to see if this provides additional improvement, since the products.products_status is also an index?

SELECT DISTINCT m.manufacturers_id, m.manufacturers_name
  FROM manufacturers m
      INNER JOIN products p
          ON p.manufacturers_id = m.manufacturers_id
         AND p.products_status = 1
 ORDER BY m.manufacturers_name;

Sounds like a good one to submit to GitHUb.

https://github.com/zencart/zencart/issues