$_amount_clause = ($GLOBALS['config']['General']['unlimited_products'] == 'N') ? 'AND p.avail > 0' : '';
$__date_limit = strtotime('-' . $GLOBALS['smarty']->_tpl_vars['date_limit'] . ' days');
$__orderby = (strtoupper($GLOBALS['smarty']->_tpl_vars['randomize']) == 'Y') ? 'RAND()' : 'add_date ' . $GLOBALS['smarty']->_tpl_vars['ordering'];
$__sql = "
SELECT DISTINCT
p.productid
FROM
$_sql_tbl[products] AS p
LEFT JOIN $_sql_tbl[products_categories] AS pc ON pc.productid = p.productid
LEFT JOIN $_sql_tbl[categories] AS c ON pc.categoryid = c.categoryid
LEFT JOIN $_sql_tbl[images_T] AS i ON i.id = p.productid
LEFT JOIN $_sql_tbl[extra_field_values] AS e ON e.productid = p.productid
WHERE
i.id IS NOT NULL AND
e.fieldid = '3' AND
e.value = 'No' AND
p.forsale = 'Y' AND
c.avail = 'Y'
$_amount_clause
$_cat_clause AND
p.add_date >= '$__date_limit'
GROUP BY i.id
ORDER BY
$__orderby
";
This query runs pretty slow when I have 100,000 products on my site. Thanks guys in advance.
