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
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
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.
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
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
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.
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
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
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.
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
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
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.
9. Which mode tells cache queries that begin with SELECT SQL_CACHE?
a) 0
b) 1
c) 2
d) 3
View Answer
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
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.
- Practice Programming MCQs
- Check MySQL Books
- Check Information Technology Books
- Apply for Programming Internship
