Explaining MySQL Plans and Indexing Strategies

Before running any query, it's essential to understand how MySQL plans to execute the query. This can be done by using the `EXPLAIN` statement with the `FORMAT = JSON` option, which provides a detailed explanation of the query plan in a readable format.

The output of the `EXPLAIN` statement shows that the query is executing as a simple select on one column, which might seem straightforward. However, upon closer inspection, it becomes apparent that this column refers to a specific table, and all columns in this table are currently null, making them irrelevant apart from this particular column.

This indicates that MySQL is performing a full table scan of the entire table, scanning all rows before applying any filters. The number of rows being scanned is substantial, with approximately 600 rows matching the condition on the `product_name` column, which accounts for about 10% of the total rows in the table.

To optimize this query and reduce the number of rows being scanned, it's essential to create an index on the columns used in the filter. In this case, creating an index on the `product_name` column can significantly improve performance. By running the `EXPLAIN` statement again after creating the index, we see that MySQL is now able to use this index to filter the rows before scanning the entire table.

However, there's a catch: even with the index, the query plan doesn't immediately switch to using the covering index, which would be ideal. This is because the cost of using the covering index is higher than the original cost of performing a full table scan and applying filters. To overcome this issue, we can use the `USE INDEX` syntax to force MySQL to use a specific index.

When we run the `EXPLAIN` statement with the `USE INDEX` option and specify the name of the desired index, we see that the query plan changes dramatically. The cost of executing the query decreases significantly, from 7690 to 109.27. This highlights the importance of indexing strategies in optimizing MySQL queries.

By creating indexes on columns used in filters and using the `USE INDEX` syntax to force MySQL to use specific indices, we can improve performance and reduce the number of rows being scanned. While it may take some trial and error to find the optimal indexing strategy for a particular query, understanding how MySQL plans and executes queries is essential for writing efficient SQL code.

"WEBVTTKind: captionsLanguage: enMySQL is one of the most popular databases,learn how to use it in this course from asenior database engineer. Welcome to thisbasic MySQL course I want to start by appreciatingyou for trying to learn a new skill. Let meintroduce myself. My name is Barbara and Iwork for Salesforce as a senior database engineer,I have over 12 years of experience with avariety of databases Oracle being the mainone, I have experience working with companieslike Chase, PayPal, Wells, Fargo, StubHub,etc. Let me answer a few basic questions foryou first, and that is who, what and why.So who should take this course this courseis meant for database professionals who wantto expand their skill set. If you are a softwareengineer or a full stack developer and youwant to gain a deep understanding of MySQLdatabase, this course is for you. And if you'rea college student, or a computer science studentor a fresh graduate, this course will giveyou some knowledge on database internals.So why should you learn MySQL, MySQL is themost popular open source database and of course,Postgres. SQL is definitely up there. As companiesmove their data from on prem to cloud, theyusually like migrate to a cloud native database,or an open source database, like MySQL orPostgres sequel, in order to save costs. Solet's say that you are an Oracle Databaseexpert. If you gain knowledge on a databaselike MySQL, then you could help companiesmigrate their data from Oracle to MySQL, andthat can be really valuable. Now let's lookat what is being covered in this course. Now,first of all, by MySQL, I mean, the MySQLInnoDB storage engine throughout this course,which is used behind any commerce website,or a bank or a financial institution, andso on. And MySQL offers a variety of storageengines, my I Sam, in memory storage engine,or some popular storage engines, which areavailable, we are going to learn about MySQLInnoDB, I am not covering any other type ofstorage engines. Now, these are the topicsthat I'm going to cover in this course. Andplease note that this is a database administrationcourse. So it is 80% database administration.And for people who are completely new to databases,I have included SQL basics. So you'll be learningabout database installation, MySQL Workbench,database indexes, database logs, you'll alsolearn a little bit of performance tuning,that is SQL explain. So these are some interestingtopics that I'm going to cover. So what doyou exactly need to get started with thiscourse, you need a PC or a Mac. So if you'reusing a PC, then I recommend you to actuallylook into the worksheets or supplemental materialthat is attached in the description. If youhave a Mac laptop, then you are in the bestposition to learn this course. Because thenyou can just see what I'm typing. And youcan just type the same commands and just followalong from end to end. And most of all, thisis the main requirement, I want you to createan AWS account, that's correct and amaze onWeb Services account. So if you don't knowwhat I'm talking about, please do look intomy worksheet which can be found in the description,I've attached some resources, which will showyou how to create an AWS account, I will beusing an AWS EC two instance throughout thecourse. And I'll show you how to create one.But one main thing that I want you to rememberis after each study session of yours, youcan shut down your EC two instance. That wayyou don't have to pay any unnecessary cost.And please do remember that you don't haveto keep your EC two instance running 24 seven.So once you have created an AWS account, andsign in, you will land on this dashboard,or this page. And you can go to the servicesmenu right here. And then under compute, youcan choose easy to so over here on the lefthand side, you can choose instances. And thenhere we're going to create an instance whichwill be our lab environment. So click on launchinstance. And then let's choose an image forour instance. So I'm going to choose Red HatEnterprise Linux version 864 bit and my instancetype is going to be T two micro which is freetier eligible. And you need to choose an appropriatesubnet. If you just created an AWS account,you can just leave. Leave it whatever defaultsubnet that shows up for you. For me, I'mgoing to choose me Be a specific one. Andthen make sure to enable this auto assignpublic IP option. Because that will assigna public IP to your instance using which youcan SSH into your instance, from your laptop,and leave all the other settings as is. Andlet's allocate like 25 gigs, for the database,or for the entire instance. And you can justleave the rest ISIS, go to the next page read,you can create a tag for your instance. SoI'm gonna call it my SQL instance. One, goto the security group page. And then here,what's important is that you need to be ableto SSH into the instance. And you need to,you need to create proper firewall rules forthat anyone, basically, anyone in the worldcan log into this instance, via Port 22. Andthat's not secure at all. And I will takecare of this instance. Because once I finishedrecording, I usually remove it. So I knowhow 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 canreview your configuration, and then clickon launch. But if you want, you can createa new key pair, and then just give it somename. And then download it before you createthe instance. For me, I'm just going to choosean existing key pair. Maybe this one, andI say knowledge, maybe a different one. Allright, this one. And launch instance. Nowyour instance is getting created, it probablytake a couple of minutes to create this instance.Okay, that's how you create an EC two instance.And now that my instance is up and running,and I can see the public IP. Later, I willbe installing MySQL on this instance. So that'swhat I wanted to show you in this lesson.So what you're seeing is basically my sequeldocumentation showing all these differentinstallation guides, like Windows and Mac,and so on. So we are interested in Linux installationare basically MySQL installation on Linux.And there's a couple of guides actually. Sothis one is basically installing jannettekbinary, we're gonna skip that and go here.And even within installing MySQL, on Linux,there's a bunch of guides. So the recommendedmethod of installation is using RPM packagesfrom Oracle. But we're going to use this MySQLyum repository based installation. And it'sactually quite straightforward. So for thisinstallation, we need to go to MySQL command downloads. And we are downloading theCommunity Edition and go to yum repository.As you know, the instance that we createdhas Archie l eight, Red Hat, Enterprise Linuxeight running on it. And so we need to downloadthis rpm. But then we need to download theRPM on the instance itself, the one that wecreated. So let's actually log into the instance.So I'm going to use SSH, and we're going touse my private key. And logging is easy touser choose the default user and basicallyget the public IP of my instance login andwe switch to a root okay. So one thing thatwe need for downloading this RPM to this Linuxinstance is W get package. So let's go aheadand install that first. Alright, so now thatw gate is installed, we need to download theRPM that we just saw. So to get the link ofthis RPM, we need to go into this download.And we have to right click here and copy thelink. And if you're installing on a differentOSS, you need to click on the appropriatebutton. Okay, so we got the link, and let'sjust go ahead and paste that link over here,like w get and the link. And that commanddownloads this package, now we are going touse an RPM command to install this package.So this package, as I mentioned before, isgoing to add this MySQL yum repo to your localsystem repo list. With Red Hat EnterpriseLinux installation, you get like a MySQL moduleby default. So let's disable that one. Ifyou don't disable it, then this will interferewith our MySQL installation. So let's go aheadand disable it using this command. And don'tworry about writing these commands, I willput a link to my Git repo with all these commentsin the description. So all these have beendisabled. Now let's go ahead and install MySQLcommunity Server Edition using yum installMySQL community server. And let's put minusy in there just to go ahead and accept allthe prompts. And that's installing all thesepackages. Alright, so my SQL has been installed.Let's go ahead and start the MySQL databaseusing system CTL. Command. And let's checkthe status. So now, my SQL database is upand running. Okay, so the log file of thisMySQL database software is under var log.And then if you grip temp from this log file,you will get the temporary password for rootuser. And you can use that to log into theMySQL database. And how do you log in, youuse this command my sequel minus u, that'sgoing to be root and minus P is for passwordbased login. And then we're logging into MySQLdatabase. So let's use this password and seeif it logs in. And we are in. And if you runany command at this point, my sequel is goingto ask you to reset the password using alteruser statement. We can do this in a differentway. So there is a executable called MySQLadmin. And this is the command for it. MySQLadmin, minus you the user name and minus ppassword. We are going to reset the passwordof root user. And let's provide the currentpassword which is this temporary passwordfirst. And let's provide the new passwordNo. All right, the password has been accepted.Now let me try to log in with this new passwordusing the previous command my SQL minus uproved and minus p MySQL. Let me put the passwordI said just now we are in. So let's go aheadand run a simple show databases command whichshows all the default databases that comeas part of the installation. So one more thingthat we need to do to complete the installationis to load a timezone file or timezone tableas shown here. So if I do a select star, whichis basically a SQL query to read from thistable, you can see that the table is emptyright now. So let's exit out and run anothercommand. To load timezone related data. Sothis is the command. And let's go ahead andrun it. And I'm going to go ahead and putmy password, and that loads a bunch of data,you can ignore all these warnings. Let's goback to our MySQL database. So if you do asselect star, from MySQL dot timezone, again,it shows a bunch of data. So now, you're good.And that completes the MySQL database installation.Alright, folks, in this section, we are goingto talk about data modeling. Okay, so databasedesign, data modeling, schema design, theseare all interchangeable words, terms for databasedesign is an ongoing process. So you comeup with a basic design, when you sort of likecreate your application. And then as the application,you know, gets added new features, enhancements,improvements, you basically iterate over thisdesign, right, you keep adding new thingsto your design, and so on. So the first thingthat you do when you're doing database designor data modeling is understand business data.And then once you understand business data,you have to come up with logical design ofyour 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 null constraints,default values, foreign keys, these are allvarious things that you need to create. Whenyou when you come up with a logical designof your schema, once you actually have thisbasic table design or schema design, thenyou can look for data redundancy, that isbasically you see where your data is repetative.And then you start eliminating it by normalizingyour tables actually. And that's because dataredundancy causes data anomalies. What I meanby that is, when you have like multiple occurrencesof the same data, when you're, let's say,updating certain data, you have to updatein many places. And if you forget to updateeven one place, now you you have two versionsof the same data in your database. And thatcreates like data anomaly data inconsistencyis along the same lines actually. And allthat happens because of data redundancy. Sowhat we are looking at is basically a spreadsheetand the spreadsheet is basically a huge tablea large table, right? And, and what what weare going to do is basically design a tablefor a ecommerce website, an e commerce websiteis basically like an amazing zone, or E Bay,or whatever, like online business, like onlinee commerce website, like Alibaba, or whatever,right? Let's say like, you have only one tablein this database, right? And you start, asthe orders come in, through this website,you start putting data into this table, right?You have, you know, let's look at some ofthe things that you will record in this table,right, you obviously need like a count ofyour orders. So you might like numbering yourorders, and then how it is coming like, youknow, is it desktop or mobile? Or what productis it? Like, you know, here I have got likea couple of books, the book titles, and thenthe price of the products. And then who isthe customer customers details, payment details,delivery details, and so on. So these areall part of like, e commerce word, basically.Right? And you have one giant table. And ifyou look at the data here, right? So, youknow, here, I've got like a couple of customersbuying you know, sort of like two differentproduct products, right. So, and you can seethat the data has been repetitive. What Imean by that is like every time I buy thesame product, I have to repeat this data,like the first order that came in was throughthe web desktop website that was brought bybirth and then you can see all the detailsof this customer and all that details aboutthe product and the payment, payment detailsas well. Then the second order that came wasfrom a different person, but then it was,you know, the order was for the same productand you had to repeat the product information.Right. The third was from the previous customer.But then this time he bought bought a differentproduct, his information, the customer informationhas repeated actually. So there is a lot ofdata redundancy data has been repetative.So, this is basically a denormalized database,where you have only one table or a handfulof tables, we back all the information fromyour website, or your business into thesefew tables. Actually, this is a denormalizedversion of your database, let's actually golook at what else you could do. So what youcan do is basically, you can start with thisbasic denormalize table, and then you canstart taking out all the redundant informationout of your database or your table action,it first thing I did was I took out like thecustomer information, they put it in a separatetable, right. And I've got only customer detailshere. And I started like putting a ID numberfor each customer ID or customer number, whateveryou call it. Once I take out the customerinformation, I have the Orders table, theinitial table, I'm calling Orders table lookinglike this now, right? And you can see thatI've got like customer ID column over here.And what is this customer ID column? Yourguesses? Right? So this customer ID columnis the same as what you see over here. Right?So and why do I have that because I need away to relate these rows. As you can tell,like, you know, these are columns, these arerows, these rows, I need to be able to relateto a customer, right? If I take out customerinformation, then how can I relate? You know,this table and that table? It's through acommon column, or a bunch of columns. Actually,in this case, it's just one column. So customerID, right? I'm just like putting the ID numberover here. And what else actually we can takeout of this table. So this is one level ofnormalization. Right? So let's keep normalizingwhich is like take out the product details.Right? So product details are also repeating.So here, you don't feel that much pain, becausethere'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 informationand move it to a different table. And thenI have a product ID column just to number,like ID the products actually, and your Orderstable will look like this, then you take outthe payment information to a different table.And your Orders table will then look likethis. This is basically the process of goingfrom a denormalized schema or a database toa normalized database. When you have yourdata in a single table, then you don't needto do any joints. So you might ask actually,what are joints actually, when you're runningqueries, like using SQL, SQL is a languageright? A Structured Query Language, when you'rerunning commands in your database, you, youcan get all your data from this one tableif your table database is totally denormalized.Whereas if you have many tables, you needto sort of combine or join the stables andthen you have to get the data out. So thatis called joining the tables. So when youhave a denormalize database, you don't haveto do many joints. And that is kind of goodin a way because your database doesn't haveto think that much to get the data. You sayI want this data and this data is availablein this table. So it's just very straightforward.Whereas like in a in a normalized database,when you join many tables, then your databaseengine with which they call optimizer in,in Oracle or in most of the tape most of thedatabases. So this database engine has tothink more as then like, okay, which tableShould I scan first? And how should I filterthe data in this table. And then Okay, I takethe result set from that table and have tojoin with these other tables. So there isso much more thinking there's so much moreprocessing that has to happen on whicheverserver this database is running on. Right.And because of that, the performance willbe kind of flexible. Lower right and it willconsume a lot of resources and you have allthat happening at scale as in like many operationsare happening at the same time, then you basicallyhave slow performance actually, or at leastlower than what it would have been in a denormalizedatabase, but at the same time, we are removingso much data repetition or data redundancyis very low, because of that, the storageneeded in a normalized database is much lower.So, you cannot actually like generalize andsay a normalized database will always be slowor denormalize database will be fast, it isall like it depends actually you have to lookat the data and see how much repetition ishappening, etc, etc. So, but generally thisis how it goes, as you go through this designprocess, right, you know, see what we havedone actually, right. So, we have decided,the tables that we need, like you know, wehave orders, table products got the customersand payments, and we decide the column names.And then not just that for each table, youneed to decide actually what will be the primarykey. What I mean by that as a primary keyis a unique key and which cannot be null,actually, which is very important. So usingthis primary key, you should be able to identifyany record in this table, any row in thistable. For example, if I say here, the primarykey is order number, then I can, anytime ifI have an order number, then I can look upthis table, let's say order number equalstwo, I can just get this record out of mydatabase. And then you need to also have likesome unique keys actually, right. So uniquekeys is pretty much like primary key. Anda unique key can be now a primary key cannotbe null, as I mentioned. And then you canalso have indexes on your table. So indexesare ways to basically select your day datafaster. Let's say that I often search thistable based on a customer's email, then Ineed an index on customer email column, right,you need to decide that. And you need to decideabout which columns can be null. Right? Here,none of the columns can be null. Let's sayyou have another column called preferencea the customer preference as in what kindof shipping or what kind of or which phonenumber is preferred, or something like that.So that can be a null column, right? So youcan have none columns. Otherwise, you defineyour columns as not null. Let's say like,in your Orders table, you have this deliveredcolumn, when an order is basically createdwhen a customer buys a product on your website.Of course, it's not delivered immediately,at the time of order creation, the deliveredcolumn will always have no or n, a n value,right? All these things, all these decisionsthat we are making we're talking about ispart of schema design. And once you have allthis figured out, you can put the informationin your design tool Entity Relationship designtool. And in the next section, I will showyou how I do that on sequel workbench, mysequel world workbench, basically, you canactually then have a pictorial representationof 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 relationshipbetween the two tables, let's say you cansay oh, this table in this table, they haveone to many relationship, for example, eachcustomer can place many orders. So that isactually a one to many relationship, right.But one, you know, one order can be done byonly one customer. Right? So, so that kindof thing. So you have one to one relationship,one to many relationship, or many to manyrelationship between tables. Actually, theseare all part of data modeling. But you don'tneed to be worried so much about that, aslong as you have a clear idea of like whatdata is coming into your database. And alongthe way, you need to define like the datatype of your columns. Actually, that is veryimportant. Your names are going to be a wildchar. You know, phone numbers can be numbers,and then email is again like a watch chart.And your ID column or number columns are goingto be int or number. These are all some decisionsthat you would make you In a data modelingtask, actually, that's pretty much what Iwant to say about data modeling, then there'smuch more we can talk about it. And like atomicity,like you, you have all the address, sort oflike attributes packed into one column, we,we need to split that as well. So that iscalled atomicity. You can have address separately,city separately states separately, and, youknow, zip code separately, right. So thosekinds of things, there are nuances that makeyour database more and more efficient. And,of course, we're not going to go into a lotof details there. But this is the basic datamodeling that you need to understand. Andas I said before, in the next section, I willshow you how to take this and then input thaton my sequel workbench. quick recap of whatI did in the last section, I basically createda logical design of an e commerce website.So what you're looking at is a table thatI started with. It's a denormalize table,and we basically took this denormalized table,and we normalized it. As you can see, thereis four versions of this table, I'm callingthis table Orders table. So there is fourdifferent versions. And I with each iteration,I took out repeating data. So finally, welanded with four tables, apart from the originalOrders table. So now we have also customersproducts and payment. In this video, I'm goingto take this now all the stables and thenI'm going to take the structure, and I amgoing to create a logical design. Okay, solet's actually go to MySQL Workbench and Iam already connected to a database, what Iam going to do is go to File and go to newmodels. so here we can add a new ER diagram,an entity relationship diagram. And let'scall this database eecom. Store, somethinglike that. So let's go ahead and start creatingour tables. Now, I'm not going to be creatingall four tables that would take probably moretime or long, and I'm going to be creatinga couple of tables. And that should be enoughfor you to understand how we're doing this.So let's just start with the Customers table.So the customer table has five columns, thisis the icon for creating a new table, youcan drag and drop, or you can try to drawnow double click and then create a table calledcustomer and here we can start putting thetable the column names customer ID, and thenthis is going to be populated by your sequence.So sequence is a database object. And it'sgoing to be an integer. So we can leave itas is. And we can have it as a primary key,that's fine and a primary key has to be populated,it cannot be now. So that is automaticallyselected. The next one is customer name, wecould split that into first name, and thenwe can choose watch our and maybe give a littlebit more room as in the length of the nameand then last name, I can watch 100 and thenall these cannot be now so we can choose notnull constraint. So these are different constraintswhich are available. Let's go ahead with thenext one address again address. And if youremember I talked about atomicity. So youwant your columns to be you know, atomic in,in the sense that here, basically the wholeaddress is packed into one column. good practiceto actually split that into atomic columnsas an address separately, city separatelystate separately and then zip code separately.So we have all these, of course, none of thesecan be now and what else is there? So customerphone number, phone number is going to beall numbers. But then I want to make get 10numbers, of course not null and customer email.So I can just say email id 100 Okay, not null.So since I Id is the primary key here or customerID, I want to make sure that that we havea constraint to avoid repeating customer information.For example, if you have one customers datafor ID one, I don't want the same customer,customers data to repeat for a different ID,for example, Id two. So I'm gonna actuallymake email id unique for each record overhere. And then may or may be phone numberalso. So these are all unique key constraints,or unique constraints. That's it. So we havethe Customers table created. So let's go backand see what else we have. So let's now create,I would say product. And then you basicallydo the same thing, select that is for creatinga new table. And then now here, you can justdraw in this one, I'm going to call it product.And we want to go through the same processand then put the product column names in there.If you're wondering, this is the same customerID column that we added over here. And weare going to make that a foreign key in aminute. So let's go ahead and split that intomultiple columns. Because, again, everythingis packed into one column, which is not agood practice. So let's say let's call itcredit card number. If the customer is usingPayPal, then we need that email. So can useemail id over here. So this can be null ornot null based on what payment type is beingused. So that's okay, so expiration date isgoing to be a date column. So let's actuallychange that. So if you're not sure, you canhit that the drop down and then choose yourproper data type for each. The other thingthat I mentioned, which is basically aboutforeign key, this customer ID is the sameas what we added over here. So let's actuallymake that customer ID a foreign key. So wecan just call it customer ID foreign key one.And then the table that is going to be referencedis the customers. And the column is goingto be customer ID. And that's it. So you cansee that now we have connection or a relationbetween these two tables, I'm going to actuallyjust add the Orders table as well. I've createdthe Orders table as well, which is the maintable and I'm going to now create some foreignkeys for the orders. Everything is done. Ifyou want to create any indexes at this point,you can do that. So I guess we are done. Sowe are basically added four tables to ourlogical design these four tables and thenwe have created columns and then define theirdata types. And also we created the foreignkeys and of course primary key and uniquekey for each of the tables. And you can seethe foreign key relationship you know clearlyshowing here and that You know, that's whatyou would do to create a data model. Alright,so now actually, let's just go ahead and createa SQL script for this data model. So you goto database, and then do forward engineer.And then basically, you provide the databasedetails where you want to create this, thesetables are the schema. So this is, these aremy details continue, go to the next one, providethe password. Right now we are connected,I had to try the password two, three times.And this has basically created SQL scriptfor us to create the schema and the tableswith all the primary key unique key and foreignkey constraints. So what we can do is we canjust continue and then now the database orthe schema is created as it goes through andthen executes that script. And close. Andnow you can see the stables are actually created.So you can even go to your SQL editor, andthen you can start reading your can startquerying your tables. There you go. So youready came back, of course, there is no datain it. And you can now start using your database.So we actually successfully created the basicschema, or designed the data model for thise commerce website. Table creation or a CREATETABLE command starts with CREATE TABLE keywordfollowed by the name of the table and followedby parenthesis. So within the parenthesis,this pair of parentheses, you have all thesecolumn names, followed by the column datatypes, and followed by the constraints. Andyou can also use this auto increment keyword,if you want your column value to be incremented.Automatically, as you load values to the asyou load records to this table, and afterthe column definition, you have the optionof specifying the keys like primary keys,unique keys, foreign keys, and so on, youcan also specify the storage engine type aspart of your table creation. And this is avery simple table. You can also have partitiontables or partition tables that have compressedtables, encrypted tables, and all these thingsrequire special keywords to be used in yourtable definition. And please check my sequeldocumentation. If you want more details aboutthe syntax. As mentioned, I'm using just integerwatch char data types, the MySQL documentationshows like all these different data typeslike numeric, date, and time data types, stringdata types, like the ones I'm using, and JSONspatial. So these are all available in MySQLfor you to use. So let's go ahead and createthis table. And before creating the table,I want to run this drop command just to makesure the table doesn't exist. And I'm goingto be creating that table and see if the tablehas been created. Yes, the table has beencreated successfully, I'm going to be runninga select star from the table name to see ifI can successfully query from this table aswell. And then it returns. Basically, it doesn'treturn anything that means no data existsin the stable. And that's how you create atable using CREATE TABLE syntax. Finally,there is actually a default keyword whichhelps you specify default values for a certainor for your columns. So if you don't specifya value for this quantity column in your insertstatements, or when you're loading throughprocedures, loading data through procedures,it will automatically take this default value.That's pretty much it. And I'll see you guysin my next MySQL SQL session. So I just dida describe on the table that I created andit has product ID product name, product type,price and quantity. And you can see that productID is also an auto incrementing column rightnow there is no data in it a typical insertStatement looks like this, let's go to insertinto keyword and the table name a bunch ofcolumns within parenthesis, the ones thatyou want to populate, followed by the valueskeyword. And followed by the actual columnvalues. If you can realize I haven't actuallyspecified the product ID value because it'san auto incrementing column. So let's go aheadand execute this and insert statement goesthrough. So let me also run the select statement.As you can see, the product ID table has takenthe value one, and that's happened automaticallyident supply the value one, so I'll go aheadand commit the change. And then let's actuallymove 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 toshow you that it is possible. So it goes throughand then a commit. And then let's do a selectto make sure the value has been inserted.So let's go to this third variation of thisinsert statement. So it's going to be prettymuch the same, except Actually, I'm goingto just jump some values and then insert thevalue 10. For this product ID column, I'llgo ahead and do that. It goes through a commentand select again, then you can see that thatis also fine. So yeah, so that worked. Soyou can actually jump a few values. let'sactually look at the next variation. Again,I'm going to insert a record into the stable,with no product ID specified exclusively.Or explicitly, the product ID column is missingover here, and I'm going to run the insertstatement and commit. And then I'm going torun the select statement. So just wanted toshow you that wherever the latest value isfor this auto incrementing column, I insertedthe value 10 for product ID last time, andthen the next time I do an insert is autoincrement kicks in and then you know increases,increases this value from 10 to 11, rightpicks up from the value that was insertedlast time. And I'm gonna just take anotherinsert statement, and this time, it's actuallyinsert into the table name. And instead ofthe values keyword, specifying the columnnames, values, etc, we actually select froma different table. Basically, if the productsthree table is exactly matching the structureof products, one table, then we can do evena select star from if the columns don't matchexactly as in like products, one has a differentset of columns, and product three has a differentset of columns, then we need to make surethat we actually select the columns. And then,for example, this product ID from products,three maps to this product ID and productsone, and product name from products, threemaps to product one in I'm sorry, ProductName and product one, and so on. Let's goahead and run this and see what happens. Andthen that goes through. And then if I nowselect the products, one table, you can seelike take all these rows are inserted properly.And basically the products one table is populated.And then we got all the data from productsthree table. So this time I want to show youthe insert statement. Again, this is kindof like a bulk insert or multiple insert justcombined into one statement, you can see theinsert into clause is specified only oncebut in the values spark in this clause, actually,we have two rows specified at the same time.So we can even use such as syntax and a commit,and then do a select all good. So these area few variations of insert statement. I hopeyou understood how this works. I will seeyou guys in the next session. Hey, my sequellearners. So in this session, actually, I'mgoing to be talking about update and deletestatements. So as usual, I'm going to be usingmy eecom store schema. And I will be usingmy products table to do this demo. So justquickly, if we select products table, there'stwo rows right now. So the first update isjust to show you the syntax of update. Soyou have the update keyword followed by thetable name followed by set keyword. And thenyou can have as many columns as your tablecontains, but in this case, I've got onlyone column and I can just run this update.So let's actually add one more clause to it,which is the where clause and this is to justupdate the rows that Do you really want toupdate so we will be updating only the rowswith product ID equals one. So let's go aheadand do that. And then I'll just come in anddo a SELECT FROM products quantity has goneup by 50, it went from 299 to 349. Now onemore thing to realize is actually like youcan, you know, you can specify literal values,when you are updating, you know, that happensall the time. Or you can also like specifya formula, or you can have SQL functions likereplace substring, length, and so on, youknow, you can look at my SQL documentationto see what kind of functions are availablein this update, like, I just wanted to showyou the syntax, basically, to let you knowthat like you can have multiple, you can updatemultiple rows at once. In this case, I putlike, values one, two, and three, you know,you don't have to do one row at a time oranything. So when you use the end keyword,and you can say product ID or whatever, callthem in and then a bunch of values to selectthe rows that you want. And there are otherways to do it. But the point being, you canupdate multiple records at once. And anotherinteresting usage is using the case statement,you know, you can let's say you have a bunchof update statements, one for product ID equalsone another for product ID equals two andanother for the other product ID values. Andyou can combine all that into one UPDATE statementusing a case when then and clause or keywordbasically, in this case, actually, for productID equals one, I want to increment the quantityby 50. And product ID equals two, I want toincrement the quantity by 100, and so on.So then I've got this similar WHERE clauseare similar to the one that I showed you before,I'm going to run this you can see the columnsare getting incremental, I'm not going togo back and check I'm pretty sure that it'sdone the right thing. So the next one is basicallywhen you want to delete records from a tableor purge data from a table, then you can justuse a simple delete statement. And if youare wanting to delete a particular row, again,similar to the update, you can use a whereclause to actually like narrow down the datathat you want to delete this particular statement,which is delete from a table name, and thenwhere column name equals or the column value.And you can have multiple filters in here.So here I don't have the row number three,I think I deleted it already. Alright, solet's keep going products three table, I justwanted to show you it contains a lot moredata than my other table. You can see it containsdata about 5849 rows, I wanted to also showyou this particular parameter MySQL configurationparameter to basically enable and disablesafe updates. So let's say like if your deletestatement or UPDATE statement is not usinga primary key column in the where clause,you know, then basically if you enable thisparticular parameter, let's say by settingthis one, and then if you run your delete,you will get like an error code 1175 it mean,and then it says you're using safe updatemode, etc, etc. It's not leading you to therun this kind of add delete statements, becauseit could be a good cause bad performance.So if I disable the same thing, and then ifI run the Delete statement, and then justrun a select again, then it should go throughbecause now the parameter is disabled. Andtwo more things. One is actually like if youhave a huge table and you want to delete onlya few rows at a time, then you can use thelimit keyword to limit the number of recordsthat are deleted by the statement, you know,in this case, because I wanted to delete only10 rows, let's go ahead and do that. And itshould work just fine. And then if I do aselect, you'll see the difference in the rowcount actually, now it's like 5839, beforeit was fired four nights. So that's how theLIMIT clause helps you also in the limit floors,you can also specify the ORDER BY clause.It basically sorts the data by these columns,first by quantity, then by product ID, thenit deletes the top and or 100 or whatevervalue you put here, actually. So let's goahead and do it and then select again. Yeah,Kearney, seven D is gone. So the top 10 rowsare gone. And yeah, that's pretty much it.Actually, those are all a few variations ofupdate and delete statements. And of course,there's lots of tangents we can get into butI will leave that task to you. And I hopeit was useful. And if you have any questions,let me know in the comments. I'll see youguys in the next session. MySQL learners.So in this session, we'll look at SELECT statements,not just the syntax But also like some waysyou can actually like improve the performanceof your queries, I'm going to be using theschema called income store to explain aboutthis SELECT statement in its most simple formwill look like this. So you have the Selectand from keywords and then after the Select,you specify the Select list, which is thecolumns that you want to select. If you specifya star, or asterik, that actually selectsall the column columns from this table, andthen after the from keyword, you specify thetable names where you want to select the datafrom. So if I do a select star from productsunderscore three, it's going to return allthe data from products underscore three table.But do remember that anytime you are usinga star after the Select, or in the Selectlist, you're basically querying all the columnsin this table, you don't need to query allthe columns in the table in most of the cases,so you only like specify the columns thatyou need to query. So in this next query,let's go line by line and see what changesthat 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 columnsin the Select list. In the from clause, Ihave specified products underscore three tablevery often you will be selecting from multipletables, you need to join the tables and thenretrieve useful data out of it. And in thewhere clause, you specify all the filters,or the conditions based on which your datawill be filtered out. So here I am, includingonly the data which have quantity less than25. So this way, I'm able to actually filtermost of the data out of this table, this isvery useful in minimizing the amount of datathat you retrieve from the database. And yourqueries are going to be fast as ordered byis basically going to sort the data that isretrieved based on the columns that we specifyhere. So here, I'm just like ordering by productname. And of course, like when you're sortingdata, especially when you're sorting a lotof data, the operation can be expensive, unlessyour source buffer size, that is actuallythe memory area where the slots happen. Unlessit is sized properly, the operation can bereally slow. So you need to pay attentionto that configuration as well. And I havethis other query, which just goes to showyou that like this is a very simple SELECTstatement. Again, in this select, actually,I have only the Select keyword and a function,I'm using the now function. But there areseveral other SQL functions that you can usein the script, for example, I can use thedatabase function to return the database thatI am actually connected to. And as you cansee, I am actually able to invoke multiplefunctions in the same query. So that's prettymuch it. I'll see you guys in the next session.In this session, I will be teaching aboutSQL joints, let's dive straight into the demo,I will be using a schema called eecom store.And I'm creating a table first called T onewith one column, the column name is C one,and I'm inserting these two values in thistable. One and two, I'm creating another tablecalled T two with a column called C one andinserting these two values again, into tableD two, one and three. So it's one and twohere and one and three, here, we're goingto go ahead and run a commit to make my changespermanent. So I'm going to be just creatingthese two tables just to show you the records,D one has one and two, D two as one and three.Of course, a join is an operation that joinstwo tables. And we have all these differenttypes of joints, we'll go one by one and understandwhat they are. So this is the syntax, so selectan a column list that you're selecting. Sowe're joining T one and T two. And then we'respecifying the kind of join that we are making.And then we also have this on keyword. Andthen comes the condition on which the tableis joined. So I'm going to go ahead and runthis query and see what happens. As you cansee, this query, this inner join has returnedthe value one, so that means actually, soit returns the values that exist in both thetables that match. So that's what INNER JOINdoes. So let's just change it to a left tojoin, go ahead and run it. Now the left joinis gonna return all the values from your leftto table which is T one. So T one has valuesone and two are the rows one and two, andthen T two, it's going to return only thematching values. And then for this value,which only exists in table T one, it's goingto return and now and then I'm going to changeit to right join and as you might have guessedit's going to return all the way Use fromtable t to in the places where there is nomatching value, it's going to return a null.So let's see if that happens. That's whatwe expected. So we got all the rows from Ttwo, and then for three, there is no matchingvalue and T one, you know, that position hasno value. Now we'll jump quickly to a union.And then we'll come back to a full join, aunion is basically going to look like this.So two queries, and then in between, we havethe union keyword, let's see what it returns,you can see that it's written one, two, andthree. So that's actually the rows from boththe tables, but it's kind of like combinedthe data and then smashes them together. Andthen you have one, two, and three, and thenlet's run the same query with a slight difference.We'll put union all and then we'll see whathappens. That's written one, two, and one,three. So that's returned all the data fromboth the tables, but except this time, wehave duplicate values, Union gets rid of allthe duplicate values, it's almost like a setwhere you have a unique set of data, a unionall returns all the values, including duplicatedata. Jumping back to full join, we don'thave a full join keyword. So rather, we dofull join this way in my sequel. So basically,you have the similar query where you're joiningT one and T two, a left join first on this,there's one column that we have, and thenyou have another query, again, joining T oneand T two on this, just one column, but thenwe are doing a union of these two, and that'sgoing to return the data from both the tables,we have one, two, these two are matching,then for two, there is no matching value.So it returns and now for three, there isno matching value in T one, it returns a nullover here. So this is a full joint, that'sbasically all the joints, all different typesof joints that you can do in MySQL, I hopethis example was clear. And I'll see you guysin my next session. All right, my SQL learners.In this section, we're going to learn aboutlocks. More specifically, I want to talk aboutthe isolation level section. So the firstthing is just see what I have here, I havetwo terminal sessions. One is in black. Theother one is in slight maroon color. So I'mactually going to log into the database asthe root user. And I'm going to do the samething over here. There you go. I am loggedinto my SQL database. So I have a little scripthere to create a dummy table called T one.Okay, so let me show you the SQL script. Atthis point, actually, you might not understandthe SQL syntax and so on. But then let meexplain. Briefly, first thing I'm doing issetting auto commit to zero or commit is basicallya command that you use to save your work.Basically, the data changes that you're doingis permanently stored in the database. Whenyou issue a commit command, in my sequel,you have this variable called auto commit,which is turned on by default, meaning allyour commands will be automatically committed.If you don't turn this off, I want to havemore control over what I'm doing here. Sobasically, I am doing an auto commit, disablefirst, so and then I'm starting a transaction.And just to be safe, I'm dropping this tableif I had already created it. So this tabledoesn't exist. So it says unknown table. Andthe next thing is I'm creating a table calledT one in eecom store schema. And then thecolumn name is C one. And the data type isint and its primary key. So and then I'm actuallyinserting value, just one row into this tablecalled p one, right, the one that we justcreated. And I issue a commit command, alteror the alternative to commit is rollback command.So which basically rolls back rewards thechanges that you just done in that session.So if I just do a select star from the stable,then I'm going to see this value, so whichis fine so far. So this is pretty straightforward.So far, we haven't talked about the isolationlevels. So what I mean by isolation levelis when multiple sessions are trying to modifyor access the same data data, then you needlocking mechanism to make sure the data isnot corrupted, or the database is behavingin a way that you expect to see how you actuallyset isolation levels. And this is the command.So this is the other session I had openedshow session variables like isolation. Sothat shows like the transaction isolationlevel is set to read committed. Right. Sothis is one of the possible options action.So this is read committed, and you have readuncommitted, and you have repetative read,or repeatable read. And then you have a serializablevalue, actually, so let's go one by one, right.In this session, I already started a transaction.So I'm going to actually try to update thisvalue using an update command. So basically,I am updating the same table, and I'm updatingthis column to to where the column value iscurrently one, right. So I'm going to do thatthe auto commit is turned off. So it's notcommitted yet for the start a transactionover here. And let me run a query againstthe same table and just copy and paste thetable name, want to type it, okay, so we seethe value one, which is the previous value.And if I ran the same query over here, inthis session, I see the value two, becausethis is the session where we are modifyingthe data, right? So and I can see the changesbefore committing in the same session here.Actually, since the value of this transactionisolation, or the isolation level is set toread committed, it is possible only to readthe committed data. In other words, when multiplesessions are accessing the same data, in thiscase, this column right here from this table,apart from the session that is actually modifyingthe data, the other sessions can only seecommitted data, any data that is committedjust before this select is executed. So I'mgoing to go here and run a commit, and comeback over here and run a select. So now yousee the latest data because that commit happenedbefore I ran this query. Now let's talk aboutread uncommitted isolation, setting actuallyfreshly log in again, because these thingscan get tricky. So every time I want to justrecreate the tables to remove any confusion.So let's actually log in again, okay, in here,and I'm actually going to execute the samescript that I showed you before. So just disablingauto command, starting a transaction, droppingthe stable and recreating it, inserting thisvalue, and then running the command. So nowhere, what we could do is go ahead and updatethis value to two. But remember, I haven'tcommitted this data yet. Let's go to thissession. And here, go ahead and change thesetting to the isolation setting to read uncommitted,because by default, it is always set to readcommitted action. Right. So you can see thathere. 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 justneed to change it at the session level. Sosession level isolation initially read committed,then I ran the set session transaction, isolationlevel, read uncommitted, and then checkingthe value again. Now it's changed to readuncommitted, if I ran a select star from thistable, then I get the value two, and if youremember that I I only updated the value fromone to two. And you can already see this betaeven though it is not committed over here.So that is how read uncommitted works. Sothere is not much locking going on here. Becausedatabase is now letting the sessions do dirtyreads because one session is able to readand other sessions changes even before thecommits happen actually, right. So those aredirty reads. Yes, actually. So let's go onto the next one. So we have seen read, committedand read Committed so far. So now let's moveon to repeatable reads action. Right? So exit.So here I am going to just commit. And I'mgonna re rerun my initial script just to clearthe table. So drop table and then recreatedinsert, value one again, and then commit.So now, the table is back to how it lookedbefore. So here, let me log in again. So thisis repeatable read setting, right? So rememberthat the default value for this isolationsetting is always read committed. So if Ichange it to repeatable read, write, and thencheck the value again, then you can see this.So and again, remember, or show variablesis the command to check the current value.And then set is the command to set the configurationright, so I will put all these commands inlike a git GitHub repo file, then you canactually grab the commands from there, andthen you can try them yourself. Basically,I'm changing the I'm changing the settingfrom read committed to repeatable read, right,so I'm going to just start a new transactionover here in here, I'm going to update thisvalue to two. And over here, I'm going torun the Select query that we saw before justselecting everything from this table. Andyou see that the value is currently one. Andthat makes sense. So let me go ahead and runcommit. And if I ran the same query, again,I see the value one. And this is the sameas the value that was read before, even thoughthe data was changed by this other session.And then committed within this transaction,the data that we are seeing is the same inin other terms, basically, we are, we arereading the same data, or the reads are beingrepeated. Right. So that is the third setting.And the last one is the most strict lockingconfiguration. So which is called serializable.So I'm going to, as usual, I'm going to dropthe table and then just recreate them recreated,inserted value one again, they might comein. So here, we're going to log in again.And as usual, the default setting is readcommitted, right? So let's check that first,just to show you, and then I'm going to changeit to serial serializable. So what this meansis, basically, I'm going to start a transaction.So on the first session, I'm going to runan update, basically changing the value fromone to two. And here, I'm going to start atransaction, and I'm going to run a queryon that table. Right. And now this query,even though it's just a select, select isjust a read, it's not updating, it's not deletingor doing anything, it's just a read, it iswaiting, because the update is basically updatingthis data. And then it's not database, MySQLdatabases, not even letting this read or theSelect query from the other session to seethe data. So this is the most strict settingaction. So if I do a commit over here, thenon this other session, you will see that theGradius return and it's seeing the latestvalue, right. So if I go ahead and run anotherselect, of course, it's returning the samething. But if I try to update this value fromtwo to three, another update, that is basicallygoing to wait on the Select, basically thistransaction that is running right now becausethe Select again, select is just a read, itis just reading the data. But still it islocking that row in the database, and it'snot letting any updates or modifications tothat data. And then you can see that the updateeven failed because it waited for some timeand then the timeout value exceeded so wedon't have to go into those details. But I'mgoing to try updating now. And here. I'm justgoing to exit out of this session, which willrelease all the locks. And that will helpthe update to go through. And then I can commitand exit as well, and how you hope it wasclear to you guys. And if you have any questions,please put it in the comments and reach outto me somehow I know you can figure it out,as you guys are next section. Hey, MySQL learners.So welcome back to this new section of myMySQL tutorial. So in this video, or in thissection, we're going to talk about locks.So what are these locks? Right? So let's actuallyapproach this kind of like logically. So ifyou have a database, and if you're the onlyperson working in this database, then youbasically need not worry about anything, right?You know what you're doing. So you will insertdata, delete, or update data the way you want.And there is no one else trying to interveneor interrupt your work. But unfortunately,that's not the case. In today's world. Ifyou think of a busy ecommerce database likeAmazon, then then there's like, a lot goingon on the on those websites. There's likemultiple people browsing is like, a lot ofpeople buying stuff. There's the people whoare selling stuff on on these websites, they'reupdating data relevant to their products.So that is basically concurrency, right? Soyou have many users trying to do somethingon this website at the same time, so how doyou manage this concurrency, that's why weneed locks. So if I let everyone work on thesame data at the same time, then there's goingto be a lot of confusion. And we might endup losing some data. So let me actually showyou a simple example of how that happens.So I have a table, a product table. So ifyou've been following my tutorial, 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 recordsover here. And let's say that we have a sellerand a buyer who are working on these records,especially like this particular record, thefirst one, which is a book, and the books,prizes, this and the quantity, the thing wedidn't have quantity when we talked aboutit in my previous sections. But then I addedquantity here. So there's this quantity column.And there's a there's a seller and buyingbuyer interested in this record, let's lookat this, right, so we have sort of like atime sequence here. So what the seller ofthis particular product is trying to do ishe's trying to update the quantity of thisproduct at nine one, he is adding 60 morequantity to that product, which is you know,40 plus 60, which 100. And that's what wehave over here. So then a buyer comes andhe looks at the quantity. And then he basicallywants to order two or these books, that's100 minus 90 100 minus two, it's 98 and thenyou have the quantity 98 over here. So thishappened in a sequence. So but we are worriedabout concurrency, right? concurrency is likewhen things happen at the same time. But whatif Okay, first the seller comes and then hereads the quantity of this item. Initiallyit was 40 and then buyer comes and he alsosees that the quantity is 40. Right? And at901. So the first two operations happen atthe same time at nine or one seller comesin he says I want to update I want to add60 more quantity, like meaning I have 60 morebooks of this title, but then buyer comesand he says okay, I'm buying two items ortwo of these books. So but while you thathe saw before was 40. So 40 minus two is 38.So he updates the quantity 38. So the sellerupdates at 200. But then, because of thisprevious look up, the quantity is updatedto 38. due to which this whole thing, thiswhole operation is lost. And we end up withsort of like corrupted data for this quantitycolumn. So this is a simple example of howconcurrency when not managed well might causeissue data issues like this. MySQL learners.So in this video, we are going to look atbasically how table locks works. In the contextof e commerce database, we created a simpledatabase or schema called the column store.And we created a bunch of tables or used anotherdummy table to explain our transaction isolationlevels. So if you haven't seen my previousmaterial, go back and check it out. And comeback here. But then yeah, you have four tablesfor main tables. And the main table that weare interested in is products table here.And in the products table, I inserted a coupleof records. These are dummy records. So Idon't have a front end or application runningover here. So we're just looking at database,right. So what what's going to happen in thistutorial is, so we, we're going to basicallysimulate a situation where a seller is tryingto update the quantity of the book that heis selling on this website, which is thisfirst book actually, the common path to uncommonsuccess. And then the right now the quantityof this the quantity available. You know,for this book is 40, right? So he wants toupdate this quantity 200. And also, we'llhave a couple of more users, or buyers, basicallyone buyer is trying to buy the same book,we'll have another buyer Hill, who tried tobuy a different book, which is this book,tiny habits, and then the same buyer willalso try to browse the website, like of course,like, we are going to have to imagine a littlebit because I don't have a front end to showyou everything. So let's actually see howthis goes. So first of all, you know basicsfirst, actually, let's actually turn off theauto commit. Just so just so actually, wehave more control over what's happening. Andlet me do that in all the three sessions Ihave open and the first session is the sellersession. The second session is the buyer onesession. And the third session is the buyerto session, basically. So I'm going to turnoff the auto commit, which is basically amechanism that commits automatically if it'senabled. And I don't want that. So I'm disablingit. So next is I want to show you the transactionisolation level. And we talked about it inmy previous session. So right now it's a repeatableread. And it's the same for all. So we aregoing to change that to read committed, becauseread committed is isolation is the right isolationlevel for OLTP databases. So now let's actuallystart with the first seller session. So threesessions. So the first seller session is goingto update the quantity of this book that he'sinterested in or his selling action. But weare going to take this aggressive approachand log the whole table. Right. So let's saythe application is returned in a way thatit logs the whole product stable for right.And then the other session, let's say by onesecond session, buyer one comes and he isgoing to try to buy two books and and howactually we're going To do that is by runningan update. So we are basically updating theproducts table and we are subtracting thequantity by two, which means actually thewe are buying two books. And which book isthere in the book? Where are the record whereproduct ID equals one, right? So if you rememberthe data, product ID one is this book, let'sgo ahead and run this update in the secondsession. And it's going to obviously, wait,because the table itself has been locked forright by the seller session, the buyer, onesession is waiting. And let's go to the buyerto session the buyer to Australia trying tobuy a different book, which book is it thisother book, which is tiny habits book whereproduct ID equals two. And we're gonna dothat. Of course, even that is hanging or waiting.And that is actually a little bit crazy, isn'tit. So just sellers trying to update the quantityof this one record with just one book. Andeverything is tanking. And the buyer, too,was trying to buy a different book, he kindof gives up. So he moves to a different session.And instead of buying or trying to buy a book,he just tries to browse the website, whichis a select query or read query, read a selectquery, which is also hanging. So the buyertoo is getting frustrated right now. So youcan see how restricted this kind of sequences.So if someone's using table logs, that's goingto basically reduce the concurrency of theoperations that can happen in this database.So that's the main point here in this demo.Hey, my sequel learners. So in this session,we are going to take a brief look at row levellocks. In my sequel, I have three sessions,I'm already connected to my ecommerce database,MySQL database, and this is how the data looksnow. So we have a products table which holdsyou know, this data, only two books now, justdummy data that I created this, this is theprice and you have the quantity column showingyou how many, how much quantity is left foreach of these books. So the first sessionis seller session. The second session is buyersession, we can call this buyer one session.And the third session is a buyer to session.So this is the data. And just for clarity,actually, I wanted to show you the transactionisolation setting, which is read committed.And the auto commit is turned to turned offbasically, it's disabled. So unless I commitexplicitly, my transactions will not be permanent.So let's actually start with a seller. He'sgoing on the website or a portal that he hasavailable to update the inventory of, let'ssay the book one, it or the product one, whichis this book. And so he is going to clicksome buttons, which is going to translateto an update statement being executed in thisdatabase, right? So let's say he wants toincrease the number of books available inthe inventory. So that will mean quantityis going to be increased incremented by 50.So that's the UPDATE statement. And he's goingto run that update. And we can look at thebuyer one session, let's say buyer one istrying to buy the same book. And and then,so he's going to go on the website and thenclick on buy now or whatever and then is goingto translate into this UPDATE statement inthe database, choose quantity equals quantityminus one. So reducing the quantity by one,meaning he's buying a, buying a book. Andof course, there's going to be, you know,other statements updating other tables. Butthen to keep it simple, I'm just showing youthe product table changes section. So as youcan see, this is going to wait because selleris updating this particular row action. Andthat can be seen using acquittee. On datalocks, so if you're under this greddy, ofcourse, you can modify this query as per yourneeds. But then if you query this, you willsee that there's bunch of sessions and is,is the lock mode column. And then the tableon which the database on which the locks arehappening the table, so it gives you a lotof details. So, so if you want to understandwhat's going on here. So we have productstable, and then we have ix lock, which isintention, exclusive lock on the table itself,meaning like a transaction is about to getan exclusive lock. And this is at the tablelevel, but don't get tricked by that. Thereis also another row indicating there is arecord level or a row level lock. And, andthat is logging only this data equals one.So if you remember that UPDATE statement,we are using product ID. So and data for whichis one, actually, so product ID equals one.So that's what we are seeing over here. Andif you see here, this buyer session has actuallytimed out already, so he's going to attemptto buy again. So that's how like, you canactually look at the locking details in thistable. Let's try, let's say like buyer twocomes in at this point. And then he just triesto browse the inventory on this ecommercewebsite. So that would mean a select queryor read query. And he's, he's able to do thishappy reaction. Right. So there is no problem.So while the rollouts are happening, othersessions can read this table, they can evenlook at the data for the same product. Butthey they just cannot buy this book, becausethat is being blocked by the seller. So again,it timed out. So at this point, buyer twowants to buy a different book, you know, I'mnot able to buy this book, let me try buyinga different book, that's going to translateto, you know, product ID ID equals two, whichis not being locked by the seller. And thenthat update goes through. And at this point,let's say the seller has completed updatingthe inventory. And, of course, if you lookat the data, now, it's going to look different,because this has been updated to 150. Andof course, this hasn't gone down because buyer,buyer, one is still in the process of buyingthe book, because the commit has not happenedyet in the application. And then if we lookat the data, again, the data has gone down,or the quantity has gone down, then via two,let's say wants to buy the first book thatbuyer one wanted to buy. At this point, thereare no no locks in this table. Because everyone'scommitted, and let's say buyer, who is tryingto buy this, this book, and then he goes throughwith that date, and then commits and lookat data. And then the data is changing actually.So this is how row level log basically allowsfor high concurrency. So only the rows whichare logged by your transactions are not availablefor these other sessions to modify. RightSo the other records which are not touchedby your transactions are available for updating,deleting, etc. and all, of course, you canadd new books, that means inserting new recordsin this table. So I just wanted to show youthe difference between table level logs androw level locks. So this session and my previoussession will, will be useful in understandingthat difference. Thank you, I'll see you inmy next session. In this session, we're goingto be talking about deadlocks. And I justwant to show you how deadlocks happen, theydo happen in in a busy ecommerce or B. databaseoften, so it's good to know what they are.So it's going to be a very short and sweetsession. So here, we have a couple of sessionsagain, so connecting a connected to the samedatabase has two sessions, two different sessions.So let's say that we are working with productstable, right. So we have seen the stable beforein my previous sessions. Basically, this tablehas information about the products that arebeing sold on, 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, I seller comes to actually updatethe quantity of this product, basically, let'ssay if he wants to increase the quantity by25. For this first book, this is the commandthat he's, you know, that's going to be executed,you know, whatever buttons he is clicking,will be translated to an update command likethis. Right. And let's say like a differentperson from the same company wants to updatethe price of this book, not this book, let'ssay we have it the other book, I'm just actuallyusing the product ID to update the right product,right. So we have one session where seller,one is updating the quantity of this item,we have another session where we are updatingthe price of this item. And then if you seethe prices incremented by two, let's say $2.And this is fine, right? So now we have rowlevel locks. So this guy is holding a rowlevel lock on this row. And this guy is holdinga row level lock on this row. So this is fine,right? So we are operating on two differentrecords, two different locks are independentof each other. All good. So now let's saythe same seller, the second person who isupdating with price, wants to update the priceof this other book to actually like he isactually increasing the price. Again, by $2of this book, the product ID equals one, whichbook, this one right here, let's go aheadand try to increment the price. By runningthis command, you know, he's waiting on waitingfor the lock ECI exclusive lock. And that'snot available, because this seller has notcommitted actually is not committed. So let'sactually go back here and, and this sellerat the same time price to update the priceof or quantity of this book. So two sessionsare fighting for pretty much the same resource,you know, we ended up in a deadlock situation.So my sequel was smart, smart enough to justkill the session. Otherwise, we would havetwo sessions waiting for each other endlessly.Right? So here you can see the error codethat is thrown, it says deadlock found whentrying to get locks and try restarting thattransaction. So let's go ahead and query theproducts table and see how it looks. You cansee this, this whole transaction was rolledback. Correct. Both the transactions wererolled back. There's even this one was rolledback. So I think that Locke was also killed.So that's why this this one went through.If you can see the prices have increased by$2. right because initially For 1699 and 2039,and here 8099 and 20 to 39. Okay, so that'show it works. This is a typical deadlock situation,I hope this explanation was clear. And I willsee you guys in my next session. All right,my sequel learners. So in this session, we'regoing to talk about clustered indexes. So,so clustered index is not a different indextype as in, like, you can, you know, directlycreate a create a clustered index yourself.So it is a type of index that, that MySQLkind of maintains in the, you know, behindthe scenes actually. So, in also your tabledata, the data that you insert into your tablesor load into your tables are maintained inthese indexes. indexes only what I mean bythat is, so let's say this is a B tree index,right, so this is a B tree index. So you havemy sequel, creating this B tree index, asyou load the data into these tables. And then,you know, in the leaf nodes, what you haveis actually the data, the data that you'reloading into these tables, right? In the clustering,the sorting is based on the primary key thatyou define, or, you know, in this table, actually,so if you don't define a primary key, MySQLwill automatically pick up a non nullableindex key, what that means is, so let's saythat, in fact, actually, let's jump straightinto the example that I have prepared foryou guys. So so this is my MySQL Workbench.And, you know, I'll show you this table definition.So this is called products underscore one.And it's basically a products table that istypically used in a ecommerce store. And ifyou've been following my lessons, this iswhat I've been using, I just changed the nameof the table for, you know, demonstratingthis concept, this clustering, clustered indexconcept. So you have all these like columns,and I'm defining a primary key. Okay, so let'sjust start by, you know, I'm just going toswitch to a database called eecom, store ourschema called the econ store, I'm going todrop, you know, these tables if they existalready, by any chance. So the table doesn'texist, which is okay, so I'm going to createthis table, which I just talked about, calledproducts. And then this table has primarykey in a primary key is product ID. So productID is sort of like an integer column. So thisis an auto increment, right? So you don'teven have to provide value for this column,actually, when you load the data, so you canjust put all this information and load itand then we are good, MySQL will automaticallyincrement the value of this column action.So and then, of course, like I said, likethere is isbm column, which is over here,sort of leg book iasb. And information ifyou are, you know, if you remember your schooldays like this, this is be a number attachedwith any book, so something like that. Sosome kind of ISDN alphanumeric number. SoI'm going to call that like a unique key ora unique constraint. And let's go ahead andcreate the stable and this constraint. Sothat was successfully created. And I'm goingto create a procedure, which I can use tokind of like populate the stable, right, sodon't worry about the details of this procedure.This is something that I wrote to populatethis table. And then that is successfullycreated and change the delimiter back to asemi colon. And then I'm going to call thisprocedure and which is going to throw somewarnings, which is okay with me. As long asas long as the data gets populated, I'm fine.So it's going to probably generate some, youknow, load some 6000 plus rows into this table.So we'll see how much we get this awesome.So it's actually loading a lot of data. Itseems to be done. So let's go ahead and committhe data and Now Actually, I'm going to selectthe data in this table, right? Just selectall the data, and you will see that the databy default, or the data is actually sortedbased on the primary key, which is productID. And you can see, we know, I haven't likespecified any ordering. So this is, you know,this is the default ordering of data, right.And so basically, your table data is sortedbased on your clustered index, which is primarykey over here, because you have the primarykey in the table section. Right. So now thenext thing is actually, I'm going to createa similar table, which is, you know, so I'mgoing to call it products too. But in thiscase, I'm going to basically not define aprimary key, I'm still going to have a uniquekey called, again, the same thing, you know,it's isbm, it's a unique key. And let's justgive it a different name, just so we havekind of like, we have different names fordifferent constraints. So let's actually goahead and create this table. And so this tableis created, I'm going to copy the data fromthe first table that you know, where I loadeda lot of data. So I'm going to copy the datafrom that table into this table, right. Sojust very simple. And then I'm going to commit,right, so that's a board 6455 6455 numberof rows inserted into this table. And I'mgoing to select all the rows from this table.And you can see that now, the data is notsorted by product Id rather it is sorted bythis iasb. And it is sorting based on firstcharacter first, and then Initially, the firstand second characters are the same, then 010true. And that keeps going 05 and then 090,a BCD of GE hedge and then having after thezeros, you know, see one, so it is basicallysorting data based on iasb. And and why isbeing because because of the absence of primarykey, it's going to choose this iasb and column,as are the it's going to choose this non nullableunique index key, which is based on iasb andcolumn, right. So it's starting based on this,but this is actually a terrible, terribleidea. Because if you're generating random,alphanumeric strings for iasb. And, you know,then you're not going to be generating thestring in sort of like an ascending orderor in any type of order, actually. So in thatcase, actually, you know, when you're, asyou're inserting data into the stable, thisB tree is going to be created behind the scenes.And then my sequel, like whatever programis creating or maintaining this data structurebehind the scenes has to work really, reallyhard to manage this Bre B tree index, actually,right. That's why this is a terrible ideato have like a you UID or some kind of alphanumericstring as a primary key actually, or in theabsence of primary key. Well, my sequel isgoing to use this this key for clustering.And again, it is very bad. So keep that inmind when you're creating tables actually.Right. So finally, what I'm going to do iscreate another table called product three.And before that, I'm going to show you theoutput of this query, which is basically goingto come up empty or no, no, no road rows returned.All I'm doing is actually checking whetherthis index the index with named Jen flushedindex is there in this database, actually.And then I'm checking the InnoDB tables andinformation schema I'm joining in odb tablesand in odb indexes. And I'm checking whetherthis index indeed exists, right? Saying itdoesn't exist, which is where the this, thiscredit, return no rows, and I'm going to createthis table and this time, I'm not even goingto create the create a unique key. And I'mgoing to make all these columns as nullablecolumns, you know. So I just want to showyou what happens when you have a scenariowhere you're creating a table with all nullablecolumns and no primary key index no unique,not nullable index and you know Then I'm goingto insert data into this table. Again, six,the 400 plus rows inserted, commit. And thenI'm going to select from this product threetable right now. And when the data comes up,you can see that there is still some orderingthat's happening. And, you know, we don'thave any of these options primary key or anot nullable, unique key available, then howis MySQL able to sort data? What is it using,so it actually uses a hidden, hidden key actually,right, a hidden primary key. So if you runthe same query, again, is ready, you can seethat this index has been created on productsthree table, which is maintained internallyby my SQL, for just the purpose of clusteringthis table, actually. Okay, so that's a lotof information. I hope you found this useful.And I will see you guys in my next video.Hello, my SQL learners. So in this session,I want to teach you the basics of using explainor explained plan in MySQL. Alright, so nowlet's just let me just show you the tablethat I'm going to be working with, I'm goingto be working with that table called productsunderscore one. And it's got some net in aproduct name, product type price. And if theproduct is a book, it will have an ISP a numberattached with it. And then there is a quantitycolumn. So these are some basic columns thatyou would see in an e commerce online store.So let's get started by just looking at theindexes of this table. So this basically hastwo indexes. One is a primary key index, whichis on the product ID. And the other one isan index on the iasb and column. And thisis a unique index, actually. So let's getstarted by picking a simple query that weare going to kind of like optimize using explain.So the query that I'm going to be using isthis. So I'm going to be selecting iasb. Andfrom this products underscore one table whereproduct name has cat in it. So the productname is cat. Okay, so And before I run thisquery, I'm going to look at the explainedplan of it. And I'm going to put a slash gat the end. So I get that we'll put in therein a readable format. So first of all, itgives this output, right, and selectors, justone straights, simple select. That's whatthis is showing. But the main thing is weare working with our this particular row isreferring to this table. And apart from that,actually, you have all these columns, andthen they are all null right now, like theydon't make much sense apart from this. Sothis is a tight column and all means thatit is doing a full table scan. Basically,MySQL is doing a full table scan, it's scanningthe whole table. And how many rows is thatit's these many rows. And we are using a filterover here, it gets all those rows and thenit filters the output. And basically a youknow, there's about 600 rows with productname equals cat, right, so the filtered personages like 10%, basically, and then there issome extra information. Let's go ahead andcreate an index on this table. Create indexcalled, you know, we can give an arbitraryname. And, and I'm going to create add onproducts, one table and the column is productname, of course. This is the column on whichI'm creating the index. Actually, let's justgo ahead and run the explain again. So thisis the explained plan. And that's how it looks.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 datafor all these columns. So first of all possiblekeys column shows like all the indexes thatthis query can use. And, and out of which,like this is the key or index that it is,you know, it is going to use this particularexecution is going to use, and this is thekey length in bytes actually write the numberof rows that is being scanned in this key,which is 589. And, you know, since this isindex based, we're not really filtering data,rather, we're just going to the index andgetting the data. So there is no filteringover there. Let's actually create anotherindex, which also includes iasb. And, andsee, like, what happens, actually, we're gonnacreate the other index and give it a differentname. So let's go ahead and run the explainplan again. So now, again, the possible keysare these two indexes, but it still choosesto go with this particular index, and theindex, key length is the same, and then grows,and etc, etc. So there's no filtering thathappened, right? Because we're choosing anindex. So you might be wondering, like, youknow, why it's not using the covering index,right. So this is supposed to be the coveringindex and covering indexes are supposed tobe better than normal, non clustered indexor a secondary index. So you can actuallylike, use a format like JSON format to getmore information. So how you can do that isby just specifying like format equals JSON,and use the use that. And so that's goingto give you the output in JSON format. Andyou can see that the you know, it gives youa little bit more information as then likethe query cost, you know, this is how muchit's going to cost for my sequel to executethis query. And this is a representation ofthe amount of work MySQL has to do to runthis query actually. So the cost for thisone is 7690, right. And then again, it saysthese are the possible keys. And used keyis used key parts is product name, which wasnot given over here. And then there is a costand for which is a split of where the costis going. So you can read my SQL documentationon all these fields. You know, you might bewondering why the covering index is not beingused. And we can actually force that indexby using this use index. Syntax or use indexkeyword. And then I'm going to put the indexname that I want to force which is this one.And when I ran it, this ran the explainedplan this time, it shows the cost of thisone is going to be 109 point two seven, youknow in comparison to the previous explainplan, where the cost is only 76. And thisis why my sequel is going with this particularplan instead of this guy. Okay, I hope thissession was useful.\n"