I inherited a site which has a product search running Coldfusion8 and MysSQL(5.0.88). Both filling the search form with default values and the search take forever, so I'm trying to improve both.
Currently for filling the search form with default values the same query is ran multiple times (omce per criteria).
Criterias queries are
min/max prices (3 sets),
colors. For each criteria the whole product table is queried.
I'm now trying combine the queries to not run over the table multiple times, but I'm having a few problems. My current code:
SELECT DISTINCT ( SELECT art.corp) AS corp ,( SELECT art.brand) AS brand ,( SELECT MIN(art.price_1) ) AS p1_from ,( SELECT MAX(art.price_1) ) AS p1_to ,( SELECT MIN(art.preis_2) ) AS p2_from ,( SELECT MAX(art.preis_2) ) AS p2_from ,( SELECT ROUND(MIN( 100*( ( art.price_1 - art.price_2 ) / price_2 ) ))) AS reb_min ,( SELECT ROUND(MAX( 100*( ( art.price_1 - art.price_2 ) / price_2 ) ))) AS reb_max FROM articles AS art USE INDEX (i_iln) ... a ton of criteria
While this gets the correct value, I'm only getting a sinlge row, as soon as I add the first min/max query.
So my question:
Is it possible to do the above query and return different number of row values? Or do I have to split this up into 2 queries, one for brand/corp, one for min/max values.
Also, if some values require a
LEFT JOIN, which is only applicable to some subqueries, can I include this on the subquery? How about specific indices?