Hi Benjamin, Just a quick gif picture of the explain of the other query I made.. (It's quite late now and I will read your email tomorrow again..) > Could you post an EXPLAIN for it? I am curious to see it. > > > 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 This is the explain: table type possible_keys key key_len ref rows Extra Edit Delete co ALL PRIMARY,id 2596 where used Edit Delete cpo ALL 4431 where used Bye Bye David ----- Original Message ----- From: "Benjamin Pflugmann" <[EMAIL PROTECTED]> To: "David Bouw" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Saturday, July 21, 2001 9:37 PM Subject: Re: Left Join very sloooowwww.. > Hi David! > > On Sat, Jul 21, 2001 at 04:08:51PM +0200, [EMAIL PROTECTED] wrote: > [...] > > > 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.. > > Yes. > > [...] > > 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..) > > Well, either add LEFT JOINs (but only after the join with the article > tables)... > > > (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.. > > ... or maybe you can just insert an appropriate dummy article? > > [...] > > 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... > > If speed is a concern, this sounds like the best solution to me. > > [...] > > > 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..? > > superfluous, unnecessary > > > I think that you mean that it hasn't any effect because this column > > already is a primary key..? > > Yes, it will be never used. > > [...] > > I also bet that one table (pco) which contains a varchar column to > > link with tables with will drop the speed.. > > Correct. > > > 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...? > > Filesize and insertion/update speed are the main reasons. Another is > to only use what you really need, i.e. creating indexes which you are > not sure about using at all, should be avoided. > > > I use PHPmyAdmin a lot to place indexes and I also noticed that you > > can place two indexes on the same column..?? > > To place two keys on the same column may be possible but doesn't help > anything. > > There is one exception, though, there are compound keys, which will be > of use. So, > > INDEX(id), INDEX(id) > > makes no sense, but > > IDNEX(id), INDEX(id,ordernr) > > may make sense. > > Btw, INDEX(id) and KEY(id) are synonyms within MySQL and are the > common keys. A value may appear several times. > > UNIQUE(id) or UNIQUE KEY(id) are like INDEX(id) with an additional > constraint, nameley that each value may only appear once. > > A PRIMARY KEY is like UNIQUE with the additional constraint, that it > may not contain NULL values and that there may be only one PRIMARY KEY > per table. (Additionally, primary keys have a special meaning in > database design). > > > Also does it have any use to place an index when a column is > > unique..? (eg: I make all my auto-increment colums unique..) > > It depends. UNIQUE(id) is a mean to assure that 'id' has no > duplicates, if you need the database to ensure this constraint. > > If you want a key on 'id' and you know that each value may only appear > once, I know of no reason not to use UNIQUE(id) instead of KEY(id). > > If both does not apply, i.e. you don't need a key on that column and > you don't need the database to enforce the uniqueness, it makes no > sense to create a unique key on the column. > > [...] > > Just to end of this email I have also made a query which (almost) > > gets the same results in about 4 seconds.. :-) > > Could you post an EXPLAIN for it? I am curious to see it. > > > 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 > > > [...] > > > 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... > > Your solution looks fine. One could consider to only store an id to a > table with brandnames, else, I would do it the same way. > > 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
