A physical query plan is like a routine that the DBMS follows to assemble the re

Question

A physical query plan is like a routine that the DBMS follows to assemble the requested query
results from the underlying base tables. Different queries will have different physical plans. in
fact, the same query may be translated into different physical plans depending on the physical
database design. MySQL provides an ‘EXPLAIN’ function, as shown in the figure below, to help
you to check the query plan of your query. You can use this function to examine how is your
query will be executed internally, what indexes are being used, and the total cost of your
query.
In this homework, we want you to examine the impact of indexing on several queries on the
TopicalBirds dataset. Pay close attention to what you see both with and without indexes - this
is what you will need to know how to do someday if you end up “doing databases for dollars”
and need to performance tune your database application(s)! For example, the figure below
shows that the example query is going to be executed using a full table scan - i.e., by iterating
over all of the birds - since there’s not faster “access path” available. (In this case, that’s
precisely what the query is asking for - show everything about all birds - so that query isn’t
further tunable. Others are, however, as you will soon see.)

1. To start down the path of exploring physical database designs (indexing) and query plans,
start by checking the queries plans for each of the following queries without any indexes using
the EXPLAIN function. Report the query plan after each query by taking snapshots and pasting
them into your copy of this document.
a) SELECT * FROM Bird WHERE Bird.first_name = 'Elizabeth';
b) SELECT * FROM Bird WHERE Bird.first_name LIKE '%abeth';
c) SELECT * FROM Bird WHERE Bird.first_name LIKE 'Eliz%';
d) SELECT * FROM Bird WHERE Bird.btag = 'laura43';
e) SELECT * FROM Bird WHERE Bird.birthdate > '1975-1-1';

2. Now create indexes (which are B+ trees, under the hood of MySQL) on the Bird.first_name
attribute and Bird.birthdate attribute separately - i.e., create two indexes, one per attribute.
Paste your CREATE INDEX statements below.

3. Re-explain the queries in Q1. Report on the query plan after each query, as before. Be sure
to look carefully at each plan - think about what you are seeing there.
a) SELECT * FROM Bird WHERE Bird.first_name = 'Elizabeth';
b) SELECT * FROM Bird WHERE Bird.first_name LIKE '%abeth';
c) SELECT * FROM Bird WHERE Bird.first_name LIKE 'Eliz%';
d) SELECT * FROM Bird WHERE Bird.btag = 'laura43';
e) SELECT * FROM Bird WHERE Bird.birthdate > '1975-1-1';

f) Examine and compare the query plans for queries a-c above - in one brief sentence,
what can you observe about B+ tree indexes and their usefulness for equality and
different LIKE queries? (Enter your observation below.)
g) Examine and compare the query plans for queries d-e above - in one brief sentence,
what can you observe about B+ tree indexes and their usefulness for equality and
range queries? (Enter your observation below.)

4. It’s time to go one step further and explore the notion of a “composite Index”, which is an
index that covers several fields together.
a) Check the query plans of following queries. Report the query plan after each query.
i) SELECT * FROM Bird WHERE Bird.first_name = 'Elizabeth' and Bird.last_name =
'Smith';
ii) SELECT * FROM Bird WHERE Bird.first_name = 'Elizabeth';
iii) SELECT * FROM Bird WHERE Bird.last_name = 'Smith';
b) Create a composite index on the attributes last_name and first_name (in that order!)
of the Bird table. Paste your CREATE INDEX statement below.

c) Re-explain these queries and report the query plan after each query.
i) SELECT * FROM Bird WHERE Bird.first_name = 'Elizabeth' and Bird.last_name =
'Smith';
ii) SELECT * FROM Bird WHERE Bird.first_name = 'Elizabeth';
iii) SELECT * FROM Bird WHERE Bird.last_name = 'Smith';
d) Examine and compare the query plans for this composite indexing example. As before,
in one brief sentence, what can you observe about composite B+ tree indexes and
their applicability to multi-attribute queries? (Enter your observation below.) If you
notice anything “familiar”, based on things you saw earlier, feel free to mention that
too.

EXTRA CREDIT [10 points] If you would like to delve even further into the wild world of query
plans and indexing, you could try dropping the composite index from Q4 part b (above) and
instead creating two separate indexes, one per attribute, and examine the query plans for
your queries under that alternative physical database design. If you wish to do so, your task
(detailed below) will be to repeat steps Q4 b-d above but with those two indexes instead of
the single composite index.
a) Drop the index that you created in Q4 using MySQL’s DROP INDEX statement. Paste
your DROP INDEX statement below.
b) Create the two separate indexes. Paste your CREATE INDEX statements below.
c) Re-explain the queries in Q4 with the created indexes.
i) SELECT * FROM Bird WHERE Bird.first_name = 'Elizabeth' and Bird.last_name =
'Smith';
ii) SELECT * FROM Bird WHERE Bird.first_name = 'Elizabeth';
iii) SELECT * FROM Bird WHERE Bird.last_name = 'Smith';
d) Examine and compare the new query plans for these queries with the previous query
plans from Q4; briefly summarize your observations below.

[TopicalBirds dataset link (sql format)]

