Notes of SQL performance & optimization.
When a query is entered, it is 1st passed to the parser. It checks for:
If the checks fail, the error is returned. If the checks pass, the query is turned into an algebraic expression & passed on.
The engine turns the plan into operations for the database & returns the result.Source
When you start dealing with bigger data sets, you'll start to care more about how quickly your queries execute. SQL is a declarative language - each query declares what we want the SQL engine to do, not how. The how, known as the query plan, is 1 thing that affects the efficiency of a query. Imagine we had the following data:
We want to find all the starships who class = "fighter". SQL could find the result using 2 different plans; Full table scan or Create an index.
SQL will look at every single row in the fleet table & return the rows where class = "fighter". This would be fast if the table only had 4 rows. If the table has 1 million rows however, then it would require looking at 1 million rows, which could be slow.
Creating an index of a table involves picking a column(s) that you want to sort your table by. SQL will then generate a new sorted table, made up of the column(s) you selected & the id column (to link each row back to the original table).
Now when you do the query:
SELECT *FROM fleetWHERE class = "fighter";
If the table contains 1 millions rows, creating the index would require ~60 million operations (depending on the engine) which could table some time.
Each proceeding binary search on the original table however, would only require
log(1 000 000) = 6 lookups to find a value.
The process of optimizing queries.
EXPLAIN QUERY PLANin front of a query to see the query plan).
fleet table had a significant number of rows, users were regularly querying that table to find all rows with a specific class & it was taking a significant amount of time.
We could create an index based on the
class column in our schema.
This would take some time to be created but now all the queries would be faster.