MYSQL MCQ and Answers – Query optimization

admin July 29, 2021October 9, 2021 0 Comments

MYSQL MCQs questions with answers to prepare for exams, tests, and certifications. These questions are taken from a real written exam and some parts are taken from an interview. So you will find questions on MySQL Database, SQL query, Data Model, and more. This MCQ will easily prepare anyone to pass their exam.

1. To check how MySQL will execute a SELECT query, which statement is used?

A TELL

B SHOW

C DISPLAY

D EXPLAIN

In MySQL by executing the EXPLAIN statement, MySQL displays information about the execution of a SELECT query without executing it. It is prefixed with the query.

2. To perform the analysis of the key values by the server, the instruction ______ is used.

A ANALYZE KEYS

B ANALYZE TABLE

C PERFORM ANALYSIS

D PERFORM TABLE ANALYSIS

In MySQL, for MyISAM and InnoDB tables, the server can be requested to perform key value analysis by executing the ANALYZE TABLE statement. It helps to optimize queries.

The above command parses and stores the table key. During the analysis, the table is locked for reading.

3. The “optimizer_prune_level” parameter is set by default?

A True

B False

The “optimizer_prune_level” parameter is enabled by default. This variable tells the optimizer to ignore certain plans based on the estimated number of rows accessed for each table.

This option can be disabled (optimizer_prune_level = 0)

4. Which system variable indicates how far should the optimizer look, in the rest of each incomplete plan, to determine whether it needs to be extended further?

A optimizer_prune_level

B optimizer_search_depth

C optimizer_search

D optimizer_prune

In MySQL, the system variable “optimizer_search_depth” indicates how far the optimizer should search to evaluate whether it should be extended further.

The default value is 62. The planner generates several plans, but sometimes it also generates an incomplete plan.

If we set a higher value (max 63), the query optimizer tries to evaluate all incomplete plans that take longer to execute. If we set a lower value, the query optimizer may ignore some plans.

5. Which system variable tells the optimizer to ignore certain plans based on the estimated number of rows accessed for each table?

A optimizer_prune_level

B optimizer_search_depth

C optimizer_search

D optimizer_prune

The optimizer_prune_level variable tells the optimizer to ignore certain plans based on the estimated number of rows accessed for each table.

6. What are the results of the following queries if ‘col’ is a column declared INTEGER?

SELECT * FROM ma_table WHERE col = '8';
SELECT * FROM ma_table WHERE col = 8;

A Both are the same

B The two are different

C The first request produces an error

D The second request produces an error

The first request calls a type conversion. This implies some performance degradation.

7. Which of the following WHERE clauses is faster?

WHERE col * 2 < 4
WHERE col < 4 / 2

A The first request

B The second request

C Both have the same speed

D Depends on the operating system

For the first query, MySQL will get the value of the column ‘col’ for each row, multiply by 2, and then compare the result. In this case, no index can be used and therefore it is slower.

8. Which of these comparisons is slow?

A INT/INT

B INT/BIGINT

C BIGINT/BIGINT

D All have the same speed

When comparing indexed columns, identical data types will give better performance than different types. An INT / INT or BIGINT / BIGINT comparison is therefore faster than an INT / BIGINT comparison.

9. Which statement is used to force the optimizer to use tables in a particular order?

A FORCE INDEX

B USE INDEX

C IGNORE INDEX

D STRAIGHT_JOIN

STRAIGHT_JOIN is used to force the optimizer to use tables in a particular order. The default MySQL optimizer considers itself free to determine the order in which to parse the tables most quickly.

10. Which instruction is used to check the operation of the optimizer?

A ANALYZE

B VERIFY

C EXPLAIN

D SHOW

EXPLAIN statement in MySQL can indicate whether indexes are used or not. This information is useful when there are different ways to write a statement that need to be tested.

1. To check how MySQL will execute a SELECT query, which statement is used?

2. To perform the analysis of the key values by the server, the instruction ______ is used.

3. The “optimizer_prune_level” parameter is set by default?

4. Which system variable indicates how far should the optimizer look, in the rest of each incomplete plan, to determine whether it needs to be extended further?

5. Which system variable tells the optimizer to ignore certain plans based on the estimated number of rows accessed for each table?

6. What are the results of the following queries if ‘col’ is a column declared INTEGER?

7. Which of the following WHERE clauses is faster?

8. Which of these comparisons is slow?

9. Which statement is used to force the optimizer to use tables in a particular order?

10. Which instruction is used to check the operation of the optimizer?

You May Also Like

MySQL Practice Exercises with Solutions – Part 5

MYSQL MCQ and Answers – Part 5

MYSQL MCQ and Answers – Part 4

Leave a Reply Cancel reply