https://drive.google.com/open?id=0B3fUBGGj2Gh5bzJWZTJOMlVXdVk

Explanation / Answer

A physical question arrange is sort of a routine that the DBMS follows to assemble the requested question
results from the underlying base tables. totally {different|completely different} queries can have different physical plans. in
fact, an equivalent question could also be translated into totally different physical plans reckoning on the physical
database style. MySQL provides associate ‘EXPLAIN’ operate, as shown within the figure below, to help
you to envision the question arrange of your question. you'll be able to use this operate to look at however is your
query are dead internally, what indexes ar being employed, and therefore the total price of your
query.
In this prep, we wish you to look at the impact of classification on many queries on the
TopicalBirds dataset. Pay shut attention to what you see each with and while not indexes - this
is what you may have to be compelled to savvy to try and do sometime if you finish up “doing databases for dollars”
and need to performance tune your information application(s)! for instance, the figure below
shows that the instance question goes to be dead employing a full table scan - i.e., by iterating
over all of the birds - since there’s not quicker “access path” on the market. (In this case, that’s
precisely what the question is inquiring for - show everything concerning all birds - in order that question isn’t
further tunable. Others are, however, as you may presently see.)

1. to begin down the trail of exploring physical information styles (indexing) and question plans,
start by checking the queries plans for every of the subsequent queries with none indexes victimisation
the justify operate. Report the question arrange once every question by taking snapshots and pasting
them into your copy of this document.
a) choose * FROM Bird wherever Bird.first_name = 'Elizabeth';
b) choose * FROM Bird wherever Bird.first_name LIKE '%abeth';
c) choose * FROM Bird wherever Bird.first_name LIKE 'Eliz%';
d) choose * FROM Bird wherever Bird.btag = 'laura43';
e) choose * FROM Bird wherever Bird.birthdate > '1975-1-1';
2. currently produce indexes (which ar B+ trees, below the hood of MySQL) on the Bird.first_name
attribute and Bird.birthdate attribute one by one - i.e., produce 2 indexes, one per attribute.
Paste your produce INDEX statements below.
3. interpret the queries in Q1. Report on the question arrange once every question, as before. Be sure
to look fastidiously at every arrange - consider what you're seeing there.
a) choose * FROM Bird wherever Bird.first_name = 'Elizabeth';
b) choose * FROM Bird wherever Bird.first_name LIKE '%abeth';
c) choose * FROM Bird wherever Bird.first_name LIKE 'Eliz%';
d) choose * FROM Bird wherever Bird.btag = 'laura43';
e) choose * FROM Bird wherever Bird.birthdate > '1975-1-1';
f) Examine and compare the question plans for queries a-c on top of - in one temporary sentence,
what are you able to observe concerning B+ tree indexes and their quality for equality and
different LIKE queries? (Enter your observation below.)
g) Examine and compare the question plans for queries d-e on top of - in one temporary sentence,
what are you able to observe concerning B+ tree indexes and their quality for equality and
range queries? (Enter your observation below.)
4. It’s time to travel one step more and explore the notion of a “composite Index”, that is associate
index that covers many fields along.
a) Check the question plans of following queries. Report the question arrange once every question.
i) choose * FROM Bird wherever Bird.first_name = 'Elizabeth' and Bird.last_name =
'Smith';
ii) choose * FROM Bird wherever Bird.first_name = 'Elizabeth';
iii) choose * FROM Bird wherever Bird.last_name = 'Smith';
b) produce a composite index on the attributes cognomen and forename (in that order!)
of the Bird table. Paste your produce INDEX statement below.
c) interpret these queries and report the question arrange once every question.
i) choose * FROM Bird wherever Bird.first_name = 'Elizabeth' and Bird.last_name =
'Smith';
ii) choose * FROM Bird wherever Bird.first_name = 'Elizabeth';
iii) choose * FROM Bird wherever Bird.last_name = 'Smith';
d) Examine and compare the question plans for this composite classification example. As before,
in one temporary sentence, what are you able to observe concerning composite B+ tree indexes and
their pertinency to multi-attribute queries? (Enter your observation below.) If you
notice something “familiar”, supported stuff you saw earlier, be happy to say that
too.
EXTRA CREDIT [10 points] If you'd prefer to cut into even more into the wild world of question
plans and classification, you'll attempt dropping the composite index from this fall half b (above) and
instead making 2 separate indexes, one per attribute, and examine the question plans for
your queries under it various physical information style. If you want to try and do thus, your task
(detailed below) are to repeat steps this fall b-d on top of however with those 2 indexes rather than
the single composite index.
a) Drop the index that you simply created in this fall victimisation MySQL’s DROP INDEX statement. Paste
your DROP INDEX statement below.
b) produce the 2 separate indexes. Paste your produce INDEX statements below.
c) interpret the queries in this fall with the created indexes.
i) choose * FROM Bird wherever Bird.first_name = 'Elizabeth' and Bird.last_name =
'Smith';
ii) choose * FROM Bird wherever Bird.first_name = 'Elizabeth';
iii) choose * FROM Bird wherever Bird.last_name = 'Smith';

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.