MySQL Database Tutorial - 25 - Full-Text Searching Video Lecture | MySQL (Sequential Query Language) Database Tutorial - Software Development

what is going on guys welcome to your
25th to toriel and in this lesson I want
to talk to you guys about something
called full-text searching it's an
awesome feature that's built into MySQL
basically if you ever thought about
making a search engine or maybe you just
have a website where you already have a
search functionality and maybe you using
like wildcards or a like statement or
regular expressions to let users search
your website for something that's nice
and all but now let me intrigue I
introduce you guys to full-text
searching is just hands-down way better
than any other search feature that you
can build an awesome thing is it's
already built into MySQL so the first
thing we need to do is we actually need
to enable this now typically whenever
you create a table for the very first
time that's when you decide what columns
you want to enable for full-text
searching on however since we already
created this table or I did rather we
just want to alter the table a little
bit so I know I didn't tell you guys how
to create tables or alter table so we're
jumping a little bit ahead here but just
go ahead and type in this code alter
table and then go ahead and type in the
table name which is items and what we
want to do is how do we want to alter it
we want to add a functionality called
full text not test text now inside the
parentheses just go ahead and write a
column that has some text in it you
don't want to write any numerical
columns here or else it's not going to
work out now go ahead and run this query
I'm not going to go ahead and run it
again because I already have full text
enabled because well I was playing
around with it later on or earlier on so
go ahead and run this and once you do
you might see a little prompt on your
screen as long as you don't get any
error messages you're good to go but
anyways now that you're in that query
full text is now enabled on that column
name so we can now basically treat it
like a search engine and search for
things in that column so let me go ahead
and run a real quick example select name
and cost from items where now instead of
having a wear statement like maybe the
word is like baby or maybe I'm going to
use a regular expression to make it baby
maybe make some wild cards ah that's for
noobs we are here to learn about
full-text searching and the syntax is a
little weird the first thing you're want
to write is match now for this um
argument or parameter whatever you want
to call it inside the parentheses you're
right
what column do you want to search in and
I want to search in the name column
because well quite frankly it's the only
one that we enabled full-text searching
on but if they have multiple columns
make sure to specify here so basically
what column do you want to search in we
want to search in the name column now
what do you want to search for and you
do that by writing the word against now
against just like match it takes
parentheses and inside the parentheses
you write your search term so what do
you want to search for well I'm just
going to search for the word baby
because I know that there are some items
with word baby it's like a baby coat
baby pacifier maybe I don't know we'll
find out
so go ahead and hit go and what it does
is it takes the keyword baby and it's
search it basically searches all the
items in the name column and it ends up
with baby coat baby seat baby soap baby
bottle pretty cool couple baby I'll just
maybe I'll buy something for my little
cousin who knows so basically if you're
like okay I could have done this exact
same thing using the light keyword
because you told me that before it
seemed pretty neat I'm going to use that
for my search engine instead well you
can but there's a couple things going on
behind the scenes and hopefully I want
to tell you guys about those and
hopefully I can talk you guys out of it
even though you can't see it in this
example what my SQL is doing is it's
ranking all of the results depending on
how well it's matched for example
whenever you go to google and type in
the word I don't know dog or something
it just doesn't get random results and
you know give you whatever is on its
mind whatever it's feeling it actually
has these results ranked depending on
you know what people click before
whatever algorithm they use they
ranked them in a very smart fashion and
that's what this is doing right here
even though you can't see it because I
don't know these are really bad examples
but it's ranking it based on the number
of keywords the number of total words in
the name column and a bunch of other
criteria that a bunch of smart people
figure it out
so aside from that excuse me I got
whooping cough
aside from ranking them automatically
for you which is awesome it's not only
better than like a regular expressions
for that but it's a lot faster too I
know you guys can't tell because these
queries are really simple and my
database isn't that big but full-text
searching is usually a ton faster than
using regular expressions or the like
statement and another cool thing that
you can do is you can customize these a
little bit further so say for example
that I don't know your user wanted to
look for baby items but they didn't want
to look for baby coats well this has a
special I want to say as a special
feature I guess would be the best way
and it's called a boolean mode so go
ahead and after you're against but make
sure you're in your parentheses still
type in boolean mode now I'm just going
to show you guys a couple arm symbols
but there are like 10 or 12 symbols that
you can use I'm going to show you guys
two real quick now
symbol the plus sign is whenever you
type plus a word it means make sure that
this word is included so it's only going
to give you results where the word baby
is included just like before
now another easy one that we can
understand - is make sure that this word
isn't included so basically we want to
search again using full text searching
but what we want to do is only search
for the items that have the word baby
and they don't include the word code so
last time we ended up with baby coat
baby seat baby so baby bottle but now we
want to take out baby coat so we would
do something like this so run this beast
and check it out we get baby seat baby
soap baby baby bottle wow that's kind of
like a tongue tongue twister but no baby
coat so again like I said those are two
easy symbols those are pretty probably
the easiest ones that I can think
right now but there are a lot other ones
and a lot of them deal with how the
results are ranked and um guys I
encourage you to go on the website go
look at all the symbols but I'm not
going to bore you going through them all
and show you all the little itty Gertie
details but anyways whenever you want to
use the symbols that's how you use them
in boolean mode so anyways I know you
guys are probably not sold on full-text
searching yet but whenever you make a
huge database trust me guys don't forget
about it use it and you'll see the
benefits of it but for now thank you
guys for watching don't forget subscribe
don't forget to add me on Google+ and I
will see you guys in the next video