MySQL Questions and Answers – MySQL Query Optimizer

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

Answer: d
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

Answer: b
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

Answer: c
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.
advertisement
advertisement

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

Answer: d
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

Answer: b
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.
Note: Join free Sanfoundry classes at Telegram or Youtube

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

Answer: b
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.
advertisement

7. What are the results of the following SQL commands if col is an integer column?

advertisement
        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

Answer: a
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

Answer: a
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

Answer: b
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

Answer: a
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.

If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]

advertisement
advertisement
Subscribe to our Newsletters (Subject-wise). Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!

Youtube | Telegram | LinkedIn | Instagram | Facebook | Twitter | Pinterest
Manish Bhojasia - Founder & CTO at Sanfoundry
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.

Subscribe to his free Masterclasses at Youtube & discussions at Telegram SanfoundryClasses.