Hi Benjamin
> > 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
Yes, I see, this is basically the apples/pears join I know and also use a lot..
Indeed you are right that you get another result because records which can't be joined
will also be displayed in
the result with the the 'left table' join en be left away in the normal join..
In my query this can be a problem because it is possible to add a record to
productorders table with for example extra discount..
The 'ordernr' of this discount article can't be linked to the articles table.. (Though
I must say that we have never done this in
the past..)
(But it is possible..) -> eg, hard to explain, but the pco table allows us to add
articles which can't be linked to the articles
database..
> Therefore a LEFT JOIN allways has to inspect all rows, whereas a
> normal join can optimize which rows to look at.
I see....
And you are very right about this..!!!
I ran your new query on the database and it was done in little more than 15 seconds!!!
My old query goes on for more than a minute.... (more than 4 times as long..!!!!)
The total query takes even longer because I also make a second query to get the total
sum of all turnovers.. I need this to
calculate the
percentage per brand of the total turnover made... Because the query is so hard and I
don't see a easy way to get the total amout of
all turnover (on all brands) easily I can solve this by making a small loop in PHP I
use to retrieve all records in an array and
then quickly manipulate
this array to count up total... (Another option I saw in the MySQL manual was to
insert the results into a temporary table and then
do a (quick) query
on this table to get the totals.. (This has the advantage that the sum of all the
turnovers will always match the total turnover you
get this way in stead of doing
a seperare query and having a chance that someone inserts a new order in the mean
while which will influence this number..)
> 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).
What do you mean by redundant..?
I think that you mean that it hasn't any effect because this column already is a
primary key..?
> 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.
Yes, this makes sence (date index), but I don't really ever use these queries...
It's also not really a problem that this query takes so long, but it keeps me very
busy that MySQL is blazing fast on all my queries
and
when I do some linking the speed drops.. (But I have learned that 90% has to do with
my left joining (and I know now why..) and
probably
I also bet that one table (pco) which contains a varchar column to link with tables
with will drop the speed..
B.T.W. Whats the side effects on using a index.. What I see in the mysql manual
inserts and updates get slower because the index
needs to be updated...
But what other reasons are there to not use indexes...? I use PHPmyAdmin a lot to
place indexes and I also noticed that you can
place two indexes on the
same column..?? Also does it have any use to place an index when a column is unique..?
(eg: I make all my auto-increment colums
unique..)
> PS: Your query rewritten to use normal joins (and with use of table
> aliases to make it shorter).
<--- snip snip snip ---->
The query is great, I am used that MySQL takes more than 1 minute to do an query.. But
if you thing what he is doing it's really
stiill is very fast...
Just to end of this email I have also made a query which (almost) gets the same
results in about 4 seconds.. :-)
SELECT cpo.brandname,
Sum((cpo.quantity)*(cpo.price)) AS omzet
FROM perfect_customer_productorders AS cpo,
perfect_customer_orders AS co
WHERE co.id = cpo.orderid
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 cpo.brandname
ORDER BY omzet asc
Basically I drop the linking to my articles table, products table and brand table..
All the linking to articles and then to products and then to brand is basically done
for 1 thing... And that is to have the option
to group by brandname!!...
How can I do this..??? Well, in the productorders database I *also* store the
brandname of the articles + some other values like the
productname/articlename..)
Why do I do this..? Well, when I ever need to change a brandname because the company
changes his name, or maybe I need to change the
name of a product for some strange reason I always want to be able to reconstruct the
original bill made for the order.. I can only
do this by storing the brandname etc when the order is made..
The probably are other ways to do this... I now have a lot of records with exactly the
same brandname.. But this doesn't matter...
Thanks for all the help and suggestions!!
Bye Bye
David
---------------------------------------------------------------------
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
