MySQL Questions and Answers – Administrative-Level Optimizations

This set of MySQL Database Multiple Choice Questions & Answers (MCQs) focuses on “Administrative-Level Optimizations”.

1. Accessing data from which of these is faster?

memory, disk

a) memory
b) disk
c) same speed
d) machine dependent
View Answer

Answer: a
Explanation: In MySQL, the administrators have more privileges since they have more control of the MySQL server or the machine on which it runs. Accessing data in memory is faster than from disk.

2. Which system variable controls the size of the table cache?
a) table_cache
b) cache_table
c) open_cache
d) cache_open
View Answer

Answer: a
Explanation: In MySQL, when the server opens table files it keeps them open to minimize the number of file-opening operations. It maintains information about open files in the table cache.
advertisement
advertisement

3. Which status indicator assesses how effective the table cache is?
a) Open_tables
b) Opened_tables
c) Close_tables
d) Closed_tables
View Answer

Answer: b
Explanation: When the server accesses various tables, the table cache fills up. The server closes the tables that have not been used recently in order to make room for opening the new tables.

4. In MySQL, the default size of the key buffer in MB is _________________
a) 4
b) 8
c) 16
d) 32
View Answer

Answer: b
Explanation: The larger values allow MySQL to hold more index blocks in memory at once. This increases the chances of finding key values in memory without having to read a new block from disk.
Sanfoundry Certification Contest of the Month is Live. 100+ Subjects. Participate Now!

5. The system variable to maintain InnoDB log buffer size is _________________
a) innodb_log_buffer_size
b) innodb_buffer_log_size
c) buffer_log_innodb_size
d) log_buffer_innodb_size
View Answer

Answer: a
Explanation: The InnoDB storage engine has its own cache used for buffering the data and index values. It maintains a log buffer. The size for this is controlled by innodb_log_buffer_size.

6. All queries can be cached.
a) True
b) False
View Answer

Answer: b
Explanation: A query is not cached if the query returns non deterministic results. For example, a query that uses the NOW() function returns different results over time. Therefore, it cannot be cached.
advertisement

7. To determine whether query cache is supported by the server the variable is _________________
a) have_query_cache
b) have_cache_query
c) query_cache_have
d) cache_query_have
View Answer

Answer: a
Explanation: In MySQL, in order to determine whether a server supports the query cache or not, the value of the ‘have_query_cache’ system variable is checked. It returns ‘YES’ if support is available.

8. Which mode tells not to cache query results?
a) 0
b) 1
c) 2
d) 3
View Answer

Answer: a
Explanation: The have_query_cache system variable is used to check whether there is support for query cache. The mode ‘0’ tells not to cache the query results or to retrieve the cached results.
advertisement

9. Which mode tells cache queries that begin with SELECT SQL_CACHE?
a) 0
b) 1
c) 2
d) 3
View Answer

Answer: c
Explanation: The have_query_cache system variable is used to check whether there is support for query cache. The mode ‘2’ tells cache only those queries that begin with SELECT SQL_CACHE.

10. Installing more memory into the machine enables to configure larger values for cache server.
a) True
b) False
View Answer

Answer: a
Explanation: Installing more memory into the machine enables to configure larger values for the cache server and buffer sizes. This enables it to keep data in memory for longer duration.

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.