MySQL Database Multiple Choice Questions on “Using Indexing”.
1. Using indexes optimizes query performance.
a) True
b) False
Answer: a
Clarification: In MySQL, there are many techniques available for optimizing queries. Indexing is the most useful. Generally the one thing that makes the most difference is the proper use of indexes.
2. The maximum number of indexes on MyISAM table is __________
a) 0
b) 1
c) 2
d) more than 1
Answer: d
Clarification: In MySQL, for a MyISAM table, there can be more than one indexes. The data rows of the MyISAM table are stored in a data file and the index values are stored in a separate index file.
3. The data rows of a MyISAM table are stored in __________
a) data file
b) index file
c) log file
d) error file
Answer: a
Clarification: The data rows of the MyISAM table are stored in a data file and the index values are stored in a separate index file. In MySQL, for a MyISAM table, there can be more than one indexes.
4. In a single table (with 10 columns) join query the number of values needed to examine per column is __________
a) 1
b) 10
c) 0
d) 2
Answer: b
Clarification: For indexing in the context of performing joins, in a single table query, the number of values that are needed to be examined per column is the number of rows in the table.
5. Suppose tbl_1 is a table consisting of 8 rows and tbl_2 is a table consisting of 6 rows, the number of combinations through which the search is performed is __________
SELECT tbl_1.i1, tbl_2.i2 FROM tbl_1 INNER JOIN tbl_2 WHERE tbl_1.i1 = tbl_2.i2;
a) 14
b) 2
c) 1
d) 48
Answer: d
Clarification: In MySQL, when the tables are unindexed, the searches are run through all the possible combinations. In this case, the search space is 8 x 6 = 48, the product of the number of rows.
6. How many of the following shared tablespaces is bound by the OS file size limit?
MyISAM, InnoDB, TRANSACTION
a) 0
b) 1
c) 2
d) 3
Answer: b
Clarification: In MySQL, all the MyISAM tables shared tablespace files size if bound by the operating system file size limits. For InnoDB tables shared tablespaces, it is not bound by this factor.
7. If a column contains 7 values and 6 distinct values, the cardinality is __________
a) 7
b) 6
c) 13
d) 1
Answer: b
Clarification: In MySQL, the cardinality of a column is the number of distinct values that it contains. A column that contains the values 1, 2, 3, 4, 4, 5, 6 has a cardinality of six and not seven.
8. For a table having 4 columns, the number of columns to which hash function is applied when using hash indexes is __________
a) 1
b) 2
c) 3
d) 4
Answer: d
Clarification: In MySQL, for the hash indexes, a hash function is applied to each of the column values. The resulting hash values are stored in the index and used to perform lookups on it.
9. The slow query log can be used to identify queries that may be performing badly.
a) True
b) False
Answer: a
Clarification: In MySQL, the slow query log is used to identify queries that may be performing badly. This log can help find the queries that might benefit from indexing. The slow query log is written as text.
10. The slow query log is written as __________
a) numbers
b) text
c) image
d) graph
Answer: b
Clarification: In MySQL, the slow query log is written as text, so it is viewable with any file display program. The mysqldumpslow utility can also be used to summarize the contents of the same.