Hi there,
I am wondering if the Left Join syntax is a very inefficient query for MySQL (and
other databases generally speaking..)???
I have a table which consits of a few tables from which the bigest table contains
about 4500 records...
Heres the scenario:
I have 5 tables fom an ecommerce site:
table 1 contains brands: eg Microsoft, Apple Macintosch etc etc
table 2 contains a product: eg A Product named Operarting systems in which the table
has a INT column to link it to brandname
which goes with the product etc
table 3 contains a article: eg Windows 95 and Windows 98, Windows NT which is linked
to the product..
(Microsoft -> Operating Systems -> Windows 95, Windows 98 etc = table 1 -> table 2
-> table 3
When a article is ordered this is stored in the following way
table 4 contains the basic order information (customer name, order amount way of
payment etc)
table 5 contains the articles ordered (Windows 95)
I made a query which calculated the total amount of everything that is ordered the
last 30 days per brandname..
This is done by linking table 5 to table 4 (I need to link it to table 4 because there
is a status bit in table 4 which tells me if
the order hasn't been cancelled..)
I also link table 5 tot table 3, table 3 needs to be linked to table 2 and table 2
needs to be linked to table 1 to get the brand..
I add a sum in the query and group the data by brand because I want to now the totals
for each brand..
Here is the query
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
Almost all fields which are linked are INT or medium Int.. Only the
productorders.ordernr field is an *varchar* in which the order
code of the product is stored..
I know that this will probably delay a lot but it's not easy to change this and I did
do some checks to see if this makes *very*
huge differences but at the time it didn't look to
make a lot of difference...
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)
Is there a more efficient way to link tables.. I also have this problem with a small
search routine where I want to find a customer
+ order.. I have a separate customer database which I can link to the orders, but this
+linking adds a very huge delay (about 15
seconds on a search).. When I just use the orders table (with no linking) because
99.99% of the cases we need to look up a customer
who has placed an order the query is very fast...
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..??
Thanks for any 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
