USing sakila SELECT last_name, first_name, ROUND(AVG(length), 0) AS average FROM actor INNER JOIN film_actor ON film_actor.actor_id = actor.actor_id INNER JOIN film ON film_actor.film_id = film.film_id WHERE title = "ALONE TRIP" GROUP BY last_name, first_name ORDER BY average; Highlight the SELECT query. In the main menu, select Query > Explain Current Statement. In the Display Info box, highlighted in red below, select Data Read per Join. The execution plan depicts the result of EXPLAIN for the SELECT query. The execution plan has seven steps, corresponding to the red numbers on the screenshot: Access a single film row using the idx_title index on the title column. Access matching film_actor rows using the idx_fk_film_id index on the film_id foreign key. Join the results using the nested loop algorithm. Access actor rows via the index on the primary key. Join actor rows with the prior join result using the nested loop algorithm. Store the result in a temporary table and compute the aggregate function. Sort and generate the result table. Now, replace = in the WHERE clause with < and generate a new execution plan. Step 1 of the execution plan says Index Range Scan. The index scan accesses all films with titles preceding "ALONE TRIP", rather than a single film.Finally, replace < in the WHERE clause with > and generate a third execution plan. Step 1 of the execution plan says Full Table Scan and accesses actor rather than film.In the zyLab environment, write EXPLAIN statements for the three queries, in the order described above. Submit the EXPLAIN statements for testing.
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
USing sakila
SELECT last_name, first_name, ROUND(AVG(length), 0) AS average
FROM actor
INNER JOIN film_actor ON film_actor.actor_id = actor.actor_id
INNER JOIN film ON film_actor.film_id = film.film_id
WHERE title = "ALONE TRIP"
GROUP BY last_name, first_name
ORDER BY average;
Highlight the SELECT query.
In the main menu, select Query > Explain Current Statement.
In the Display Info box, highlighted in red below, select Data Read per Join.
The execution plan depicts the result of EXPLAIN for the SELECT query. The execution plan has seven steps, corresponding to the red numbers on the screenshot:
- Access a single film row using the idx_title index on the title column.
- Access matching film_actor rows using the idx_fk_film_id index on the film_id foreign key.
- Join the results using the nested loop
algorithm . - Access actor rows via the index on the primary key.
- Join actor rows with the prior join result using the nested loop algorithm.
- Store the result in a temporary table and compute the aggregate function.
- Sort and generate the result table.
Now, replace = in the WHERE clause with < and generate a new execution plan. Step 1 of the execution plan says Index Range Scan. The index scan accesses all films with titles preceding "ALONE TRIP", rather than a single film.Finally, replace < in the WHERE clause with > and generate a third execution plan. Step 1 of the execution plan says Full Table Scan and accesses actor rather than film.In the zyLab environment, write EXPLAIN statements for the three queries, in the order described above. Submit the EXPLAIN statements for testing.
Trending now
This is a popular solution!
Step by step
Solved in 4 steps
