Hour 16, Using Indexes to Improve Performance

Team-Fly    

 
Sams Teach Yourself SQL in 24 Hours, Third Edition
By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Appendix C.  Answers to Quizzes and Exercises


Hour 16, "Using Indexes to Improve Performance"

Quiz Answers

1:

What are some major disadvantages of using indexes?

A1:

Major disadvantages of an index include slowing batch jobs, storage space on the disk, and maintenance upkeep on the index.

2:

Why is the order of columns in a composite important?

A2:

Because query performance is improved by putting the column with the most restrictive values first.

3:

Should a column with a large percentage of NULLs be indexed?

A3:

No. A column with a large percentage of NULLs should not be indexed, because the speed of accessing these rows degrades when the value of a large percentage of rows is the same.

4:

Is the main purpose of an index to stop duplicate values in a table?

A4:

No. The main purpose of an index is to enhance data retrieval speed, although a unique index stops duplicate values in a table.

5:

True or false: The main reason for a composite index is for aggregate function usage in an index.

A5:

False. The main reason for composite indexes is for two or more columns in the same table to be indexed.

Exercise Answers

1:

For the following situations, decide whether an index should be used and, if so, what type of index should be used.

  1. Several columns, but a rather small table

  2. Medium- sized table, no duplicates should be allowed

  3. Several columns, very large table, several columns used as filters in the WHERE clause

  4. Large table, many columns, a lot of data manipulation

A1:
  1. Several columns, but a rather small table.

    Being a very small table, no index is needed.

  2. Medium-sized table, no duplicates should be allowed.

    A unique index could be used.

  3. Several columns, very large table, several columns are used as filters in the WHERE clause.

    A composite index on the columns used as filter in the WHERE clause should be the choice.

  4. Large table, many columns, lots of data manipulation.

    A choice of a single-column or composite index should be considered , depending on filtering, ordering, and grouping. For the large amount of data manipulation, the index could be dropped and re-created after the INSERT, UPDATE, or DELETE jobs were done.

2:

Type the following code into the mysql> prompt to create an index on the EMPLOYEE_PAY_TBL table on the POSITION column:

 CREATE INDEX EP_POSITION ON EMPLOYEE_PAY_TBL (POSITION); 
3:

Study the tables used in this book. What are some good candidates for indexed columns based on how a user might search for data?

A3:
 EMPLOYEE_TBL.LAST_NAME  EMPLOYEE_TBL.FIRST_NAME EMPLOYEE_TBL.EMP_ID EMPLOYEE_PAY_TBL.EMP_ID EMPLOYEE_PAY_TBL.POSITION CUSTOMER_TBL.CUST_ID CUSTOMER_TBL.CUST_NAME ORDERS_TBL.ORD_NUM ORDERS_TBL.CUST_ID ORDERS_TBL.PROD_ID ORDERS_TBL.ORD_DATE PRODUCTS_TBL.PROD_ID PRODUCTS_TBL.PROD_DESC 
4:

Create a multi-column index on the ORDERS_TBL table. Include the following columns: CUST_ID, PROD_ID, and ORD_DATE.

A4:
 CREATE INDEX ORD_IDX ON ORDERS_TBL (CUST_ID, PROD_ID, ORD_DATE); 
5:

Create some additional indexes on your tables as desired.


Team-Fly    
Top
 


Sams Teach Yourself SQL in 24 Hours
Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)
ISBN: 0672335417
EAN: 2147483647
Year: 2002
Pages: 275

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net