MySQL Database - Full Course

MySQL Database - Full Course

SUBTITLE'S INFO:

Language: English

Type: Robot

Number of phrases: 2981

Number of words: 16641

Number of symbols: 71397

DOWNLOAD SUBTITLES:

DOWNLOAD AUDIO AND VIDEO:

SUBTITLES:

Subtitles generated by robot
00:00
mysql is one of the most popular databases learn how to use it in this course from a senior database engineer welcome to this basic mysql course i want to start by appreciating you for trying to learn a new skill let me introduce myself my name is bharat and i work for salesforce as a senior database engineer i have over 12 years of experience with a variety of databases oracle being the main one i have experience working with companies like chase
00:30
paypal wells fargo stubhub etc let me answer a few basic questions for you first and that is who what and why so who should take this course this course is meant for database professionals who want to expand their skill set if you are a software engineer or a full stack developer and you want to gain a deep understanding of mysql database this course is for you and if you're a college student or a computer science student or a fresh graduate this course will give you uh some
01:01
knowledge on database internals so why should you learn mysql mysql is the most popular open source database and of course postgresql is definitely up there as companies move their data from on-prem to cloud they usually like migrate to a cloud native database or an open source database like mysql or postgresql in order to save cost so let's say that you are an oracle database expert if you gain knowledge on
01:31
a database like my sequel then you could help companies migrate their data from oracle to mysql and that can be really valuable now let's look at what is being covered in this course now first of all by my sequel i mean the mysql innodb storage engine throughout this course which is used behind an e-commerce website or a bank or a financial institution and so on and mysql offers a variety of storage engines my isam in-memory storage engine or some popular
02:02
storage engines which are available we are going to learn about mysql in odb i am not covering any other type of storage engines now these are the topics that i'm going to cover in this course and please note that this is a database administration course so it is 80 database administration and for people who are completely new to databases i have included sql basics so you'll be learning about database installation mysql workbench database indexes
02:32
database logs uh you'll also learn a little bit of performance tuning uh that is sql explain so these are some interesting topics that i'm going to cover so what do you exactly need to get started with this course you need a pc or a mac so if you're using a pc then i recommend you to actually look into the worksheets or supplemental material that is attached in the description if you have a mac laptop then you are in the best position to learn this course because then you can just see what i'm
03:05
typing and you can just type the same commands and just follow along from end to end and most of all this is the main requirement i want you to create an aws account that's correct an amazon web services account so if you don't know what i'm talking about please do look into my worksheet which can be found in the description i've attached some resources which will show you how to create an aws account i will be using an aws ec2 instance throughout the course and i'll show you how to create one but
03:36
one main thing that i want you to remember is so after each study session of yours you can shut down your ec2 instance that way you don't have to pay any unnecessary cost and please do remember that you don't have to keep your ec2 instance running 24 7. so once you have created an aws account and sign in you will land on this dashboard or this page and you can go to the services menu right here and then under compute
04:13
you can choose easy to so over here on the left hand side you can choose instances and then here we're going to create an instance which will be our lab environment so click on launch instance and then let's choose an image for our instance so i'm going to choose a red hat enterprise linux version 8 64-bit and my instance type is going to be t2 micro which is free tier eligible
04:45
and you need to choose an appropriate subnet if you just created an aws account you can just leave leave it whatever default subnet that shows up for you for me i'm going to choose maybe a specific one and then make sure to enable this auto assign public ip option because that will assign a public ip to your instance using which you can ssh into your instance
05:16
from your laptop and leave all the other settings as this and let's allocate like 25 gigs for the database or for the entire instance and you can just leave the rest as is go to the next page where you can create a tag for your instance so i'm going to call it mysql instance
05:53
one go to the security group page and then here what's important is that you need to be able to ssh into the instance and you need to you need to create proper firewall rules for that anyone basically anyone in the world can log into this instance via port 22 and that's not secure at all and i will take care of this instance because once i finish recording i usually remove
06:25
it so i know how to handle this but when you create rules make sure to put your laptop's ip in there just so it's more secure then now you can review your configuration and then click on launch but if you want you can create a new key pair and then just give it some name and then download it before you create the instance
06:56
for me i'm just going to choose an existing key pair maybe this one and i say acknowledge uh maybe a different one all right this one and launch instance now your instance is getting created and it might take a couple of minutes to create this instance okay that's how you create an ec2 instance and now that my instance is up and
07:30
running and i can see the public ip uh later i will be installing my sequel on this instance so that's what i wanted to show you in this lesson so what you're seeing is basically my sequel documentation showing all these different installation guides like windows and mac and so on so we are
08:03
interested in linux installation or basically my sequel installation on linux and there's a couple of guides actually so this one is basically installing generic binary we're going to skip that and go here and even within installing my sequel on linux there's a bunch of guides so the recommended method of installation is using rpm packages from oracle but we're going to use this mysql yum
08:36
repository based installation and it's actually quite straightforward so for this installation um we need to go to mysql.com and downloads and we are downloading the community edition and go to yum repository as you know the instance that we created has rhl8 red at enterprise linux 8 running on it
09:07
and so we need to download this rpm but then we need to download the rpm on the instance itself the one that we created so let's actually log into the instance so i'm going to use ssh and i'm going to use my private key and login as easy to user which is the default user and basically get the public id of my
09:41
instance then we log in and we switch to root okay so one thing that we need for downloading this rpm to this linux instance is wget package so let's go ahead and install that first all right so now the wget is installed we need to download the rpm that we just saw so to get the link of this rpm we need to go
10:20
into this download and we have to right click here and copy the link and if you're installing on a different os uh you need to click on the appropriate button okay so we got the link and let's just go ahead and paste that link over here like wget and the link and that command downloads this package now we are going to use an
10:52
rpm command to install this package so this package as i mentioned before is going to add this my sequel yem repo to your local system repo list with red hat enterprise linux installation you get like a mysql module by default so let's disable that one if you don't disable it then this will interfere with our mysql installation
11:25
so let's go ahead and disable it using this command and don't worry about writing these commands i will put a link to my git repo with all these commands in the description so all these have been disabled now let's go ahead and install mysql community server edition using yum install mysql community server and let's put minus y in there
11:57
just to go ahead and accept all the prompts and that's installing all these packages all right so my sequel has been installed let's go ahead and start the mysql database using systemctl command and let's check the status so now our mysql database is up and running okay so the log file of this mysql
12:34
database software is under war log and then if you grip temp from this log file you will get the temporary password for root user and you can use that to log into the mysql database and how do you log in you use this command mysql minus u that's going to be root and minus p
13:06
is for password paste login and then we're logging into mysql database so let's use this password and see if it logs in and we are in and if you run any command at this point mysql is going to ask you to reset the password using alter user statement we can do this in a different way so there is a
13:39
executable called mysql admin and this is the command for it mysql admin minus u the name and minus p password we are going to reset the password of root user and let's provide the the current password which is this temporary password first and let's provide the new password now all right the password has been accepted now let me try to log
14:10
in with this new password using the previous command mysql minus u root and minus p my sql let me put the password i said just now we are in so let's go ahead and run a simple show databases command which shows all the default databases that come as part of the installation so one more thing that we need to do to complete the installation is to load a time zone file
14:41
a time zone table as shown here so if i do a select star which is basically a sql query to read from this table you can see that the table is empty right now so let's exit out and run another command to load time zone related data so this is the command and let's go ahead and run it and i'm going to go ahead and put my
15:12
password and that loads a bunch of data you can ignore all these warnings let's go back to our my sql database so if you do a select star [Music] from mysql.timezone again it shows a bunch of data so now you're good and that completes the mysql database installation all right folks in this section we are going to talk about data modeling
15:46
okay so database design data modeling schema design these are all interchangeable words terms well database design is an ongoing process so you come up with a basic design when you sort of like create your application and then as the application you know gets added new features enhancements improvements you basically iterate over this design right you keep adding new things to your design and so on so
16:19
the first thing that you do when you're doing database design or data modeling is understand business data and then once you understand business data you have to come up with a logical design of your database what do i mean by that well basically you have to design your tables the columns that goes into those tables indexes constraints like primary key constraint unique key constraint not nail constraints default values
16:50
foreign keys like these are all various things that you need to create when you [Music] when you come up with a logical design of your schema once you actually have this basic uh table design or schema design then you can look for data redundancy that is basically you see where your data is repetitive and then you start eliminating it by normalizing your
17:22
tables actually and that's because data redundancy causes data anomalies what i mean by that is when you have like multiple occurrences of the same data when you are let's say updating certain data you have to update in many places and if you forget to update even one place now you you have two versions of the same data in your database and that creates like data anomaly data inconsistency
17:52
is along the same lines actually and all that happens because of data redundancy so what we are looking at is basically a spreadsheet and a spreadsheet is basically a huge table a large table right and and what what we are going to do is basically design a table for a e-commerce website an e-commerce website is basically like an amazon or ebay or whatever like online
18:21
uh business like online e-commerce website like alibaba or whatever right let's say like you have only one table in this database right and you start as the orders come in through this website you start putting data into this table right you have uh you know let's look at some of the things that you will record in this table right you obviously need like account of your order so you might like numbering your orders and then how it is coming like you know
18:52
is it desktop or mobile or what product is it like you know here i have got like a couple of books and then book titles and then the price of the products and then who's the customer customers details payment details delivery details and so on so these are all part of like uh e-commerce uh world basically right and you have one giant table and if you look at the data here right so
19:22
you know here i've got like a couple of customers buying uh you know sort of like two different prod products right so and you can see that the data has in been repetitive what i mean by that is like every time i buy the same product i have to repeat this data like the first order that came in was through the web desktop website
19:53
that was bought by bharat and then you can see all the details of this customer and and all the details about the product and the payment uh payment details as well then the second order that came was from a different person but then it was you know the the order was for the same product and you had to repeat the product information right the third was from the previous customer but then this time he bought a different
20:24
product this information the customer information has repeated actually so there is a lot of data redundancy data has been repetitive so this is basically a denominated database where you have only one table or a handful of tables we pack all the information from your website for your business into these few tables actually this is a denominated version of your database
20:55
let's actually go look at what else you could do so what you can do is uh basically you can start with this basic denomination table and then you can start taking out all the redundant information out of your database or your table actually it first thing i did was i took out like the customer information i put it in a separate table right and i've got only customer details here and i started like putting a id number
21:26
for each customer right or a customer number whatever you call it once i take out the customer information i have the orders table the initial table i'm calling orders table looking like this now right and you can see that uh i've got like customer id column over here and what is this customer id column your guess is right so this customer id column is the same as what you see over here right so and
21:58
why do i have that because i i need a way to relate these rows as you can tell like you know these are columns these are rows these rows i need to be able to relate to a customer right if i take out customer information then how can i relate you know this table and that table it's through a common column uh or a bunch of columns actually in this case it's just one column so customer id right i'm just like putting the id number over here
22:28
and what else actually we can take out of this table so this is one level of normalization right so let's keep normalizing which is like take out the product details right so product details are also repeating so here you don't feel that much pain because there's only three records in this table what if the table has million records right this is why we need to normalize the table now you actually take out the product information and move it to a different table and then i have a product id column just
23:00
to number like id the products actually and your orders table will look like this then you take out the payment information to a different table and your orders table will then look like this this is basically the process of going from a denominalist schema or a database to a normalized database when you have your data in a single table then you don't need to do any joins so you might ask actually what are joints actually when you're running queries like using
23:31
sql sql is a language right a structured query language when you're running commands in your database you you can get all your data from this one table if your database is totally denominated whereas if you have many tables you need to sort of combine or join in these tables and then you have to get the data out so that is called joining the tables so when you have a denominator database you don't have to do many joins
24:01
and that is kind of good in a way because your database doesn't have to think that much to get the data you say i want this data and this data is available in this table so it's just very straightforward whereas like in a in a normalized database when you join many tables then your database engine which which they call optimizer in um in oracle or in most of the data most of the databases so this database engine has to think more
24:33
as in like okay which table should i scan first and how should i filter the data in this table and then okay i take the result set from that table and i have to join with these other tables so there is so much more thinking there is so much more uh processing that has to happen on whichever server this database is running on right and because of that the performance will be kind of like slower right and it will consume a lot of resources
25:04
and you have all that happening at scale as in like many operations are happening at the same time then you basically have slow performance actually or at least slower than what it would have been in a denominator database but at the same time we are removing so much data repetition or data redundancy is very low because of that the storage needed in a normalized database is much lower so you cannot actually
25:34
like generalize and say a normalized database will always be slow or denominate database will be fast it is all like it depends actually you have to look at the data and see how much repetition is happening etc etc so but generally this is how it goes as you go through this design process right you know see what we have done actually right so we have decided then the tables that we need like you
26:04
know we have order stable products customers and payments and we decide the column names and then not just that for each table you need to decide actually what will be the primary key what i mean by that is the primary key is is a unique key and which cannot be null actually which is very important so using this primary key you should be able to identify any record in this table any row in this table for example if i say here
26:35
the primary key is order number then i can anytime if i have an order number then i can look up this table let's say order number equals 2 i can just get this record out of my database and then you need to also have like some unique keys actually right so unique keys is pretty much like primary key and unique key can be null a primary key cannot be null as i mentioned and then you can also have indexes on your table
27:06
so indexes are ways to basically select your day data faster let's say that i often search this table based on customers email then i need an index on a customer email column right you need to decide that and you need to decide about which columns can be null right here none of the columns can be null let's say you have uh another column called preference right a customer preference as in what
27:38
kind of shipping or what kind of or which phone number is preferred or something like that so that can be a null column right so you can have null columns otherwise you define your columns as not null let's say like in your orders table you have this delivered column when an order is basically created when a customer buys a product on your website of course it's not delivered immediately at the time of order creation the delivered column will always have
28:09
no or n a n value right all these things all these decisions that we are making we're talking about is part of schema design and once you have all this figured out you can put the information in a er design tool entity relationship design tool and in the next section i will show you how i do that on sql workbench my sequel workbench basically you can actually then
28:40
have a pictorial representation of your logical design of your database right and that's basically what you call an er diagram and of course you can talk about the relationship between like two tables let's say you can say oh this table and this table they have one too many relationship for example each customer can place many orders so that is actually a one two many relationship right but one
29:11
you know one order can be done by only one customer right so so that kind of thing so you have one to one relationship one to many relationship or many to many relationship between tables actually these are all part of uh data modeling but you don't need to be worried so much about that as long as you have clear idea of like what data is coming into your database and along the way you need to define like the data type of your columns actually that is very important
29:42
your names are going to be a varchar you know phone numbers can be numbers and then email is again like a watcher and your id column or number columns are going to be int or number these are all some decisions that you would make in a data modeling task actually that's pretty much what i want to say about data modeling um and there's much more we can talk about it and like atomicity like you you have all
30:15
the address sort of like attributes packed into one uh column we we need to split that as well so that is called atomicity you can have address separately city separately state separately and you know zip code separately right so those kinds of things there are nuances that make your database more and more efficient and of course we're not going to go into a lot of details there but this is the basic data modeling that
30:46
you need to understand and as i said before in the next section i will show you how to take this and then input that on my sql workbench a quick recap of what i did in the last section i basically created a logical design of an e-commerce website so what you're looking at is a table that i started with it's a denominalized table and we basically took this denomination table and we normalized it as you can see
31:21
there is four versions of this table i'm calling this table orders table so there is four different versions and i with each iteration i took out repeating data so finally we landed with four tables apart from the original orders table so now we have also customers products and payments in this video i'm gonna take this and all these tables and i'm going to take the structure and i'm going to create a logical design
31:52
okay so let's actually go to my sequel workbench and i am already connected to a database what i am going to do is go to file and go to new model so here we can add a new er diagram an entity relationship diagram and let's call this database ecom store something like that so let's go ahead and start creating our tables
32:22
now i'm not going to be creating all four tables that would take probably more time or long and i'm going to be creating a couple of tables and that should be enough for you to understand how we're doing this so let's just start with the customers table so the customer table has five columns this is the icon for creating a new table you can drag and drop or you can try to draw and now double click and then create a table called
32:53
customer and here we can start putting the table the column names customer id and then this is going to be populated by a sequence so sequence is a database object and it's going to be an integer so we can leave it as this and we can have it as a primary key that's fine and a primary key has to be populated it cannot be null so that is automatically selected
33:24
the next one is customer name we could split that into first name and then we can choose watch our and maybe give a little bit more room as in the the length for the name and then last name again a watch are 100 and then all these cannot be null so we can choose not null constraint so these are different constraints which are available let's go ahead with the next one address again address and if you
33:57
remember i talked about atomicity so you want your columns to be you know atomic in in the sense that here basically the whole address is packed into one column good practice to actually split that into atomic columns as in address separately city separately state separately and then zip code separately so we have all these and of course none of these can be null and what else
34:39
is there so customer phone number phone number is going to be all numbers but then i want to make it 10 numbers of course not null and customer email so can just say email id 100 make it not null so since id is the primary key here or customer id i want to make sure that that we have a constraint to avoid repeating customer information
35:10
for example if you have one customer's data for id one i don't want the same customer customers data to repeat for a a different id for example id2 so i'm going to actually make email id unique for each record over here and then may all maybe phone number also so these are all unique key constraints or unique constraints that's it so we have the customers table created so let's go back and see
35:43
what else we have so let's now create i would say product and then you basically do the same thing select that just for creating a new table and then now here you can just draw and this one i'm going to call it product and we're going to go through the same uh process and then put the product column names in there
36:33
and if you're wondering this is the same customer id column that we added over here and we are going to make that a foreign key in a minute so let's go ahead and split that into multiple columns because again everything is packed into one column which is not a good practice so let's say let's call it a credit card number you know if the customer is using paypal
37:22
then we need a email so we can use email id over here so this can be null or not null based on what payment type is being used so that's okay so expiration date is going to be a date column so let's actually change that so if you're not sure you can hit that the drop down and then choose a proper data type for each the other thing that i mentioned which is
37:54
uh basically about foreign key uh this customer id is the same as what we added over here so let's actually make that customer id a foreign key so we can just call it customer id foreign key one and then the table that is going to be referenced is the customers and the column is going to be customer id and that's it so you can see that now we have
38:26
a connection or a relation between these two tables i'm going to actually just add the orders table as well i've created the orders table as well which is the main table and i'm going to now create some foreign keys for the orders
39:16
everything is done uh if you want to create any indexes at this point you can do that so i guess we are done so we uh basically added four tables to our logical design these four tables and then we have created columns and then defined their data types and also we created the foreign keys and of course primary key and unique for
39:48
each of the tables and you can see the the foreign key relationship uh you know clearly showing here and uh that's you know that's what you would do to create a data model uh all right so now actually let's just go ahead and create a sql script for this data model so you go to database and then do forward engineer
40:17
and then basically you provide the database details where you want to create this these tables or the schema so this is these are my details continue go to the next one provide the password all right now we are connected i had to try the password two three times all right and this has basically created a sql script for us to create the schema
40:51
and the tables with all the primary key unique key and foreign key constraints so what we can do is we can just continue and then now the database or the schema is created as it goes through and then executes that script and close and now you can see these tables are actually created
41:23
so you can even go to your sql editor and then you can start querying your um you can start querying your tables there you go so you query came back of course there is no data in it and you can now start using your uh database so we actually successfully created the basic schema or design the data model for this
41:59
e-commerce website a table creation or a create table command starts with create table keyword followed by the name of the table and followed by parentheses so within the parenthesis the spare of paranthesis you have all these column names followed by the column data types and followed by the constraints and you can also use this auto increment keyword if you want your column value to be incremented automatically as you load values to the as you load
42:34
records to this table and after the column definition you have uh the option of specifying the keys like primary keys unique keys foreign keys and so on you can also specify the storage engine type as part of your table creation and this is a very simple table you can also have partition tables subpartition tables you can have compressed tables encrypted tables and all these things require special keywords to be used in your
43:05
table definition and please check my sql documentation if you want more details about the syntax as mentioned i'm using just integer watcher data types the mysql documentation shows like all these different data types like numeric date and time data types string data types like the ones i'm using and json spatial so these are all available in my sql for you to use so let's go ahead
43:35
and create this table and before creating the table i want to run this drop command just to make sure the table doesn't exist and i'm going to be creating that table and let's see if the table has been created yes the table has been created successfully i'm going to be running a select star from the table name to see if i can successfully query from this table as well and then it returns a basically it doesn't return anything that means no data exists in this table and that's how
44:06
you create a table using create table syntax finally there's actually a default keyword which helps you specify default values for a certain or for your columns so if you don't specify a value for this quantity column in your insert statements or when you're loading through procedures loading data through procedures it will automatically take this default value that's pretty much it and i'll see you guys in my next my
44:36
sequel sequel session so i just did a describe on the table that i created and it has product id product name product type price and quantity and you can see that uh product id is also an auto incrementing column right now there is no data in it a typical insert statement looks like this it's got the insert into keyword in the table name a bunch of columns within parenthesis the ones that you want to populate
45:09
followed by the values keyword and followed by the actual column values if you can realize i haven't actually specified the product id value because it's an auto incrementing column so let's go ahead and execute this and the insert statement goes through so let me also run the select statement as you can see the product id table has taken the value 1 and that's happened automatically i didn't supply the value 1. so i'll go ahead and commit the change and then let's
45:39
actually move on to the second variation so this time i'm going to specify a value for product id nothing else is different so just want to show you that it is possible so it goes through i'm gonna commit and then let's do a select to make sure the value has been inserted so let's go to this third variation of this insert statement so it's going to be pretty much the same except actually i'm going to just uh jump some values and then insert the value 10 for this product id column i'll go
46:12
ahead and do that it goes through a commit and uh select again then you can see that that is also fine so yeah so that worked so you can actually jump a few values let's actually look at the next variation again i'm going to insert a record into this table with no product id uh specified exclusively or explicitly the product id column is missing over here and i'm going to run the insert
46:42
statement and commit and then i'm going to run the select statement so i just wanted to show you that wherever the latest value is for this auto incrementing column i inserted the value 10 for product id last time and then the next time i do an insert this auto increment uh kicks in and then you know increases you know increases this value from 10 to 11. so it picks up from the value that was inserted last time and i'm gonna just take
47:13
another insert statement and this time it's actually insert into the table name and instead of the values keyword specifying the column names values etc we actually select from a different table basically if the products three table is exactly matching the structure of products one table then we can do even a select uh star from if the columns don't match exactly as in like
47:43
products one has a different set of columns and product three has a different set of columns then we need to make sure that we actually select the columns and then for example this product id from products3 maps to this product id in products1 and product name from products3 maps to product1 in i'm sorry product name and product one and so on let's go ahead and run this and see what happens and then that goes through and then if i
48:14
now select the products one table you can see like like all these rows are inserted properly and basically the products one table is populated and then we got all the data from products3 table so this time i want to show you the insert statement again this is kind of like a bulk insert or a multiple insert just combined into one statement you can see the insert into uh clause is specified only once but in the values
48:46
part in this clause actually we have two rows specified at the same time so we can even use such a syntax and a commit and then do a select all good so these are a few variations of insert statement i hope you understood how this works i'll see you guys in the next session hey my sequel learners so in this session actually i'm going to be talking about update and delete statements
49:19
so as usual i'm going to be using my ecom store schema and i will be using my products table to do this demo so just quickly if we select products table there's two rows right now so the first update uh is just to show you the the syntax of updates so you have the update keyword followed by the table name followed by set keyword and then you can have as many columns as your table uh contains uh but in this case i've got only one
49:49
column and i can just run this update so let's actually add one more clause to it uh which is the where clause and this is to just update the rows that you really want to update so we will be updating only the rows with product id equals one so let's go ahead and do that and then i'll just commit and select from products quantity has gone up by 50 it went from 299 to 349. now one more thing to realize is actually like you can you know you can specify literal values
50:20
uh when you are updating you know that happens all the time or you can also like specify formula you can have sql functions like replace substring length and so on you know you can look at my sql documentation to see what kind of functions are available in this update like i just wanted to show you the syntax basically to let you know that like you can have multiple uh you can update multiple rows at at once uh in this case i put like the values one two and three you know you don't have to do
50:51
one row at a time or anything so when you use the in keyword then you can say product id or whatever column in and then a bunch of values to select the rows that you want and there are other ways to do it but the point being you can update multiple records at once and another interesting usage is uh using the case statement you know you can let's say you have a bunch of update statements uh one for product id equals one another for product id equals two and another for uh the other product id
51:23
values and you can combine all that into one update statement using a case when then end the clause or keyword basically in this case actually for product id equals one i want to increment the quantity by 50 and product id equals 2 i want to increment the quantity by 100 and so on so then i've got this a similar where clause that similar to the one that i showed you before i'm going to run this you can see the columns are getting incremented i'm not going to go back and check i'm pretty sure that it's done the right thing so the
51:54
next one is basically when you want to delete records from a table or purge data from a table then you can just use a simple delete statement and if you are wanting to delete a particular row again similar to the update you can use aware class to actually like narrow down uh the data that you want to delete this particular statement which is delete from a table name and then where column name equals at the column value and you can have multiple filters in here so here i don't have the
52:25
row number three i think i've deleted it already all right so let's keep going products3 table i just wanted to show you it contains a lot more data than my other table uh you can see it contains uh data about 5849 rows i wanted to also show you this particular parameter mysql configuration parameter to basically enable and disable save updates so let's say like if your delete statement or update statement is not
52:56
using a primary key column in the where clause you know then basically if you enable this particular parameter uh let's say by setting this one and then if you run your delete you will get like an error code one one seven five and mean and then it says you're using safe update mode etc etc it's not leading you to run this kind of delete statements because it could be uh it could cause bad performance so if i disable the same thing and then if i run the delete statement
53:27
and then uh just trying to select again then it should go through because now the parameter is disabled and uh two more things one is actually like if you have a huge table and you want to delete only a few rows at a time then you can use the limit keyword to limit the number of records that are deleted uh by this statement you know in this case like i wanted to delete only 10 rows let's go ahead and do that and it should just work just fine and then if i do a select you will see
53:59
the difference in the row count actually now it's like five eight three nine before it was five eight four nine so that's how the limit uh clause helps you also in the limit flaws you can all specify the order by flaws uh it basically sorts the data by these columns like first by quantity then by product id then it deletes the top 10 or 100 or whatever value you put here actually so let's go ahead and do it and then select again yeah 2070
54:28
is gone so the top 10 rows are gone and yeah that's pretty much it actually those are all a few variations of update and delete statements and of course there's lots of tangents we can get into but i will leave that task uh to you and i hope it was useful and if you have any questions let me know in the comments i'll sh i'll see you guys in the next session hey my sql learners so in this session we'll look at select statements not just the syntax but also like some
55:01
ways you can actually like improve the performance of your queries i'm going to be using the schema called ecom store to explain about this a select statement in its most simple form will look like this so you have to select and from keywords and then after the select you specify the select list which is the columns that you want to select if you specify a star or as trick so that actually selects all the column columns from this table and then after the from keyword you specify the table names where you want
55:32
to select the data from so if i do a select star from products underscore 3 it's going to return all the data from products underscore 3 table but do remember that anytime you are using a star after the select or in the select list you are basically querying all the columns in this table you don't need to query all the columns in the table in most other cases so you only like specify the columns that you need to query so in this next query let's go line by line and see
56:03
what changes that have done to this query to make it better so let's say i want to select only these columns that's why i specified only these columns in the select list in the from clause i have specified products underscore three table very often you'll be selecting from multiple tables you need to join the tables and then retrieve useful data out of it and in the where clause you specify all the filters all the conditions based on which your data will be filtered out so here
56:33
i am including only the data which have quantity less than 25 so this way i'm able to actually filter most of the data out of this table this is very useful in minimizing the amount of data that you retrieve from the database hence your queries are going to be fast this order by is basically going to sort the data that is retrieved based on the columns that we specify here so here i'm just like ordering by product name and of course like when you're sorting data especially when you're sorting a lot of data
57:05
the operation can be expensive unless your sort buffer size that is actually the memory area where the sorts happen unless it is sized properly the operation can be really slow so you need to pay attention to that configuration as well and i have this other query which just goes to show you that like this is a very simple select statement again in this select actually i have only the select keyword and a function i'm using the now function but there are several other sql functions that you can
57:35
use in this query for example i can use the database function to return the database that i am actually connected to and as you can see i am actually able to uh invoke multiple functions in the same query so that's pretty much it i'll see you guys in the next session in this session i will be teaching about sql joins let's dive straight into the demo i will be using a schema called ecom store and i'm creating a table first
58:07
called t1 with one column the column name is c1 and i'm inserting these two values in this table one and two i'm creating another table called t2 with the column called c1 i'm inserting these two values again into table t2 one and three so it's one and two here and one and three here i'm going to go ahead and run a commit to make my changes permanent so i'm going to be just querying these two tables just to show you
58:37
the records t1 has one and two t2 as one and three of course a join is an operation that joins two tables and we have all these different types of joints will go one by one and understand what they are so this is the syntax so select in a column list that you're selecting so we're joining t1 and t2 and then we're specifying the kind of join that we are making and then we also have this on keyword and then comes the
59:08
the condition on which the table is joined so i'm going to go ahead and run this query and see what happens as you can see this query this inner join has returned the value one so that means actually so it returns the values that exist in both the tables that match so that's what inner join does so let's just change it to a left join go ahead and run it now the left join is going to return all the values from your left table which is t1 so p1 has
59:40
values 1 and 2 are the rows 1 and 2 and then t2 it's going to return only the matching values and then for this value which only exists in table t1 it's going to return a null and then i'm going to change it to right join and as you might have guessed it's going to return all the values from table t2 in the places where there is no matching value it's going to return a null so let's see if that happens that's what we expected so we got all the rows from
01:00:11
t2 and then for 3 there is no matching value in t1 you know that position has null value now we'll jump quickly to a union and then we'll come back to a full join a union is basically gonna look like this so two queries and then in between we have the union keyword let's see what it returns you can see that it's written one two and three so that's actually the the rows from both the tables but it's kind of like combined the data and then smooshes them
01:00:42
together and then you have one two and three and then let's run the same query with a slight difference we'll put union all and then we'll see what happens that's written one two and one three so that's returned all the data from both the tables but except this time we have duplicate values a union gets rid of all the duplicate values it's almost like a set where you have a unique set of data a union all returns all the values including duplicate data
01:01:12
jumping back to full join we don't have a full join keyword so rather we do full join this way in my sql so basically you have the a similar query where you're joining t1 and t2 and left join first on this this one column that we have and then you have another query again joining t1 and t2 on this just one column but then we are doing a union of these two and that's going to return the data from both the tables
01:01:42
we have one two these two are matching then for two there is no matching value so it returns a null for three there is no matching value in t1 it returns a null over here so this is a full join that's basically all the joins all different types of joins that you can do in my sequel i hope this example was clear and i'll see you guys in my next session all right my sequel learners in this section we're going to learn about locks more specifically i want to talk about the
01:02:14
isolation levels actually so the first thing is just see what i have here i have like two terminal sessions one is in black the other one is in slide maroon color so i am actually going to log into the database as root user and i'm going to do the same thing over here there you go i am logged into my sql database so i have a little script here
01:02:44
to create a dummy table called t1 okay so let me show you the sql script so at this point actually you might not understand the sql syntax and so on but then let me explain uh briefly first thing i'm doing is setting auto commit to zero or commit is basically a command that you use to save your work basically the data changes that you're doing
01:03:14
uh is permanently stored in the database when you issue a commit command in mysql you have this variable called auto commit which is turned on by default meaning all your commands will be automatically committed if you don't turn this off i want to have more control over what i'm doing here so basically i am doing an auto commit disable first so and then i'm starting a
01:03:46
transaction and uh just to be safe i'm dropping this table if i had already created it uh so this table doesn't exist so it says unknown table and the next thing is i'm creating a table called t1 in ecom store schema and then the column name is c1 and the data type is int and it's a primary key so and then i'm actually inserting a value just one row into this table
01:04:16
called p1 right the one that we just created and i issue a commit command alter alternative to commit is a rollback command so which basically rolls back or rewards the changes that you just done in that session so if i just do a select star from this table then i'm going to see this value so which is fine so far so this is pretty straightforward so far
01:04:47
we haven't talked about the isolation levels so what i mean by isolation level is when multiple sessions are trying to modify or access the same data then you need locking mechanism to make sure the data is not corrupted or the database is behaving in a way that you expect to see how you actually set isolation levels and this is the command
01:05:17
so this is the other session i had open show session variables like isolation so that shows that the transaction isolation level is set to read committed right so this is one of the possible options actually so this is read committed and you have read uncommitted and you have repetitive read or repeatable read uh and then you have a serializable uh value actually so let's go one by one
01:05:48
right in this session i already started a transaction so i'm going to actually try to update this value using an update command so basically i am updating the same table and i'm updating this column to 2 where the column value is currently 1 right so i'm going to do that the order commit is turned off so it's not committed yet i'm going to start a transaction over here and let me run a query against
01:06:20
the same table and just copy and paste the table name don't want to type it okay so we see the value 1 which is the previous value and if i ran the same query over here in this session i see uh the value 2 because this is the session where we are modifying the data right so and i can see the changes before committing in the same session here actually since the the value of this transaction
01:06:52
isolation or the isolation level is set to read committed it is possible only to read the committed data in other words when multiple sessions are accessing the same data in this case this column right here from this table apart from the session that is actually modifying the data the other sessions can only see committed data any data that is committed just before this select is executed so i'm going to go
01:07:24
here and run a commit and come back over here and run a select so now you see the latest data because that commit happened before i ran this query now let's talk about read uncommitted isolation setting actually freshly log in again because these things can get tricky so every time i want to just recreate the tables to remove any confusion so let's actually log in again okay in here and
01:07:57
i'm actually going to execute the same script that i showed you before so just disabling order commit starting a transaction dropping this table of recreating it inserting this value and then running a comment so now here what we could do is go ahead and update this value to 2 but remember i haven't committed this data yet let's go to this session and here go ahead and change the setting
01:08:29
to the isolation setting to read uncommitted because by default it is always set to read committed actually right so you can see that here so and this is a session level setting and you can also change it at global level but for the purpose of this demo we just need to change it at the session level so session level isolation initially read committed then i ran the
01:09:02
set session transaction isolation level read uncommitted and then checking the value again now it's changed to read uncommitted if i run a select star from this table then i get the value 2 and if you remember that i i only updated the value from 1 to 2 and you can already see this data even though it is not committed over here
01:09:32
so that is how read uncommitted works so there is not much locking going on here because database is now letting the sessions do dirty reads because one session is able to read in other sessions changes even before the commits happen actually right so those are dirty reads yes actually so let's go on to the next one so we have seen read committed and read uncommitted so far so now let's move on to
01:10:03
repeatable treats actually right so exit so here i am going to just commit and i'm going to rerun my initial script just to clear the table so drop table and then recreate it insert value one again and then comment so now the table is back to how it looked before so here let me log in again so this is repeatable read setting
01:10:34
right so remember that the default value for this isolation setting is always read committed so if i change it to repeatable read right and then check the value again then you can see this so and again remember or show variables is the command to check the current value and then set is the command to set uh the configuration right so i will put
01:11:06
all these commands in like a git github repo file then you can actually grab the commands from there and then you can try them yourself basically i'm changing the i'm changing the setting from read committed to repeatable read right so i'm going to just start a new transaction over here in here i'm going to update this value to 2 and over here i'm going to run the select query
01:11:37
that we saw before just selecting everything from this table and you see that the value is currently one and that makes sense so let me go ahead and run commit and if i ran the same query again i see the value one and this is the same as the value that was read before even though the data was changed by this other session and then committed within this
01:12:08
transaction the data that we are seeing is the same in in other terms basically we are we are reading the same data or the the reads are being repeated right so that is the third setting and the last one is the most strict locking configuration so which is called serializable so i'm going to as usual i'm going to drop the table and
01:12:40
then just recreate them recreated inserted value 1 again and then i commit so here i'm going to log in again and uh as usual the default setting is read committed right so let's check that first just to show you and then i'm gonna change it to serial serializable so what this means is basically i'm going to start a transaction so on
01:13:10
the first session i'm going to run an update basically changing the value from 1 to 2. and here i'm going to start a transaction and i'm going to run a query on that table right and now this query even though it's just a select a select is just a read it's not updating it's not deleting or doing anything it's just a read it is waiting because
01:13:39
uh the update is basically updating this data and then it's not database mysql database is not even letting this read or the select query from the other session to see the data so this is the most strict setting actually so if i do a commit over here then on this other session you will see that the query has written and it's seeing the latest value right so if i go ahead
01:14:10
and run another select of course it's returning the same thing but if i try to update this value from 2 to 3 another update that is basically going to wait on the select basically this transaction that is running right now because the select again select is just a read it is just reading the data but still it is locking that row in the database and it's not letting
01:14:41
any updates or modifications to that data and then you can see that the update even failed because it waited for some time and then the timeout value exceeded so we don't have to go into those details but i'm going to try updating now and here i am just going to exit out of this session which will release all the locks and that will help the update to go through and then i can commit and exit as well and how you hope it was clear to you guys
01:15:12
and if you have any questions please put it in the comments and reach out to me somehow i know you can figure out i'll see you guys in the next section hey my sequel learners so welcome back to this new section of my my sequel tutorial so in this video or in this section we're going to talk about locks so what are these logs right so let's actually approach this kind of like logically so if you have a database and if you are the only person
01:15:45
working in this database then you basically need not worry about anything right you know what you are doing so you will insert data delete update data uh the way you want and there is no one else trying to intervene or interrupt your work but unfortunately that's not the case in today's world if you think of a busy ecommerce database like
01:16:15
amazon then then there's like a lot going on on the on those websites there's like multiple people browsing there's like a lot of people buying stuff uh there's the people who are selling stuff on on these websites they're updating data relevant to their products so that is basically concurrency right so you have many users trying to do something on this website
01:16:47
at the same time so how do you manage this concurrency that's why we need logs so if i let everyone work on the same data at the same time then there's going to be a lot of confusion and we might end up losing some data so let me actually show you a simple example of how that happens so i have a table a product table so if you've been following my tutorial
01:17:20
thus far we talked about this table called products so where we store all the product information right so now there's a couple of records over here and let's say that we have a seller and a buyer who are working on these records especially like this particular record the first one which is a book and the book's price is this and the quantity
01:17:50
i think we didn't have quantity when we talked about it in my previous sections but then i added quantity here so there's this quantity column and there's a there's a seller and buying buyer interested in this record let's look at this right so we have sort of like a time sequence here so what the seller of this particular product is trying to do is he's trying to update the quantity of
01:18:21
this product at 9 1 he is adding 60 more quantity to that product which is you know 40 plus 60 which 100 and that's what we have over here so then a buyer comes and he looks at the quantity and then he basically wants to order two of these books that's hundred minus ninety a hundred minus two
01:18:50
that's 98 and then you have the quantity 98 over here so this happened in a sequence so but we are worried about concurrency right concurrency is like when things happen at the same time but what if okay first the seller comes and then he reads the quantity of this item initially it was 40 and then buyer comes and he also sees that the quantity is 40
01:19:22
right and at 901 so the first two operations happen at the same time at 901 seller comes and he says i want to update i want to add 60 more quantity like meaning i have 60 more books of this title but then buyer comes and he says okay i'm buying two items uh two of these books so but the value that he saw before was 40. so 40 minus 2
01:19:56
is 38. so he updates the quantity to 38. so the seller updates it to 100 but then because of this previous lookup the quantity is updated to 38 due to which this whole thing this whole operation is lost and we end up with sort of like corrupted data for this quantity column so this is
01:20:27
a simple example of how concurrency when not managed well might cause issue data issues like this hey my sql learners so in this video we are going to look at basically how table locks works in the context of e-commerce database we created a simple database or schema called ecom store
01:21:01
and we created a bunch of tables and i used another dummy table to explain um transaction isolation levels so if you haven't seen my previous material go back and check it out and come back here but then yeah you have four tables four main tables and the main table that we are interested in is products table here and in the products table i inserted a couple of records these are dummy records so i don't have a front end or
01:21:33
application running over here so we're just looking at database right so what what's going to happen in this tutorial is um so we we're going to basically simulate a situation where a seller is trying to update the quantity of the book that he is selling on this website which is this first book actually the common path to uncommon success
01:22:04
and then the right now the quantity of this the quantity available you know for this book is 40 right so he wants to update this quantity to 100 and also we'll have a couple of more users or buyers basically one buyer is trying to buy the same book will have another buyer he'll he'll try to buy a different book
01:22:34
which is this book tiny habits and then the same buyer will also try to browse the website like of course like we are gonna you know have to imagine a little bit because i don't have a friend then to show you everything um so let's actually see how this goes so first of all um you know basics first actually let's actually turn off the auto commit um just so
01:23:04
just so actually we have more control over what's happening and let me do that in all the three sessions i have open and the first session is the seller session the second session is the buyer one session and the third session is the buyer two session basically so i'm going to turn off the auto commit which is basically a mechanism that commits automatically if it's enabled
01:23:35
and i don't want that so i'm disabling it so next is i want to show you the transaction isolation level and we talked about it in my previous session so right now it's a repeatable read and it's the same for all so we are going to change that to read committed because read committed as isolation is the right isolation level for
01:24:05
oltp databases so now well let's actually start with the first seller session so three sessions so the first seller session is gonna update the quantity of um this book that he's interested in or he's selling actually but but we are going to take this uh aggressive approach and lock the whole table right so let's say the application is
01:24:38
written in a way that it locks the whole products table for right and then the other session let's say buyer one the second session buyer one comes and he is gonna try to buy two books and how actually we're going to do that is by running an update so we are basically updating the products table and we are subtracting the quantity by 2 which means actually the we are
01:25:10
buying two books and which book is it you know the book where by the record where product id equals one right so if you remember the data product id one is this book let's go ahead and run this update in the second session and it's gonna obviously wait because the table itself has been locked for right by the seller session the buyer one session is waiting and let's go to the buyer
01:25:43
two session the buyer two is trying to buy a different book which book is it this other book which is tiny habits book where product id equals two and we're going to do that and of course even that is hanging or waiting and that is actually a little bit crazy isn't it so if uh just seller is trying to update the quantity of this
01:26:14
one record with this one book and everything is hanging and the buyer two who's trying to buy a different book he kind of gives up so he moves to a different session and instead of buying or trying to buy a book he just tries to browse uh the website which is a select query or a read query rate a select query which is also hanging so the
01:26:45
buyer 2 is getting frustrated right now so you can see how restricted this kind of uh sequence is so if someone's using table logs it's going to basically reduce the concurrency of the operations that can happen in this database so that's the main point here in this demo [Music] okay my sequel learners so
01:27:16
in this session we are going to take a brief look at row level logs in mysql i have three sessions i'm already connected to my e-commerce database my sql database and this is how the data looks now so we have a products table which holds um you know this data only two books now just dummy data that i created and this this is the price and you have the quantity column
01:27:48
showing you how many how much quantity is left for each of these books so the first session is a seller session the second session is a buyer session we can call this buyer one session and the third session is a buyer two session so this is the data and just for clarity actually i wanted to show you the transaction isolation setting which is read committed and
01:28:24
the auto commit is turned to uh turned off basically it's disabled so unless i commit explicitly uh my transactions will not be permanent so let's actually start with a seller uh he's going on the website or a portal that he has available to update the inventory of let's say the book one right or the product one which is
01:28:59
this book and so he's going to click some buttons which is going to translate to an update statement being executed in this database right so let's say he wants to increase the the number of books available in the inventory so that will mean quantity is going to be increasing incremented by 50 so that's the update
01:29:29
statement and he's going to run that update and we can look at the buyer one session let's say buyer one is trying to buy the same book and and then so he's going to go on the website and then click on buy now or whatever and that is going to translate into this update statement in the database
01:30:03
which is quantity equals quantity minus one so reducing the quantity by one meaning he's buying uh buying a book and of course there's going to be you know other statements updating other tables but then to keep it simple i'm just showing you the products table changes section so as you can see this is gonna wait because seller is updating
01:30:34
this particular row actually and that can be um seen using a query on data logs so if you're in the uh this query of course you can modify this query as per your needs but then if you query this you will see that there's bunch of sessions and there's
01:31:07
here's the lock mode column and then the table on which the database on which the logs are happening the table so it gives you a lot of details so so if you want to understand what's going on here so we have products table and then we have ix lock which is intention exclusive lock on the table itself meaning like a transaction is about to get an exclusive lock
01:31:40
and this is at the table level but don't get tricked by that there's also another row indicating there is a record level or a row level lock and and that is locking only this uh data equals one so if you remember our update statement we are using product id so and the data for which is one actually so product id equals one
01:32:12
so that's what we are seeing over here and if you see here this buyer session has actually timed out already so he's gonna attempt to buy again so that's how like you can actually look at the locking details in this table let's try let's say like buyer 2 comes in at this point and then he just tries to browse the the inventory on this e-commerce website so that would mean a select query a read
01:32:44
query and he's he's able to do this happily actually right so there is no problem so while the row logs are happening other sessions can read this table they can even look at the data for the same product but they they just cannot buy this book because that is being locked by the seller so again it timed out so at this point buyer 2 wants to buy a different book you know i'm not able to buy this book let me try buying
01:33:15
a different book that's going to translate to you know product id id equals 2 which is not being locked by the seller and then that update goes through and at this point let's say the seller has completed updating the inventory and of course if you look at the data now it's going to look different because this has been updated to 150 and of course this hasn't gone down
01:33:45
because buyer buyer one is still in the process of buying the book because the commit has not happened yet in the application and then if we look at the data again the data has gone down but the quantity has gone down and then buy a two let's say wants to buy the first book that buyer one wanted to buy at this point there are no no locks in
01:34:16
this table because everyone's committed and let's say buyer 2 is trying to buy this this book and then he goes through with the update and then comments and look at data and then the data is changing actually so this is how a row level lock basically
01:34:46
allows for high concurrency so only the rows which are locked by your transactions are not available for these other sessions to modify right so the other records which are not touched by your transactions are available for updating deleting etc and all of course you can add new books that means inserting new records in this table
01:35:19
so i just wanted to show you the difference between table level logs and row level logs so this session and my previous session will uh will be useful in understanding the difference thank you i'll see you in my next session in this session uh we're going to be talking about deadlocks and i just want to show you how deadlocks happen they do happen in uh in a busy ecommerce ottp uh
01:35:54
database often so it's good to know what they are so and it's gonna be a very uh short and sweet session so here we have a couple of sessions again so connecting a connected to the same database there's two sessions two different sessions so let's say that we are working with a product stable right so we have seen this table before in my
01:36:25
previous sessions basically this table has information about the products that are being sold on uh you know an e-commerce website so we have a couple of records over here you know we're going to first let's say you know a seller comes to actually update the quantity of this product basically let's say if he wants to increase the quantity by 25 for this first
01:36:56
book this is the command that he is you know that's going to be executed you know whatever buttons he's clicking will be translated to an update command like this right and uh let's say like a different person from the same company wants to update the price of this book not this book let's say we have the other book i'm just actually using the product id to you know update the the right product right so we have one session where seller one
01:37:27
is updating the quantity of this item we have another session where we are updating the uh price of this item and then if you see the price is incremented by two let's say two dollars and this is fine right so now we have row level locks so this guy is holding a row level lock on this row and this guy is holding a row level lock on this row so this is fine right so we are operating on two different records two different
01:37:59
locks independent of each other all good so now let's say on the same seller the second person who is updating uh the price wants to update the price of this other book too actually like he is actually increasing the price again by two dollars of this book the product id equals one which book this one right here
01:38:27
let's go ahead and try to increment the price by running this command you know he's waiting on waiting for the lock actually exclusive lock and that's not available because this seller has not committed actually is not committed so let's actually go back here and and this seller at the same time tries to update the price of or quantity of this book so two sessions are
01:38:59
fighting for pretty much the same resource you know we ended up in a deadlock situation so my sequel was smart enough to just kill the session otherwise we would have two sessions waiting for each other endlessly right so here you can see the error code that is thrown it says deadlock found when trying to get lock and try restarting the transaction so let's go ahead and query the the
01:39:32
products table and see how it looks you can see that this whole transaction was rolled back correct both the transactions were rolled back this even this one was rolled back so i think that lock was also killed so that's why this this one went through and if you can see the prices have increased by two dollars right because initially it was 1699 and 2039
01:40:03
and here 1899 and 20 to 39. okay so that's how it works okay this is a typical deadlock situation i hope this explanation was clear and i will see you guys in my next session all right my sql learners so in this session we are going to talk about clustered indexes so so clustered index is not a different index type as in like
01:40:35
you can uh you know directly clear create a clustered index yourself so it is a type of index that uh that my sequel kind of maintains in the you know behind the scenes actually so and also your table data the data that you insert into your tables or load into your tables are maintained in these indexes indexes only what i mean by that is so let's say this is a b
01:41:06
tree index right so this is a b tree index so you have my sql creating this b tree index as you load data into these tables and then you know in the leaf notes what you have is actually the data the data that you're loading into these tables right you know the clustering the sorting is based on the primary key that you define or you know in this table actually so if
01:41:37
you don't define a primary key mysql will automatically pick up uh a non-nullable index key what that means is so let's say that in fact actually let's jump straight into the example that i have prepared for you guys so so this is my my sequel workbench and you know i'll show you uh this table definition so this is called
01:42:08
products underscore one and it's basically a products table that is typically used in a e-commerce store and if you've been following my lessons this is what i've been using i just changed the name of the table for uh you know demonstrating this concept this clustering clustered index concept so you have all these like columns and i'm defining a primary key okay so let's just start by you know i'm just going to switch to a database
01:42:39
called ecom store or schema called e-com store i'm gonna drop you know these tables if they exist already by any chance um so the table doesn't exist which is okay so i'm gonna create uh this table which i just talked about called products and then this table has primary key uh uh in a primary key as product id so product id is sort of like an integer column so this is an auto increment right so you don't
01:43:13
even have to provide a value for this column actually when you load the data so you can just put all this information and load it and then we we are good mysql will automatically increment the value of this column actually so and then of course like i said like there is a isbn column which is over here sort of like book isbn information if you are you know if you remember your school days like there is this isbn number
01:43:44
attached with any book so something like that so some kind of isbn alpha numeric number so i'm going to call that like a a unique key or a unique constraint and let's go ahead and create this table and this constraint so that was successfully created and i'm going to create a a procedure uh which i can use to kind of like populate this table right so don't worry about the details of this
01:44:14
procedure this is something that i wrote to populate the table and then that is successfully created and change the delimiter back to a semicolon and then i'm going to call this procedure and which is going to throw some warnings which is okay with me as long as um as long as the data gets populated i'm fine so it's going to probably generate some you know load
01:44:45
some 6000 plus rows into this table so we'll see how much we get this is awesome so it's actually loading a lot of data it seems to be done so let's go ahead and commit the data and uh you know now actually i'm going to select the data in this table right um just select all the data and you will see that the data by default or the data is actually sorted based on the primary key which is product id
01:45:16
and you can see you know i haven't like specified any ordering so this is you know this is the the default ordering of data right and so basically your table data is sorted based on your clustered index which is primary key over here because you have the primary key in the table actually right so now the next thing is actually i'm going to create a similar table which is you know which i'm going to call it
01:45:46
products2 but in this case i'm going to basically not define a primary key i'm still going to have a unique key called again the same thing you know it's isbn it's a unique key and uh let's just give it a different name just so we have kind of like we have different names for different constraints so let's actually go ahead and create this table and so this table is created
01:46:20
i'm going to copy the data from the first table that you know where i loaded a lot of data so i'm going to copy the data from that table into this table right so which is very simple and then i'm going to commit right so that's about six four five five sixty four fifty five 455 number of rows inserted into this table and i'm going to select all the rows from this table and you can see
01:46:50
that now the data is not sorted uh by product id rather it is sorted by this isbn it is sorting based on first character first and then initially the first and second characters are the same then 0 1 0 2 and that keeps going 0 5 and then 0 9 0 a b c d f g h and then having after the zeros you know c 1 so it is basically
01:47:22
sorting data based on isbn and why isbn because because of the absence of primary key it's going to choose this isbn column as uh or this it's going to choose this non-nullable unique index key which is based on isbn column right so it's sorting based on this but this is actually a terrible terrible idea because if you're generating random alphanumeric string
01:47:52
for isbn you know then you're not going to be generating the string in sort of like an ascending order or in any type of order actually so in that case actually you know when you're as you're inserting data into this table this b3 is going to be created behind the scenes and then my sequel like whatever program is creating or maintaining this data structure behind the scenes has to work really really hard to manage this b3
01:48:24
index actually right that's why this is a terrible idea to have like a uuid or a some kind of alphanumeric string as a primary key actually or in the absence of primary key well mysql is going to use this this key for clustering and again it is very bad so keep that in mind when you're creating tables actually right so finally what i'm going to do is create another table uh called products3 and
01:48:55
before that i'm going to show you the output of this query which is basically going to come up uh empty or you know no no wrote rows written all i'm doing is actually checking whether this index the index with name gen clust index is there in this database actually and then i'm checking the inner db tables and information schema i'm joining in odb tables and nodb indexes and checking whether this
01:49:25
index indeed exists right so it doesn't exist which is where the this uh this query returned no rows and i'm going to create this table and this time i'm not even going to create the create a unique key and i'm going to make all these columns as like nullable columns you know so i just want to show you what happens when you have a scenario uh where you're creating a table with all nullable columns and no primary key index no unique not nullable index
01:49:57
and you know and then i'm going to insert data into this table again 6400 plus rows inserted commit and then i'm going to select from this product 3 table right now and when the data comes up you can see that there is still some ordering that's happening and you know we don't have any of these options primary key or not nullable unique key available then how is
01:50:29
my sequel able to sort data what is it using so it actually uses a hidden um hidden key actually right a hidden primary key so if you run the same query again this query you can see that this index has been created on products three table which is maintained internally by my sequel for just the purpose of clustering this table actually okay so that's a lot of information i
01:51:02
hope you found this useful and i will see you guys in my next video hello my sequel learners so in this session i want to teach you the basics of using explain or explain plan in my sequel all right so now let's just let me just show you the table that i'm going to be working with i'm going to be working with a table called products underscore one and it's got some na in a product name product type price and if the product is
01:51:36
a book it will have an isbn number attached with it uh and then there's a quantity column so these are some basic columns that you would see in an e-commerce online store so let's get started by just looking at the indexes of this table so this basically has two indexes one is a primary key index uh which is on the product id
01:52:06
and the other one is uh an index on the isbn column and this is a unique index actually so let's get started by picking a simple query that we are gonna uh kind of like optimize using explain so the the query that i'm gonna be using is this so i'm going to be selecting isbn from this products underscore one table where
01:52:36
product name has cat in it so the product name is cat okay so and before i run this query i'm going to look at the explain plan of it and i'm going gonna put a slash g at the end so i get the output in the in a readable format so first of all uh it gives this output right and select is just one straights simple select that's
01:53:17
what this is showing but the main thing is we are working with or this particular row is referring to this table and apart from that actually you have all these columns and then they are all null right now like they don't make much sense apart from this so this is a typed column and all means that it is doing a full table scan uh basically my sql is doing a full table scan it scanning the whole
01:53:47
table and how many rows is that it's these many rows and we are using a filter over here it gets all those rows and then it filters the output and basically a you know there's about uh 600 uh rose with product name equals cat right so the filtered percentage is like 10 basically and then there is some extra information
01:54:19
let's go ahead and create an index on this table create index called you know we can give an arbitrary name and and i'm going to create it on products1 table and the column is product name of course this is the column on which i'm creating the index actually let's just go ahead and run the explain again so this is the explained plan and that's how it looks
01:54:56
so basically you can see that the again it's pretty much the same kind of output but this time it is also showing some data for all these columns so first of all possible keys column shows like all the indexes that this query can use and and out of which like this is the key or index that it is uh you know it is going to use this particular execution is going to use
01:55:28
and this is the key length in bytes actually right the number of rows that is being scanned uh in this key just 589 and you know since this is index based we're not really filtering data rather we're just going to the index and getting the data so there is no filtering over there let's actually create another index which also includes isbn and
01:56:01
see like what happens actually i'm going to create the other index and give it a different name so let's go ahead and run the explain plan again so now again the possible keys are these two indexes but it still chooses to go with this particular index and the index key length is the same and then rows and etc etc so there's no
01:56:38
filtering that happened right because we're choosing an index so you might be wondering like you know why it's not using uh uh the covering index right so this is supposed to be the covering index and covering indexes are supposed to be better than normal non-clustered index or a secondary index so you can actually like use a format like json format to get
01:57:08
more information so how you can do that is by just specifying like format equals json and use the use that and so that's going to give you the output in json format and you can see that you know it gives you a little bit more information as in like the query cost you know this is how much it's going to cost for my sequel to execute this query and this
01:57:41
is you know a representation of the amount of work uh mysql has to do to run this query actually so the cost for this one is 76 90 right and then again it says these are the possible keys and used key is used keep hearts is product name which was not given over here and then there's a cost info which is
01:58:12
a split of where the cost is going so you can read my sequel documentation on all these fields you know you might be wondering why the covering index is not being used and we can actually force that index by using this use index syntax or use index keyword and then i'm going to put the index name that i want to force which is this one
01:58:46
and when i run it this run the explain plan this time it shows the cost of this one is going to be 109.27 uh you know in comparison to the previous explain plan where the cost is only 76 and this is why uh my sequel is going with this particular plan instead of this guy
01:59:15
okay i hope this session was useful

DOWNLOAD SUBTITLES: