Subtitles of the Movie

When we're running any type of table, whether it's a Normal Table, a Temporary Table, or Memory Table, we really need to know that it's running efficiently. This is especially true when we're running queries against these particular tables. Optimizing our environment is number one. As an intro to optimization, I'd like to explain the EXPLAIN statement. Now please keep in mind that this statement runs very specifically depending on the database that you are using. So whether you have MySQL or Oracle or SQL server, this all depends on how you can use the EXPLAIN statement itself. As a result, I encourage you to look into the specifics for the database that you are actually utilizing. The EXPLAIN statement captures information about the plan that has been chosen for running the statement or query that has been executed. What happens is that the EXPLAIN statement will run the information in the background and actually create a table that we can troubleshoot. Statements that you can use with the EXPLAIN are DELETEs, INSERTs, SELECT, UPDATEs. Other statements of this nature can also be run. Now what the EXPLAIN itself does is describes the strategy that's been used internally. If we're running a query statement or a single statement, we really need to know how it's being processed. It is being processed efficiently. What we really are interested in is how is the searching being done. The statements that we use usually indicate what form the columns or the indexes are in. How these indexes and structure are used have a lot to do with the performance of our database. Keep in mind that when you're actually running the EXPLAIN statement, you're not really executing the query or the statement. You're not gonna get the results. It's running through to really see the optimization plan of the query or statement itself. It's then gonna go ahead and generate a Result Table. Now, if you name the table, it's very easy of course to see it in the background. Should you not give a name specification, however, know that the Result Table will be shown in the background with the name of SHOW. Now the EXPLAIN statement can be used to check the effect of your indexes on a statement. The idea here is we could create or delete an index and see how it runs. This allows us to estimate the time needed by the database to process the statement. In an upcoming video we'll discuss our indexing strategy in detail. Indexes can be very complicated because it's very personalized based on your data and your resources. In essence though, the indexing is helping you with the sort order of the columns within your query or table. When your working, the specified query is not executed so that the EXPLAIN statement can concentrate on the performance issues rather than the result issues themselves. Now, here is an example of what we might see when we're working with an EXPLAIN statement, goes to the table, the type, your keys, your key links, your rows, your extra information. Let's talk about a few of these. The most important parts are possible keys, rows and extra. Possible keys will show NULLs. Keep in mind that indexes cannot run against a NULL statement so we know that with our indexes, eliminating blank data and preferably making sure that we have unique records is important to performance. We also want to be able to see how many rows that we're searching through. The row is important because that's exactly what it will show us. When we see how many rows have to be returned, it's really a great way to be able to spot problems. The third one that is important is extra. This shows what functionality is used to perform the actual search itself. Here, it would filter a search using where clause to tell us what the effects are. So when you're looking through it, your Possible Keys, your Rows and your Extra give us the most bang for the buck when it comes to the information that can be analyzed. Now, as I had mentioned earlier, watch availability. When you're working in terms of the Explain Plan option, each system has its own way of working with it. On your screen is a list of where the Explain Plan option is currently available, but please keep in mind, they're all slightly differently depending on the database you're using. The best case scenario here is to thoroughly go through the information given to you by the database program to know exactly how Explain will apply to your situation.