Hi.
On Fri, Jul 20, 2001 at 11:22:56PM +0200, [EMAIL PROTECTED] wrote:
>
> > SELECT brand.brandname,
> > SUM((productorders.quantity)*(productorders.price)) AS turnover
> > FROM orders
> > LEFT JOIN productorders ON productorders.orderid = orders.id
> > LEFT JOIN 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...
Oh. Sorry. A shot to quick after the issue with perfect_articles. ;-)
[...]>
> > - 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...
No need to be sorry. Everyone once had to begin with.
[...]
> 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..??
With a "normal join" (I don't know the technical term), I mean
something like (both variants are semantically equivalent, i.e. do the
same)
SELECT * FROM orders, productorders WHERE productorders.orderid = orders.id
SELECT * FROM orders JOIN productorders WHERE productorders.orderid = orders.id
Additionally to what a normal join returns, a LEFT JOIN also returns
each row of the left table, even if there is no match in the right
table, with NULL filled in as value. Or in other words: while a normal
join only returns matches, a LEFT JOIN returns all rows of the left
table either where the rows are either paired with the matches, or
with NULL values, if no matches can be found.
Therefore a LEFT JOIN allways has to inspect all rows, whereas a
normal join can optimize which rows to look at.
[...]
> 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..??
No. The order is determined by the ORDER BY clause.
> > 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 have to correct me a bit. As far as I could see in the manual, there
is no variant of the normal join which supports an ON clause, so my
statement should read:
"It wouldn't matter ..."
> 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..)
With contraints I simply meant the linking condition, like
"productorders.orderid = orders.id".
What I meant was, that if one really wants the result from a LEFT JOIN
(i.e. the NULL values, too), it doesn't make sense to write the
constraint in the WHERE clause, because it would eliminate all NULL
values (because '=' is never true for a NULL value).
[...]
> 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...????
Yes, but with LEFT JOINs, there is not much room for MySQL to use the
indexes (as the whole left table has to be scanned).
So, as I said, get rid of the LEFT JOINs, if you don't really need
them (if you are not sure, I bet you don't need them).
> 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
>[pc_po].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
Please note, that the key "id" is redundant, because the PRIMARY KEY
is already a key on the column "id" (the same is true for the next
table).
An index on perfect_customer_productorders.date could also help, if
the kind of query like the one above is not too unusual for you.
> 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
Due to "perfect_customer_productorders.orderid = perfect_customer_orders.id"
an additional key on "orderid" would help.
> 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
[...]
This was the same table again?
The other indexes look fine for the query above.
Bye,
Benjamin.
PS: Your query rewritten to use normal joins (and with use of table
aliases to make it shorter).
SELECT b.brandname,
Sum((cpo.quantity)*(cpo.price)) AS omzet
FROM perfect_customer_orders AS co,
perfect_customer_productorders AS cpo,
perfect_articles AS a,
perfect_products AS p,
perfect_brand AS b
WHERE cpo.orderid = co.id
AND a.ordernr = cpo.ordernr
AND p.id = a.id
AND p.brand = b.id
AND cpo.date >= DATE_FORMAT( DATE_SUB( NOW() , INTERVAL 30 DAY ) , '%Y-%m-%d'
)
AND cpo.date <= DATE_FORMAT( DATE_SUB( NOW() , INTERVAL 0 DAY ) , '%Y-%m-%d' )
AND co.status != 2
GROUP BY b.brandname
ORDER BY omzet ASC
---------------------------------------------------------------------
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
