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 -> Copy and paste in URL and click download

Again, there is no problem on dataset

Explanation / Answer

Question 1:

EXPLAIN QUERY PLAN:

* EXPLAIN QUERY PLAN command operates on a SELECT statement that is implemented by performing a full-table scan on any table:

sqlite> EXPLAIN QUERY PLAN SELECT * FROM Bird WHERE Bird.first_name = 'Elizabeth';
0|0|0|SCAN TABLE Bird
The example above shows SQLite picking full-table scan will visit all rows in the table. If the query were able to use an index, then the SCAN/SEARCH record would include the name of the index and, for a SEARCH record, an indication of how the subset of rows visited is identified.

*********************************************************************************************

Question 2:

As per the syntax,

The create index statements for the above requirements is

CREATE INDEX bird_birthdate_index ON Bird(birthdate) USING BTREE;

CREATE INDEX bird_firstname_index ON Bird(first_name) USING BTREE;

**************************************************************************************************************

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

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