Explore topic-wise InterviewSolutions in .

Correct ANSWER is (a) True

To explain: In MYSQL, avoiding the NULL in columns may make the queries simpler, since it is not required to check for NULL as a special case. Simpler queries generally can be PROCESSED more quickly.

The correct option is (d) OPTIMIZE TABLE

Best explanation: In the VARIABLE LENGTH ROWS, there is more FRAGMENTATION of the table on which many DELETES or updates are performed. OPTIMIZE TABLE is run periodically to maintain performance.

Correct choice is (a) INET_ATON()

The EXPLANATION: In MYSQL, the function used to convert a STRING to an integer is INET_ATON(). On the other hand, the function INET_NTOA() converts a string to the corresponding integer VALUE.

The correct option is (a) True

To explain I would say: Numbers can be compared in a single operation while STRING COMPARISONS INVOLVE several byte by byte or character by character comparisons. As the strings BECOME longer, the performance gets poor.

Right option is (a) True

Best explanation: In MYSQL, the optimizer_prune_level is on by default. This variable tells the OPTIMIZER to skip certain plans based on ESTIMATES of the NUMBER of rows ACCESSED for each table.

The correct option is (B) optimizer_search_depth

For explanation: In MySQL, the optimizer_search_depth SYSTEM variable tells how FAR into the “future” of each incomplete plan the optimizer should look to EVALUATE whether it should be EXPANDED further.

The correct OPTION is (B) INT/BIGINT

Explanation: On COMPARING indexed columns, identical data TYPES will give better performance than dissimilar types. So an INT/INT or BIGINT/BIGINT comparison is faster than an INT/BIGINT comparison.

Right answer is (d) STRAIGHT_JOIN

For explanation I would SAY: STRAIGHT_JOIN is used to force the optimizer to use TABLES in a particular ORDER. The MYSQL optimizer by default considers itself free to DETERMINE the order in which to scan tables most quickly.

Right choice is (b) ANALYZE TABLE

Easy explanation: In MySQL, for the MyISAM and InnoDB tables, the server can be TOLD to PERFORM an ANALYSIS of key values by issuing the ANALYZE TABLE STATEMENT. It helps in knowing about query optimization.

Right option is (d) EXPLAIN

Explanation: In MySQL, by issuing an EXPLAIN STATEMENT MySQL DISPLAYS some information about how it would execute a SELECT query without actually executing it. It is prefixed with the query.

Right option is (b) TEXT

Best 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.

Correct answer is (a) True

To ELABORATE: 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.

Right answer is (d) 4

To elaborate: 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.

Correct CHOICE is (a) data file

The EXPLANATION is: 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.

Correct choice is (d) more than 1

To elaborate: 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.

Correct choice is (a) True

To explain I WOULD say: 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.