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
