Nov 05: Indexes, Query Optimization

Learning Objectives

After today's class, you should be able to:

• Explain differences between a TABLE, a VIEW, and an INDEX.
• Describe how query optimization works based on selectivity.

Lesson Outline

Code Review [15 min]

See files changed in profs repository:

• All table/column names lowercase
• No more backticks / double quotes
• Host and port in DB connections
• MySQL login path uses group name
• Set DBMS in queries/__init__.py
• Added SQLite as another example

Lecture [45 min]

• Indexes and Query Optimizer
• Overview of zyBook 6.3 – 6.7

GP4 Overview [15 min]

• Add indexes to improve performance
  • Create an index.sql script
  • Run at the end of build.sh

    echo Creating indexes...
    $CMD < index.sql
    

• Create a route for each of your views
  • The form must have user input of some kind
  • The response should render a Google Chart
  • Use EXPLAIN to verify your index was used

Your To-Do List

• Read zyBook sections 6.3 – 6.7
• Start work on GP4 (coming soon)