This set of MySQL Database Multiple Choice Questions & Answers (MCQs) focuses on “MySQL Query Optimizer”.
1. To check how MySQL would execute a SELECT query, which statement is used?
a) TELL
b) SHOW
c) DISPLAY
d) EXPLAIN
View Answer
Explanation: In MySQL, by issuing an EXPLAIN statement MySQL displays some information about how it would execute a SELECT query without actually executing it. It is prefixed with the query.
2. To perform analysis of key values by the server, the statement used is __________
a) ANALYZE KEYS
b) ANALYZE TABLE
c) PERFORM ANALYSIS
d) PERFORM TABLE ANALYSIS
View Answer
Explanation: In MySQL, for the MyISAM and InnoDB tables, the server can be told to perform an analysis of key values by issuing the ANALYZE TABLE statement. It helps in knowing about query optimization.
3. Which statement is used to verify optimizer operation?
a) ANALYZE
b) VERIFY
c) EXPLAIN
d) SHOW
View Answer
Explanation: The EXPLAIN statement in MySQL can tell whether the indexes are being used or not. This information is helpful when different ways of writing a statement need to be tested.
4. Which statement is used to force the optimizer to use tables in a particular order?
a) FORCE INDEX
b) USE INDEX
c) IGNORE INDEX
d) STRAIGHT_JOIN
View Answer
Explanation: STRAIGHT_JOIN is used to force the optimizer to use tables in a particular order. The MySQL optimizer by default considers itself free to determine the order in which to scan tables most quickly.
5. Which of these comparisons is slowest?
a) INT/INT
b) INT/BIGINT
c) BIGINT/BIGINT
d) All are of same speed
View Answer
Explanation: On comparing indexed columns, identical data types will give better performance than dissimilar types. So an INT/INT or BIGINT/BIGINT comparison is faster than an INT/BIGINT comparison.
6. Which of the following WHERE clauses are faster?
1. WHERE col * 3 < 9 2. WHERE col < 9 / 3
a) 1
b) 2
c) same speed
d) dependent on operating system
View Answer
Explanation: For the first variation, MySQL would retrieve the value of ‘col’ for each row, multiply by three, and then compare the result to nine. In this case, no index can be used and hence it is slower.
7. What are the results of the following SQL commands if col is an integer column?
1. SELECT * FROM mytbl WHERE num_col = '4'; 2. SELECT * FROM mytbl WHERE num_col = 4;
a) same
b) different
c) 1 is an error
d) 2 is an error
View Answer
Explanation: The first query invokes a type conversion. The conversion operation involves some performance penalty for converting the integer and string to double to perform the comparison.
8. Which system variable tells the optimizer to skip certain plans based on estimates of the number of rows accessed for each table?
a) optimizer_prune_level
b) optimizer_search_depth
c) optimizer_search
d) optimizer_prune
View Answer
Explanation: The optimizer_prune_level variable tells the optimizer to skip certain plans based on estimates of the number of rows accessed for each table. This option is kept on by default.
9. Which system variable tells how far into the rest of each incomplete plan the optimizer should look to evaluate whether it should be expanded further?
a) optimizer_prune_level
b) optimizer_search_depth
c) optimizer_search
d) optimizer_prune
View Answer
Explanation: In MySQL, the optimizer_search_depth system variable tells how far into the “future” of each incomplete plan the optimizer should look to evaluate whether it should be expanded further.
10. The optimizer_prune_level is set by default.
a) True
b) False
View Answer
Explanation: In MySQL, the optimizer_prune_level is on by default. This variable tells the optimizer to skip certain plans based on estimates of the number of rows accessed for each table.
Sanfoundry Global Education & Learning Series – MySQL Database.
To practice all areas of MySQL Database, here is complete set of 1000+ Multiple Choice Questions and Answers.
- Check Information Technology Books
- Apply for Programming Internship
- Check MySQL Books
- Practice Programming MCQs