Home arrow MySQL arrow Page 5 - SQL Performance and Tuning Considerations, concluded

Quiz - MySQL

This article, the second of two parts, will show you how to make the SQL statements you write run faster and more efficiently. It is excerpted from chapter 11 of the book SQL DeMYSTiFied, written by Andy Oppel (McGraw-Hill/Osborne, 2005; ISBN: 0072262249).

TABLE OF CONTENTS:
  1. SQL Performance and Tuning Considerations, concluded
  2. Using EXPLAIN PLAN
  3. Microsoft SQL Server Considerations
  4. Displaying Execution Plans Using SQL Query Analyzer
  5. Quiz
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 10
March 23, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Choose the correct responses to each of the multiple-choice questions. Note that there may be more than one correct response to each question.

  1. Performance requirements

    a. Should be set after the SQL statements are tuned

    b. Provide a way to identify statements that need tuning

    c. Are best when they contain complex criteria

    d. Provide a way to know when to stop tweaking a query

    e. Are developed just to make the auditors happy
  2. Disk reads and writes can be minimized by

    a. Allocating buffers of sufficient size

    b. Placing all the database files on one disk drive

    c. Putting the entire database in memory

    d. Adding indexes for all the important table columns

    e. Spreading files across all available disk drives
  3. The computer system can be tuned by

    a. Collecting database statistics

    b. Applying available security patches

    c. Selecting fast and reliable hardware

    d. Following the DBMS tuning recommendations

    e. Consulting a tuning guide for the operating system
  4. Efficient table design includes

    a. Using VARCHAR for all variable-length character columns

    b. Using the smallest possible numeric data type that holds the data values

    c. Using triggers whenever possible

    d. Using identical data types for primary keys and their matching foreign keys

    e. Using identical data types for all primary key columns
  5. A query execution plan

    a. Describes how the DBMS will execute a query

    b. Is stored in the SQL cache

    c. Is created using the explain plan feature of the RDBMS

    d. Requires a plan table to hold the explain results

    e. Requires the use of a stored procedure
  6. The query optimizer

    a. Creates a query execution plan in the plan table

    b. Determines the best way to execute an SQL statement

    c. May use statistics gathered from the database

    d. May use rules applied to the way the statement was written

    e. Can be cost-based or rule-based
  7. In order to design proper queries, the developer should

    a. Know the characteristics of the data in the database

    b. Match data types in predicates

    c. Use hints as much as possible

    d. Avoid unnecessary tables and columns

    e. Maximize the number of rows in each result set
  8. Table scans can be avoided by

    a. Including a WHERE clause that references an indexed column

    b. Using GROUP BY instead of DISTINCT

    c. Making sure that statistics are up to date

    d. Making at least one predicate references the leading column of an index

    e. Avoiding unnecessary columns
  9. An index cannot be used when

    a. The WHERE clause references the second column of an index

    b. A LIKE clause references a comparison string that contains a wildcard (except in the first position of the string)

    c. The NOT operator is used in a predicate

    d. An SQL function is included in a column comparison (except when they match a function-based index)

    e. The NOT EQUAL operator is used in a predicate
  10. Considerations for using indexes include

    a. Placing indexes on all frequently updated columns

    b. Placing indexes on foreign key columns

    c. Avoiding overlapping indexes

    d. Creating indexes on columns that have only a few possible values

    e. Avoiding unique indexes
  11. Tuning considerations for MySQL include

    a. Function-based indexes

    b. Hash indexes

    c. Clustering indexes

    d. Storage engine options

    e. Bit-map indexes
  12. Tuning considerations for Oracle include

    a. Function-based indexes

    b. Hash indexes

    c. Clustering indexes

    d. Storage engine options

    e. Bit-map indexes
  13. Tuning considerations for Microsoft SQL Server include

    a. Function-based indexes

    b. Hash indexes

    c. Clustering indexes

    d. Storage engine options

    e. Bit-map indexes
  14. An explain plan in Oracle

    a. Requires the use of a plan table

    b. Contains a PLAN_ID to uniquely identify it

    c. Can be viewed using Enterprise Manager

    d. Can be viewed with SQL by selecting it from the plan table

    e. Is created using the CREATE PLAN statement
  15. An execution plan in Microsoft SQL Server

    a. Requires the use of a plan table

    b. Can be displayed using an option in SQL Query Analyzer

    c. Displays the execution plan in a text format

    d. Displays the execution plan in a graphical format

    e. Can be viewed with SQL by selecting it from the plan table
  16. When tuning INSERT statements, one should consider

    a. Index maintenance

    b. Row expansion

    c. The CASCADE option

    d. Adequate free space

    e. Query rewrites 
  17. When tuning UPDATE statements, one should consider

    a. Index maintenance

    b. Row expansion

    c. The CASCADE option

    d. Adequate free space

    e. Query rewrites 
  18. When tuning DELETE statements, one should consider

    a. Index maintenance

    b. Row expansion

    c. The CASCADE option

    d. Adequate free space

    e. Query rewrites
  19. Data types should match

    a. For all primary key columns

    b. Between primary key and corresponding secondary key columns

    c. Between primary key and corresponding foreign key columns

    d. Between column values and literal values compared in predicates

    e. For all function-based indexes
  20. The most likely cause of query performance problems is

    a. A poorly tuned operating system

    b. A poorly written SQL statement

    c. Trigger overhead

    d. Index maintenance overhead

    e. Row expansion


 
 
>>> More MySQL Articles          >>> More By McGraw-Hill/Osborne
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: