I'm by no means an expert, but I made left joins with 2 tables
with about 4500 and 2800 records each. They were basically
structured quite the same. I had response time at about .7
seconds on the larger and and 15 seconds on the other - which
kept me wondering why that should be.
I found that the slower table had about twice as many columns, so
I split that table to test if that would make any difference, but
it did not; I left only the necessary fields for the query in the
basic table, so it had less fields than the faster table.
Then I found
> http://www.mysql.com/doc/S/e/Searching_on_two_keys.html
> MySQL doesn't yet optimize when you search on two different keys
> combined with OR (Searching on one key with different OR parts is
> optimized quite good):
which is exactly what I did, so I left it at that, as I needed
that query for administration purposes only.
All other queries, including those with LEFT JOINS, only take
fractions of seconds, sometimes only a few milliseconds.
On every single page, I do a lot of queries, and the pages load
very fast (unless there is no bottleneck somewhere out there). It
is really amazing, and php has to load a huge program and compile
it etc. I didn't implement any of the cool stuf that's available
for performance.
Well, it wasn't always that way... Want to know how I found out?
I implemented a stopwatch mechanism, so that I could track any
single codeline down, mysql queries, too, of course. So I found
that queries are done very fast. Of course, I work with mod_php
and mysql_pconnect.
Along the way, I made some really surprising discoveries. With
this mechanism at hand, I could examine the running, live system
without disturbing normal users and eventually I would find
out... And I did. None of that stuff related to MySQL.
I had some php construction which consumed time excessively. Once
I knew, it wasn't that hard to construct fast solutions for the
same job.
I didn't examine each and everything yet, but I know that if I
want to, it is no problem any more.
But how come you need MINUTES????
BTW: if you want to know about that stopwatch mechanism, I can
elaborate on that, maybe pm, as this doesn't really relate to
MySQL well.
Sie schrieben am Freitag, 20. Juli 2001, 00:49:23:
> 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
--
Herzlich
Werner Stuerenburg
_________________________________________________
ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen
Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409
http://pferdezeitung.de
---------------------------------------------------------------------
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
