MySQL Questions and Answers – Using Indexing

This set of MySQL Database Multiple Choice Questions & Answers (MCQs) focuses on “Using Indexing”.

1. Using indexes optimizes query performance.
a) True
b) False
View Answer

Answer: a
Explanation: 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
View Answer

Answer: d
Explanation: 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
View Answer

Answer: a
Explanation: 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.
advertisement
advertisement

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
View Answer

Answer: b
Explanation: 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 __________

Note: Join free Sanfoundry classes at Telegram or Youtube
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
View Answer

Answer: d
Explanation: 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.
advertisement

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
View Answer

Answer: b
Explanation: 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.
advertisement

7. If a column contains 7 values and 6 distinct values, the cardinality is __________
a) 7
b) 6
c) 13
d) 1
View Answer

Answer: b
Explanation: 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
View Answer

Answer: d
Explanation: 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
View Answer

Answer: a
Explanation: 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
View Answer

Answer: b
Explanation: 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.

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.