Welcome Guest!

If you do not have an account yet on The Web Squeeze forums, please Register! It’s FREE and there are many benefits:

  • Receive Fast Advice
  • Learn Programming Languages
  • Get Professional Website Reviews
  • Quick Troubleshooting Assistance

> Join Optimization

This is a discussion on Join Optimization, within the MySQL section. This forum and the thread "Join Optimization" are both part of the Programming Your Website category.

 
 Reply to this topicStart new topic
> Join Optimization
paintingtheweb
post Apr 15 2008, 12:45 PM
Post #1


Squeezing
***

Posts: 76
Joined: 14-February 08
From: Las Vegas, NV
This may be a stupid question, so please don't haze me too much for it, but I was just curious if using the join statement is more/less or the same level of effeciency as constructing the same statement with a where clause?

Example:
CODE
SELECT DISTINCT
    b.brand_id,
    c.id
FROM
    ORGANIZATION o
    INNER JOIN CATEGORIES c
        ON (o.category = c.id)
    INNER JOIN INVENTORY i
        ON (i.SKU = o.sku)
    INNER JOIN brand b
        ON (i.brand = b.name)
ORDER BY b.brand_id ASC
LIMIT 700;

VS
CODE
SELECT DISTINCT
    b.brand_id,
    c.id
FROM brand b, CATEGORIES c, INVENTORY i, CATEGORIES c
WHERE
(o.category = c.id) AND
(i.SKU = o.sku) AND
(i.brand = b.name)
ORDER BY b.brand_id ASC
LIMIT 700;

I'm currently using the first one as I've always done, but this would be one of those queries that would take about 3 hours to run, so I'm wondering if it would have any effect on the speed if I were to use the second method?


--------------------
http://www.infoonmike.com

"Always program as if the person who will be maintaining your program is a violent psychopath that knows where you live." Martin Golding
Go to the top of the page
 
+Quote Post
Rakuli
post Apr 15 2008, 03:19 PM
Post #2


Squeeze Machine
Group Icon

Posts: 760
Joined: 13-February 08
From: Catching the squeezed drips downunder.
Hi mate,

You are actually using JOINS in the second query anyway biggrin.gif

CODE
FROM brand b, CATEGORIES c, INVENTORY i etc...


Is actually a "cross join" where every row is returned with every other row from the other tables. The first query will return a much smaller result set and of course this will be faster.


--------------------
Luke Dingle . com

Turn Over a Playful Leaf on Web Design -- read about the javascript cat
Go to the top of the page
 
+Quote Post
« Next Oldest · MySQL · Next Newest »
 
If you found The Web Squeeze to be helpful, please donate so we can keep this site FREE, FRESH, and fortified with Web Design & Development info!
 Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Collapse

> Similar Topics

    Topic Title Replies Topic Starter Views Last Action
No new   24 Squeeze Bot 1,091 25th November 2008 - 09:31 AM
Last post by: EggManJohn
No New Posts   7 karinne 368 10th May 2008 - 01:40 AM
Last post by: Antti
No New Posts   9 unitedcraig 329 11th June 2008 - 02:18 AM
Last post by: unitedcraig