10 messages in com.mysql.lists.mysqlRe: optimising joins: where vs. using/on
FromSent OnAttachments
Christian Hammers08 Mar 2001 08:57 
Dan Nelson08 Mar 2001 09:20 
Steve Ruby08 Mar 2001 09:28 
Christian Hammers08 Mar 2001 09:43 
Steve Ruby08 Mar 2001 09:49 
Steve Ruby08 Mar 2001 09:55 
Steve Ruby08 Mar 2001 09:56 
Randy Johnson10 Mar 2001 07:41 
Cal Evans10 Mar 2001 07:50 
Tac/Smokescreen Action Network10 Mar 2001 07:53 
Subject:Re: optimising joins: where vs. using/on
From:Steve Ruby (ste@rubysolutions.com)
Date:03/08/2001 09:55:36 AM
List:com.mysql.lists.mysql

Christian Hammers wrote:

On Thu, Mar 08, 2001 at 10:29:20AM -0700, Steve Ruby wrote:

He is doing an inner join, you are doing a left join, they (potentialy) do not produce the same results.

Hmm have to think about it... inner means fields where the right table has a NULL value in the condition are left out, right?

If you have 5 hour selects you are probably missing some vital indexes, or you are using a long of like '%blah' or "OR"

... or using 1.5GB big tables...

thanks,

-christian-

See the manual... I think you have them backwards, left join means that lines from 'a' without corresponding lines in 'b' WILL be shown with NULL

Inner join (commas) means you will only get lines where there exists a b.id corresponding to every a.id

1.5 GB tables to not produce 5 hour selects unless you are lacking indexes or using OR or '%like' or other criteria that doesn't use indexes in mysql.