Yorkville High School Computer Science Department
Yorkville High School Computer Science Department on Facebook  Yorkville High School Computer Science Department Twitter Feed  Yorkville High School Computer Science Department on Instagram

Yorkville High School Computer Science

ASSIGNMENTS: Compiler Part 2 - December 3, 2018 :: Challenges 7 - December 7, 2018 :: Network App - December 14, 2018 >>

Database Programming :: Lessons :: Query Optimization

Indexes

An index is a sorted table that can be created for a field to make SELECT queries quicker. It leads to slower INSERT and UPDATE queries, however, since the index needs to be created every time there is an update to the source data. The index also adds to the size of the database. Most of the time these trade offs are worth it. Below are the guidelines for deciding whether or not a field should have an index:

Indexes can also be added to multiple fields, which is helpful if fields are used in conjunction a lot, such as last name and first name fields.

The EXPLAIN Command

The EXPLAIN command can tell you how well queries are using indexes and can help you improve your queries.

EXPLAIN SELECT * FROM `Director` WHERE `DirectorLast` = 'Spielberg'

The above query does not actually run the query, but instead produces a table like the following:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Director ALL NULL NULL NULL NULL 25 Using where

The type of the query is the join type, which can be one of the following, ranked from fastest to slowest:

  1. system: The table is a system table with only one row.
  2. const: The table has at most one matching row, can be read once, and is treated as a constant for the remainder of the query.
  3. eq_ref: No more than one row from this table will be read for each combination of rows from the previous table.
  4. ref: All matching rows from this table will be read for each combination of rows from the previous table.
  5. range: Only rows in a given range in the table will be retrieved using an index.
  6. index: A full scan of the index will be performed for each combination of rows from previous tables.
  7. ALL: A full scan of the table will be performed for each combination of rows from previous tables.

The possible_keys result lists any fields that could serve as keys to improve performance.

Adding an index to the DirectorLast field in the above example will result in the following EXPLAIN table:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Director ref DirectorLast DirectorLast 37 const 1 Using index condition

The ref result specifies which columns or constants are used to select rows from a table and the rows result specifies how many rows might need to be examined to execute the query.

The LIMIT clause can be added to a query to limit the number of results it will return. If you only need the first 5 directors from the director table, it doesn't make sense to output all the table contents. You could do the following:

SELECT * FROM `Director` ORDER BY `DirectorLast` LIMIT 5
Yorkville High School Computer Science Department on Facebook Yorkville High School Computer Science Department Twitter Feed Yorkville High School Computer Science Department on Instagram