MYSQL MySQL 5.0 Database Administrator Certified Professional Exam, Part II Sample Questions:

1. Is it a good idea to do periodic table rebuilds for InnoDB tables? Why?

A) Yes, because the table may become fragmented after many INSERTs and DELETEs.
B) Yes, because it flushes the logs and frees memory.
C) No, because the indexes have to be rebuilt.
D) No, because there is a risk for data corruption.

2. Consider the following EXPLAIN output. mysql> EXPLAIN SELECT Name FROM City WHERE Population > 8000000 *************************** 1. row *************************** id: 1 select_type: SIMPLE table: City type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4079 Extra: Using where
Which of the following statements are true? (Choose three)

A) The Name column should be indexed.
B) The Population column should be indexed.
C) No index could be used to perform the query.
D) All 4079 rows of the table need to examined.
E) The query cannot be optimized any better.

3. Consider the following: mysql> EXPLAIN SELECT DISTINCT City.id,City.name -> FROM City,Country -> WHERE Country.Name IN ('United States','Canada','Mexico') -> AND City.CountryCode=Country.Code -> ORDER BY name *************************** 1. row *************************** id: 1 select_type: SIMPLE table: City type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4079 Extra: Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: Country type: eq_ref possible_keys: PRIMARY key: PRIMARY
key_len: 3
ref: world.City.CountryCode
rows: 1
Extra: Using where;
Distinct
Which of the following best describes the meaning of the values in the ref columns?

A) No indexed columns are used to choose rows from City and the City.CountryCode column is used to reference rows in the Country table.
B) No index columns are used to choose rows from Country and the City.CountryCode column is used to reference rows in the City table.
C) City.CountryCode is used to sort what rows are returned from the City table.

4. Should all queries in the slow query log be optimized? (Choose two)

A) No; A query may have been entered into the slow query log because other, slow queries, prevented it from executing quickly
B) No; Queries that are run rarely are probably not worth the effort of optimization
C) No; Only the very slowest queries should be optimized
D) Yes.
E) Yes, unless they depend on tables in the mysql database

5. Is it ever safe to start the MySQL server as root?

A) Yes provided you make it switch the effective user to mysql.
B) No it is never safe to start it as root.
C) Yes it is always safe to start it as root.
D) No because you can not make it switch the effective user if started as root.

Solutions: