Hi Benjamin,
Thanks for reading my message an taking some time to give some help/sugggestion..
First you are right that I should have written the query in a readable form..
You gave some comments:
> 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.
Yes, sorry, missed that one.. All the tables I originally made the query for have the
prefix perfect_ in front..
(perfect_articles, perfect_brand, perfect_productsorders.. I removed this to the make
it shorter and not make the query
look more complicated than it is.. (To answer your question: I missed the one perfect_
you saw, this had to be removed....)
> - Where is the table "orders"? I assume you meant "productorders"?
No, I do a select .... from orders...
Further in the query productorders is linked to orders..
Productorders is also then linked to the database which contains all articles ->
articles is linked to the products and products
linked to brand...
(Eg: the example: Microsoft (brand) -> Operating systems (products) -> Windows 95
(articles)
The person orders a windows 95 package.. The subtotals, way of payment etc if stored
in orders and all the articles he has ordered
is stored in productorders..
The reason why I needed to link productorders to orders is to have the option of
ignoring all records in which the order.status =
2.. (This is an number which tells me
that the order has been cancelled)
(I hope you understand, this is very hard to explain I would be better of drawing a
small chart..)
> - 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)
Sorry, tell me I am dumb... I think that I understand SQL a bit, but I am by no ways
an expert..
I do try to get my hands on books from which I can learn something, but joins and
indexes are still an area from which I can learn a
lot of stuff..
In the past I always used a 'where' clause when joining stuff (eg: select
apples.description, pears.description from apples, pears
where pears.id = apples.id order by apples.id etc etc
Then I got my hands on examples which used a left join examples.. I have never tried a
normal join (sigh..), but thinking of it,
what is the difference between an left join and a normal join..??
(I will look at the chapter join in the mysql manual just now, so if I ask something
dumb which is explained in the manual then skip
the answer..)
I use PHP to format my output, so maybe a left join has something to do with the order
the columns are displayed which is no use for
me..??
> 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.
I understand what you say, only the last sentance I think I should go read the manual
to understand it.. (you cannot write left
joins with the contraints?? in the where clause..)
I did an explain on the query like you suggested/asked: (Please bear in mind that you
should think the 'perfect_' and
perfect_customer_ away for the above example...
These command (explain select...) are new to mee, but it looks like some useful
information and that I can optimize a lot by adding
some extra indexes...????
Original Query:
SELECT perfect_brand.brandname,
Sum((perfect_customer_productorders.quantity)*(perfect_customer_productorders.price))
AS omzet
FROM perfect_customer_orders
LEFT JOIN perfect_customer_productorders on
perfect_customer_productorders.orderid = perfect_customer_orders.id
LEFT JOIN perfect_articles on perfect_customer_productorders.ordernr =
perfect_articles.ordernr
LEFT JOIN perfect_products on perfect_products.id = perfect_articles.id
LEFT JOIN perfect_brand on perfect_products.brand = perfect_brand.id
WHERE (perfect_customer_productorders.date >= DATE_FORMAT( DATE_SUB( NOW() , INTERVAL
30 DAY ) , '%Y-%m-%d' )
AND perfect_customer_productorders.date <= DATE_FORMAT( DATE_SUB( NOW() ,
INTERVAL 0 DAY ) , '%Y-%m-%d' )
AND perfect_customer_orders.status != 2)
GROUP BY perfect_brand.brandname
ORDER BY omzet asc
explain above query: (Hope my tabs to get some layout stays intact)
I added an small gif to help when the following fails..
table type possible_keys key
key_len ref
rows Extra
perfect_customer_orders ALL
2585 where used
perfect_customer_productorders ALL
4410 where used
perfect_articles eq_ref PRIMARY PRIMARY
50
perfect_customer_productorders.ordernr 1
perfect_products ALL PRIMARY
180
perfect_brand ALL PRIMARY,id
16
show index from perfect_customer_orders
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part
perfect_customer_orders 0 PRIMARY 1 id A 2585
perfect_customer_orders 1 id 1 id A
show index from perfect_customer_productorders
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part
perfect_customer_productorders 0 PRIMARY 1 id A 4410
perfect_customer_productorders 1 id 1 id A
show index from perfect_customer_productorders
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part
perfect_customer_productorders 0 PRIMARY 1 id A 4410
perfect_customer_productorders 1 id 1 id A
show index from perfect_articles
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part
perfect_articles 0 PRIMARY 1 ordernr A 314
perfect_articles 1 nr 1 nr A
show index from perfect_products
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part
perfect_products 0 PRIMARY 1 id A 180
show index from perfect_brand
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part
perfect_brand 0 PRIMARY 1 id A 16
I am also going to do some reading myself on the normal join, thanks for your info...
Bye Bye
David
----- Original Message -----
From: "Benjamin Pflugmann" <[EMAIL PROTECTED]>
To: "David Bouw" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, July 20, 2001 7:11 PM
Subject: Re: Left Join very sloooowwww..
> 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
