MySQL Database Multiple Choice Questions 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
Answer: a
Clarification: 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
Answer: a
Clarification: 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
Answer: b
Clarification: 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
Answer: b
Clarification: 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
Answer: a
Clarification: 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
Answer: b
Clarification: 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
Answer: a
Clarification: 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
Answer: a
Clarification: 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
Answer: c
Clarification: 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
Answer: a
Clarification: 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.