INFO20003 Week 8 Lab Solutions
PLEASE NOTE: Your exact query cost may differ from those displayed in these solutions. It will depend on many factors (disk characteristics, database parameters, etc). When optimising queries, it is the change in the cost, not the cost itself, that is an indication of query optimisation.
Section 3: Measuring query performance
¡ô Task 3.1 Run the following queries, and for each one, record the number of rows returned, the total cost of the query, and which indexes were used (if any).
Copyright By PowCoder代写 加微信 powcoder
Query 1 Query 2 Query 3 Query 4
Number of rows returned
256 383 10 620
Cost of query
5545.35 5545.35 1742.19 5545.35
Indexes used
clients table: clients_country_fk customers table: None
Section 4: Practical query optimisation using indexes
¡ô Task 4.9 Look back at the first two queries from Task 3.1. Using what you have learned in this section, create one or more indexes that will improve the execution cost and plan of Queries 1 and 2. Then run the queries and fill in the following table.
Queries 1 and 2 both have a WHERE condition on the cust_state_province column. This column is a candidate for the creation of an index:
CREATE INDEX cust_state_province_idx ON clients (cust_state_province);
This improves the query cost as follows:
Original cost of New cost of query (Task 3.1) query
Indexes used
Query 1 5545.35 115.71 cust_state_province_idx Query 2 5545.35 212.10 cust_state_province_idx
INFO20003 Week 8 Lab Solutions 1
¡ôTask4.10 HowmuchdoestheuniqueindeximprovethecostofQuery3fromTask 3.1? Fill in the table:
Original cost of query (Task 3.1)
New cost of Indexes used query
1212.45 clients table: clients_country_fk customers table: country_name_idx
The query is asking how many rows are present for each (country_id, cust_state_province, cust_city) combination. The obvious way to do this is by reading the entire table, i.e. performing a full table scan.
In theory, you could also accomplish this by reading the entirety of an index over these three columns, i.e. a full index scan. This would be markedly cheaper given the number of columns in the clients table.
MySQL 8.0 also supports another index scan mode called ‘Loose Index Scan’, but this requires that no aggregate functions other than MIN or MAX are used. A loose index scan DOESN’T touch every value in the index (unlike a full index scan), instead it just looks at the min/max values of each combination, which it can do because B+ trees are stored in sorted order. Since this query uses a COUNT function, we will not be able to use a Loose Index Scan. https://dev.mysql.com/doc/refman/8.0/en/group-by-optimization.html
Query 3 1742.19
¡ô Task 4.11 Can the performance of Query 4 be improved any further? If so, how? If
not, why not?
INFO20003 Week 8 Lab Solutions 2
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com