Very Slow SELECT command ?

[Carline]

Hi,

I'm a newbie to this site and wanted to know if anyone can help me resolve an issue with an SQL SELECT command I have which is very slow to execute.

I've used a number of CASE commands, but I'm sure their must be a better way which will produce the sames results much quicker. At the moment the SELECT command takes around 30 seconds to return the required results.

SELECT DISTINCT
CASE WHEN t1.istemp<>1 THEN t2.product_name ELSE t1.tmpname END AS product_name,
CASE WHEN t1.istemp<>1 THEN t2.product_sku ELSE t1.tmpsku END AS product_sku,
CASE WHEN t1.istemp=0  THEN t3.product_price ELSE t1.tmpprice END AS product_price,
t1.product_quantity,
t1.product_id,
t1.attribute,
CASE WHEN t2.product_tax_id=0 THEN 0 WHEN t1.istemp<>1 THEN t4.tax_rate ELSE t1.tmpVAT*0.01 END AS tax_rate ,
t1.product_attribute,
CASE WHEN t1.istemp<>1 THEN t2.product_s_desc ELSE t1.tmpSDesp END AS product_s_desc
FROM jos_vm_quote_product t1,

jos_vm_product t2,
jos_vm_product_price t3,
jos_vm_tax_rate AS t4,
jos_vm_shopper_group t5

WHERE t1.quote_id="2038"
AND
CASE WHEN t1.istemp<>1 THEN t1.product_id=t2.product_id AND t1.product_id=t3.product_id
AND
(t2.product_tax_id=t4.tax_rate_id or t2.product_tax_id=0)
AND
((t3.price_quantity_start <= t1.product_quantity AND t3.price_quantity_END >= t1.product_quantity) OR (t3.price_quantity_start=0 AND t3.price_quantity_END=0))
AND
t5.default=1 AND t5.shopper_group_id=t3.shopper_group_id ELSE 1 END
GROUP BY t1.id

Any help or guidance greatly appreciated as I'm very new to PHP and SQL so try and keep any answers simple. :o)

Many thanks,

[johanafm]

I'd start by splitting the query up in two separate queries. One query for t1.istmp = 1 and one for t1.istmp <> 1. You can most likely reintegrate them later, but you might as well reduce complexity while trying to find the bottleneck(s). But, there is also a chance that the query will execute slower due to your istmp CASEs, since the query has to start querying table t1, even if it would be more efficient querying another table or tables first (which seems impossible to do if the joined tables depend on this field).

After that, I'd also rewrite your "comma + where" joins into regular join syntax. There is no real difference. This is definitely a matter of personal perference, but at least I find it easier to read the query and see what's used in joins etc.

SELECT stuff
FROM t1
INNER JOIN t2 ON t1.field_a1 = t2.field_a2 AND t1.field_b1 = t2.field_b2
INNER JOIN t3 ON t1.field_a1 = t3.field_a3
...
WHERE non_join_field = @some_value

After this, make sure that you have indeices for anything used in either a join clause or the where clause. If you are using compound fields for joins/where, then you should also check if you have a usable index for this.
For example, if you have an index over (field1, field2, field3) this can be used if one or more fields are used for joins/where, assuming the first N fields are included. That is, if you have a join on either just field1 or both field1 and field2, the index is used, but if you have just field2 or field2 and field3, then the index can't be used and you'd need to add a new index for (field2, field3).

Once these things are sorted, have a look at the output of

EXPLAIN [your query]

(without the brackets, [ and ]