| 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. -
Several columns, but a rather small table -
Medium- sized table, no duplicates should be allowed -
Several columns, very large table, several columns used as filters in the WHERE clause -
Large table, many columns, a lot of data manipulation | A1: | -
Several columns, but a rather small table. Being a very small table, no index is needed. -
Medium-sized table, no duplicates should be allowed. A unique index could be used. -
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. -
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 | | |