This set of MySQL Database Multiple Choice Questions & Answers (MCQs) focuses on “Administrative-Level Optimizations”.
1. Accessing data from which of these is faster?
c) same speed
d) machine dependent
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?
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?
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 _________________
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 _________________
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.
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 _________________
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?
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?
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.
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.