How to speed up my query. This is a question
that you may ask yourself every time you have a query that doesn't perform
as you wish. In this video,
we will check how we can use AI and a tool called EverSQL by Aiven
to speed up SQL queries on both
mySQL in PostgreSQL for Free. So let's check it out. So let's start by analyzing the data. We see here is that we have a table
called taxi_trips, which give us the detail of each taxi trip
with starting location, destination location, payment type
and total amount. Then we have another table
called taxi_zones that we can use to join with taxi_trips,
with locationId, zone and borough. Then let's check a slow query What we will see here is a slow query
trying to calculate the average amount
for a taxi trip. Starting in a specific location,
ending in a specific location, starting in a borough, which is either
Queens or Manhattan on a specific date. The 1st of January 2019. And payment type equal to two,
which means that we are checking
the payment types that are paid in cash. So if we run the query, the query will take approximately
20 seconds to run. So let's wait. Here we are, 20.10 seconds. So how can we speed up this query? Let's navigate to EverSQL.com
and immediately we can click on "Start Optimizing for Free". We can select either
to optimize a single query or to monitor an entire database
and receive optimization insight. So let's click on optimize a single query. Let's select mySQL,
which is the database that we are going to optimize for, and then we can select
where we deploy the database. In our case, it's Amazon RDS. Then finally, we can check the language
that we are writing our application. In our case, it's pure SQL. We can immediately
copy and paste the SQL of our query and select the version of mySQL that
we are using, and then click on continue. After this, we can give to our EverSQL additional information
about the data structures that we have in the database
by copying the query that we can execute against our database to retrieve
the definition of tables and indexes. We can retrieve the output of the query
that will not copy any data, but just the definition
of our table structures and paste it in the EverSQL UI and then click on all done, optimize my query. We immediately see some improvement suggestions, but in order to unlock them,
we need to log in. So let's do it. We can log in. Once we log in, we can immediately
see that EverSQL will provide us suggestions
on how to improve our query. We can see that EverSQL suggests
to add three indexes. The first one on payment
type and pick up date. That will speed up our WHERE clause
about the cash payment and the pick up date and then other two
indexes on locationId, zone and burough. Then it will tell us to rewrite the query. Let's check the difference between
the original and the updated query. We can see that the updated query
has a subselect filtering only the date about the specific date
and payment type and then the type of filter
that we were applying on the date moved away from using a function into properly
just filtering the date field itself. So we can copy the index suggestions
and we can apply the three indexes in our terminal to create those indexes
in the MySQL database once the
the three indexes have been created. We can go back to the EverSQL UI. Copy the SQL that has been optimized for us. So let's do that. Let's go to the EverSQL
UI and copy the updated SQL and then let's try to execute the updated
sequel and check the new performances. As you can see here, the performances went down from 20 seconds
to 0.52 seconds. This is absolutely great. In the last few minutes, we saw how to go
from 20 seconds of execution time to 0.52 seconds, all by using AI
and a tool called EverSQL by Aiven that allow us to optimize MySQL
and PostgreSQL queries for free. If you want to know more,
check out EverSQL.com. If you want to have more videos like this,
please leave a comment below. It's everything for today. Hope to see you soon! Bye, from Francesco!
