SQL Intermediate: Query Optimization

Transcript of the free preview lesson. Remaining lessons unlock with the full course.

In these lessons, we'll learn the common practices to optimize queries in SQL. In this lesson, we're going to take a look at how query optimization impacts database performance. Speed and efficiency of data retrieval are fundamental concepts for any application working with the database. Essentially, if a query takes too long, it can cause a bottleneck or in extreme cases, cause your application to fail. That's where query optimization comes in. This practice involves modifying your queries and database structure to improve query performance and overall database efficiency. Let's start by running a simple query on our players table. In this query, we're going to use select star to retrieve all columns from the players table. Let's go ahead and run this. And now this is a simple operation and it's fast enough on our small table. However, in a real world scenario where tables have millions of rows, this could take a significant amount of time to fetch all that data. A quick way to further optimize your query would be to fetch only the columns that you need. This requires us to understand our requirements in order to pull the exact columns that we need. So let's say we only needed the players names. Rather than returning all columns, we can simply just list the column we need. In this case, the players name from the players table. Let's go ahead and run this by pressing control enter. And by reducing the amount of data we fetch from our database, we can increase the speed of our query dramatically. If we want to make significant improvement to our queries, we can use indexes. An index in a database is similar to an index in a book in that it helps us speed up the retrieval of rows. So say, for example, we frequently search for players based on their position. It'd be beneficial to have an index on the position column. To perform this operation, we'll use the create index statement. We'll give our index a name. We'll use the on keyword to specify which table. In this scenario, it's the players table. And in parentheses, we'll list the column we want to index. Let's go ahead and run this by pressing control enter. And now when we click on our players table, switch over to the structure tab. We now see that there's an index been created on our position column, as well as we can see the index we just created down below. Now, when we run a query, we want to find the players name from the players table and use a where clause with the position column equal to a value or a list of values. This query's efficiency will be significantly increased. Let's go ahead and run this. And when we run this query, our table's already been indexed on the position column, and we'll retrieve those rows significantly faster. This lesson uncovered the basics of query optimization and its importance in maintaining efficient database performance. Two common practices you can use to optimize queries are only selecting the necessary data columns and rows, as well as creating index on frequently used columns. Stay tuned for the next lesson, where we use more indexes, optimize our join queries, and use explain plans. Thanks for watching.