Hello.
[...]
> Here is the query
>
[query reformatted... btw, it would have been nice if you had done this at first]
SELECT brand.brandname,
SUM((productorders.quantity)*(productorders.price)) AS turnover
FROM orders
LEFT JOIN productorders ON productorders.orderid = orders.id
LEFT JOIN perfect_articles ON productorders.ordernr = articles.ordernr
LEFT JOIN products ON products.id = articles.id
LEFT JOIN brand ON products.brand = brand.id
WHERE productorders.date >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY),
'%Y-%m-%d')
AND productorders.date <= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 0 DAY),
'%Y-%m-%d')
AND orders.status != 2
GROUP BY brand.brandname
ORDER BY turnover asc
Some comments:
- Where is the table "articles" coming from? Did you mean
"perfect_articles"? If not, you have no restriction on this table.
- Where is the table "orders"? I assume you meant "productorders"?
- Do you really need LEFT JOINs instead of normal JOINs? At least the
effect of the LEFT JOIN with "productorders" is lost by using
"orders.status != 2" in the WHERE clause
- You know that LEFT JOINs are usually slower than normal joins and
therefore should only be used when needed? (Additonally, the result
will differ in many cases)
[...]
> I need to do the above query about 4 times for different intervals and in this case
>it then takes about 15 minutes to complete...
> (Pentium III 800 machine with 128 MB)
[...]
We need to see the output of
EXPLAIN SELECT ...
SHOW INDEX FROM brand
SHOW INDEX FROM productorders
SHOW INDEX FROM perfect_articles
SHOW INDEX FROM products
SHOW INDEX FROM orders
> It looks that as soon as if I start to link a table with more than
> 1000 records the machine is having a hard time.. Is it better to use
> a where clause to link the tables..??
It doesn't matter if you use a ON clause instead of a WHERE clause
with normal JOINS. The type of the JOIN does matter! And you cannot
write LEFT JOINs with the constraint in the WHERE clause.
Bye,
Benjamin.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
