Detailed Notes
1. inner joins vs outer joins
insert into stats(code,value1,value2) values ('TEST',6,12345);
select s.code,l.code from stats s inner join lkp_type l on l.type_id=s.value1;
select s.code,addr.city,addr.state,addr.zip from stats s inner join address addr on addr.t_one_id=s.value1;
select s.code,addr.city,addr.state,addr.zip from stats s left join address addr on addr.t_one_id=s.value1;
select s.code,addr.city,addr.state,addr.zip from stats s left join address addr on addr.t_one_id=s.value1 where addr.city is null;
select s.code,addr.city,addr.state,addr.zip,l.code from stats s left join address addr on addr.t_one_id=s.value1 left join lkp_type l on l.type_id=addr.type_id;
select s.code from stats s where s.value1 not in (select type_id from lkp_type);
select s.code from stats s inner join (select type_id from lkp_type where code='HOME') src on src.type_id=s.value1;
select s.code,addr.city,addr.state,addr.zip from stats s left join address addr on addr.t_one_id=s.value1 where a;
select s.code,l.code from stats s left join lkp_type l on l.type_id=s.value1 where l.code='HOME' or l.code is null;
Transcript Text
[Music] well hello and welcome back we are continuing our my sequel series we've taken a little bit of time off here took a break but now we're diving back in and this episode i want to look at indexes uh we've talked uh we've not really talked too much about them i would just sort of mention them in passing and so for this case i have a new uh table that i've created and there will be a sequel out there because it has a lot of rows now if we go back to let's go show our tables and doing so we can see we've got you know sort of our traditional tables actually let me do this been so long i forgot my other id but that's okay okay so now you can see our tables and there's a new one here called horses now if you remember let me see actually i'm just gonna do an id from horses i'm gonna see how many rows i've got to show you that okay we got 350 000 rows and so it takes a second uh actually 0.109 seconds to make that call whoops i want to do a little brief thing there we go or once it's in there we can just do a count star now if we look at horses it's got a bunch of different columns now right now we have on the id we do have a primary key set up and we do have on name we do have an index which is this we have a multi multi-key index set up on name so we'll talk we'll look at those a little bit first uh how do you create an index well you're going to create an index by using the phrase using create index and then you're going to give an index name what table and then what column so if i wanted to and let's start with this um let's do let's work we're gonna do work from damn and sire so they're mother and father so let's do we'll just do this one this may take a second let's do this like distinct damn from horses uh and we're gonna move it to like 100. oh goes that way so let's do let's look at super mess now for these today we're going to look this little part here particularly that thing in parentheses is going to be key because this is essentially how fast it ran now there are other ways to get into this but just to show you from a high level point of view the effect of indexes or indices then we're going to take a look at that so now let's do slick star from horses where damn equals super mess so there we go so i only got two horses it took us 0.413 seconds that's a little bit of time you know that's almost half a second to figure this one out and this is for one row so let's see what happens when we create an index let's remember that's create index and we're going to give it a name so let's call this uh damn name actually we're going to use it horse's damn name on horses now typically what you want to do you depends on where you're at but for the most part your index name should be in unique even though when we disable them when we delete them we're gonna we'll see that we use a table name but it helps do that it's just easier when you're doing debugging because you'll be able to see stuff where particularly when you you look into performance tuning and such you'll be able to see which index is being used specifically so if i create an index it didn't take too long in this case sometimes it can take a while to build an index depending on how big the table is but you see here it took us a little over half a second so now let's go back to that prior call that we made and now it's coming back in .01 seconds it's coming back instantly basically because it's using this index so now if we do the same thing with sire where does that get the distinct sire and then oh actually let's just go back to that one um there we go so here the sire is spinster so now if we use that so we're going to look at we're going to get that same row where sire equals and now granted uh took a little bit so let's do let's find some more about that uh let's say ambreeder id equals two one so we're gonna add a little bit more and breeder equals two eight one nine oh breeder id my mistake so now we're back to that 0.41 seconds now if we did the same thing and actually let's sort we'll keep some of these well you'll allow you guys to do your almost uh selects um so now if we do the same thing and we do damn equals super mess now we get it back super fast no pun intended because it's going to pick up because that name's in there it this is where this my sql engine is smart now most modern databases are like this some are not if you go back to older versions it only it basically just looks for an index here and then stops when it doesn't find it or will look for a combo index in which case you're going to find something that's going to be it's going to take a little bit longer now let's do to show a multi-column index so let's go back to here oh uh so we're here's where we have that breeder id now it's coming back pretty quick because it's cached right now because we've already seen this exact query so if we do it with uh and we've already done this one so let's change up uh let's do breeder id here and let's see if we can get a different sire oh he's only got one uh let's see if we get a just go off by one see there we go so we've got something a little different now it takes a little bit to find this one so now what we're gonna do is we're gonna do this one where sire equals jess's dream and we're going to see it's going to take oh shoot my mistake because it doesn't like that i'm going to have to let's just change oh let's change that whole thing up oh sorry tutorial and then let's go back and let's pick uh 65 let's see if that gives us something nope 64. oh 63. oh come on give me a breeder id 62. there we go okay so there we got one and this is going to be so now if we do well let's do a couple because i want to see well let's do where uh let's go off of well let's see i think i have my name already in there so if i do our name equals strong eagle yes i already have a name index in there it's gonna be hard to show this but let's go in there and create a multi-column index so let's say well let's say that we want to see now we already have uh dam in there but what we could do is let's say we want to find a breeder what would this be that would be an owner breeder combo let's say we're going to search on that a lot so in that case it's pretty easy to do we're going to do create index uh horse breeder owner let me call it whatever you want basically on horses and then just give it a list of the ids so we're gonna do owner id comma breeder id it took us a little over half a second and so now if i do select star from horses where owner id equals one two six four four eight and breeder id equals 28162 it's going to come back pretty much instantly boom so it's going to use both of those now if i do let's say i go back to owner id so that's i've got to have those combos so now if i go pick let's just pick a there it still came back pretty quick because it's probably looking at that without the breeder id this is where mike staples well the database engines in general are getting sort of smart so if there's an index that's useful then it's going to be able to do it so now i'm also probably going to see if i come back with breeder id of oh let's just pick some number let's see if it comes back uh it's an empty set it did take a while because it's breeder id but if i did owner id oh i do owner id equals and let's just do an empty set again so let's do um this guy and reader id oh shoot reader id inverter id equals this uh yeah it comes back pretty quick because it's hitting the here it's not hitting a index at all here it's trying to hit an index is saying i don't have that so that disappears pretty quick so same thing if i do where owner id and i don't give it here because i just uh so that was just in there so let's give it like a different number whoop well let's try that that's going to come back too quick because it's too out of the range it's probably going to come back super fast now anyways oh there it was but it's still it's hitting because you're looking at index uh and it's going to i guess because it's because of the first column it's going to pick that up this is where how a database uses an index can be um it varies from database and it can take a little bit of work to figure out what index is being using used and how it's being used so for example you may now you don't want to overly index something but you could have an index on in this case breeder but also an index on breeder owner you probably don't really need that but there are some cases where like for example would only use one of those if that's the the id in there now we have one for name and breeder so we could come in and let's see what that how that works so if we do name equals and it's probably going to find whichever is the fastest so name is turalura and let's say owner id equals oh we did oh and damn that's what we had before and damn equals so these are both we have indexes for both of these and it's going to come back pretty quick but you don't really know in this case which one it's hitting now also just to show how these things have changed we can see here that name has an index dam has an index and owner has an index and that's what that key is then there's some other things you can do i think we've we've talked about like you can make them unique and things of that nature but even here now granted you know if you've got 10 rows in database which is why i grabbed this one i just threw this thing in there because i wanted something that had more than you know a few rows because you're not going to see those time differences but this you know we were seeing half a second versus a tenth you know or what is that a thousandth of a second differences which is almost instantaneous versus half a sec or almost half a second with 300 000 rows now if you start to move up with a million rows or tens of millions of rows and then start joining tables based on that it can take quite a while so if i were to let's say create table owners as select all righty come up let's just get something here come a sire from horses it's going to take a second and so now i've got 300 000 in rows rows there so now if i do select star from uh horses uh let's just do this let's do owners dot sire from horses enter join owners on courses dot uh owner id equals owners dot owner id where uh let's see owner id equal well let's say where sire equals idiot proof i think is one we're gonna see uh oh that's ambiguous so let's do based on owners that sire okay so it took ah it wasn't too bad it came back moderately quickly um if i do well probably this is going to be super fast now if i do horses.sire oh well there you go oh because owners have nothing to do with that so okay so uh this again it's taking a little bit because we're joining on this id that does have an index in horses right yeah it does have an index in horses but it doesn't on the other table so if we take the same thing and let's create um can i do that real quick so if we create table breeders the same way as select breeder id from horses now we don't have any index here on either table and then we come in and we do select breeders.sire from horses or even let's say from breeders from breeder enter join horses on horses dive reader id equals breeders dot reader id uh what did i do oh from that's all the way back here it's not form it should be from there we go that's gonna take us now look it's taken a while because we don't have any indexes set up so now since we're doing this search across a lot of records 300 000 well 350 000 times 350 000 it's gonna take it a little bit and that puts us basically the end of this lesson because this thing will not finish i bet before i do so what we've covered today is indexes and we're going to do some more playing around with stuff and next time around but until then go out there and have yourself a great day a great week and we will talk to you next time yep it is still running you
Transcript Segments
[Music]
well hello and welcome back we are
continuing our my sequel series we've
taken a little bit of time off here took
a break
but now we're diving back in
and this episode i want to look at
indexes uh we've talked
uh we've not really talked too much
about them
i would just sort of mention them in
passing
and so for this case
i have a new
uh table that i've created
and there will be a sequel out there
because it has a lot of rows
now if we go back to let's go show our
tables
and doing so
we can see we've got you know sort of
our traditional tables actually let me
do this
been so long i forgot my other id but
that's okay
okay so now you can see our tables and
there's a new one here
called horses
now if you remember
let me see actually i'm just gonna do an
id
from horses i'm gonna see how many rows
i've got to show you that okay we got
350 000 rows
and so it takes a second
uh actually 0.109 seconds to make that
call
whoops
i want to do a little brief thing there
we go
or once it's in there we can just do a
count star now if we look at horses it's
got a bunch of different
columns
now right now
we have on the id
we do have a primary key set up and we
do have on name we do have an index
which is this we have a multi multi-key
index set up on name
so we'll talk we'll look at those a
little bit first
uh how do you create an index
well you're going to create an index
by
using the phrase
using create index and then you're going
to give an index name what table and
then what column
so if i wanted to
and let's start with this um
let's do
let's work we're gonna do work from
damn and sire so they're mother and
father
so let's do
we'll just do this one this may take a
second
let's do this like distinct damn
from
horses
uh and we're gonna move it to like 100.
oh
goes that way so let's do
let's look at super mess
now for these today we're going to look
this little part here particularly that
thing in parentheses is going to be key
because this is essentially how fast it
ran now there are
other ways to get into this but just to
show you from a high level point of view
the effect of indexes
or indices
then we're going to take a look at that
so now
let's do slick star from
horses where damn equals
super mess so
there we go so i only got two horses it
took us
0.413 seconds
that's
a little bit of time you know that's
almost half a second
to figure this one out and this is for
one row
so
let's see what happens when we create an
index let's remember that's create index
and we're going to give it a name so
let's call this uh damn name
actually we're going to use it horse's
damn name
on
horses
now typically what you want to do you
depends on where you're at but for the
most part your index name should be
in unique even though when we disable
them when we delete them
we're gonna we'll see that we use a
table name
but
it helps do that it's just easier when
you're doing debugging because
you'll be able to see stuff where
particularly when you you look into
performance tuning and such you'll be
able to see which index is being used
specifically
so if i create an index it didn't take
too long in this case sometimes it can
take
a while to build an index depending on
how big the table is
but you see here it took us a little
over half a second so now
let's go back to that prior
call that we made
and now it's coming back in .01 seconds
it's coming back instantly basically
because
it's using this index so now if we do
the same thing with
sire where does that
get the distinct sire
and then oh actually let's just go back
to that one um
there we go so here the sire is spinster
so now if we use that
so we're going to look at we're going to
get that same row
where sire equals
and now granted uh took a little bit so
let's do
let's find some more about that
uh let's say
ambreeder id equals two one so we're
gonna
add a little bit more
and
breeder equals
two eight one nine
oh breeder id my mistake
so now we're back to that 0.41 seconds
now if we
did the same thing and actually let's
sort we'll keep some of these
well you'll allow you guys to do your
almost uh selects
um
so now if we do the same thing and
we do damn equals
super mess
now we get it back super fast no pun
intended because it's going to pick up
because that name's in there
it this is where
this
my sql engine is smart now
most modern databases are like this
some are not if you go back to older
versions
it only
it basically just looks for an index
here and then stops when it doesn't find
it or will look for a combo index
in which case you're going to find
something that's going to be
it's going to take a little bit longer
now
let's do
to show a multi-column index so let's go
back to
here oh
uh so we're here's where we have that
breeder id
now it's coming back pretty quick
because it's cached right now
because we've already seen this exact
query
so if we do it with
uh and we've already done this one so
let's change up
uh let's do breeder id here and let's
see if we can get a different sire
oh he's only got one uh let's see if we
get a just go off by one see there we go
so we've got something a little
different now it takes a little bit
to find this one so now what we're gonna
do is we're gonna do this one
where sire equals jess's dream
and we're going to see it's going to
take
oh
shoot
my mistake because it doesn't like that
i'm going to have to
let's just change oh let's change that
whole thing up
oh sorry
tutorial
and then
let's go back and let's pick uh
65 let's see if that gives us something
nope
64.
oh
63.
oh come on give me a breeder id
62. there we go okay so there we got one
and this is going to be
so now if we do
well let's do a couple because
i want to see
well let's do where
uh
let's go off of
well let's see
i think i have my name
already in there so if i do our name
equals
strong
eagle
yes i already have a name index in there
it's gonna be hard to show this but
let's go in there and create a
multi-column index so let's say well
let's say that we want to see
now we already have uh dam in there but
what we could do is let's say
we want to find a breeder
what would this be that would be an
owner breeder combo
let's say we're going to search on that
a lot so in that case it's pretty easy
to do we're going to do create index
uh horse
breeder
owner let me call it whatever you want
basically
on horses
and then just give it a list of the ids
so we're gonna do owner id comma breeder
id
it took us a little over half a second
and so now if i do select star from
horses
where
owner id equals one two six
four four eight
and breeder id
equals 28162
it's going to come back pretty much
instantly boom so it's going to
use both of those
now if i do
let's say i go back to owner id so
that's i've got to have those combos so
now if i go pick let's just pick a
there
it still came back pretty quick
because it's probably looking at that
without the breeder id this is where
mike staples well the database engines
in general are getting sort of smart so
if there's an index that's useful
then it's going to be able to do it so
now i'm also probably going to see if i
come back with breeder id of
oh let's just pick some number let's see
if it comes back
uh it's an empty set it did take a while
because it's breeder id but if i did
owner id
oh
i do owner id
equals and let's just do an empty set
again so let's do um
this guy
and reader id oh shoot reader id
inverter id equals this uh
yeah
it comes back pretty quick because it's
hitting the
here
it's not hitting a
index at all
here it's trying to hit an index is
saying i don't have that so that
disappears pretty quick
so same thing if i do where owner id and
i don't give it
here because i just uh so that was just
in there so let's give it like a
different number
whoop
well let's try that that's going to come
back too quick because it's too out of
the range
it's probably going to come back super
fast now anyways oh there it was but
it's still it's hitting because you're
looking at index
uh and it's going to i guess because
it's because of the first column it's
going to pick that up this is where
how a database uses
an index can be
um
it varies from database and it can take
a little bit of work to figure out what
index is being using used and how it's
being used
so for example you may now you don't
want to overly index something but you
could have an index on in this case
breeder but also an index on breeder
owner
you probably don't really need that
but there are some cases where like for
example would only use one of those if
that's the
the id in there now we have one for
name and breeder
so we could come in
and let's see what that how that works
so if we do name equals and it's
probably going to find whichever is the
fastest so
name is turalura
and let's say owner id
equals oh we did
oh and damn that's what we had before
and damn equals
so these are both
we have indexes for both of these
and it's going to come back pretty quick
but you don't really know in this case
which one it's hitting
now also just to show how these things
have changed we can see here
that name has an index
dam has an index and owner has an index
and that's what that key is
then there's some other things you can
do i think we've we've talked about like
you can make them unique and things of
that nature but
even here now granted
you know if you've got 10 rows in
database which is why i grabbed this one
i just threw this thing in there
because i wanted something that had more
than you know a few rows because you're
not going to see those time differences
but this you know we were seeing half a
second versus a tenth you know or what
is that a thousandth of a second
differences which is
almost instantaneous versus half a sec
or almost half a second with 300 000
rows
now if you start to move up with
a million rows or tens of millions of
rows and then start joining tables based
on that
it can take quite a while
so if i were to
let's say create
table
owners
as select
all righty come up
let's just get something here
come a sire
from horses
it's going to take a second and so now
i've got 300 000 in rows rows there so
now if i do select
star from
uh
horses
uh let's just do this
let's do
owners dot
sire
from horses
enter join
owners on
courses dot
uh owner id
equals
owners dot owner id
where
uh let's see owner id equal well let's
say where
sire equals
idiot proof i think is one
we're gonna see
uh oh that's ambiguous so let's do based
on owners
that sire
okay so it took ah it wasn't too bad it
came back moderately quickly
um
if i do well probably this is going to
be super fast now if i do horses.sire
oh well there you go
oh because owners have nothing to do
with that so okay so
uh this again it's taking a little bit
because
we're joining on this id that does have
an index in
horses
right yeah it does have an index in
horses but it doesn't on
the other table so if we take the same
thing and let's create
um can i do that real quick so if we
create table breeders
the same way
as select breeder id from horses
now we don't have
any index here on either table
and then we come in and we do
select
breeders.sire
from horses
or even let's say from breeders from
breeder
enter join
horses on
horses dive reader
id equals breeders
dot reader
id
uh what did i do
oh from
that's
all the way back here it's not form it
should be from
there we go that's gonna take us now
look it's taken a while
because we don't have any indexes set up
so now since we're doing this search
across
a lot of records
300 000 well 350 000 times 350 000
it's gonna take it a little bit
and that puts us basically the end of
this lesson because this thing will not
finish i bet before i do
so
what we've covered today is indexes and
we're going to do some more playing
around with stuff and next time around
but until then go out there and have
yourself a great day a great week and we
will talk to you
next time
yep it is still
running
you