MYSQL 1Z0-874 : MySQL 5.0 Database Administrator Certified Professional Exam, Part II

In regards to security, which of the following statements are true? (Choose two)

• A. Mysqld should be run as root or administrator.
• B. The root or admininistrator users should own all files related to the server process
• C. The user owning the server process should own all files and directories to which the server may write.
• D. Mysqld should not be run as root or administrator.

To prevent users from seeing the password used for FEDERATED tables, you should... (Choose two)

• A. Not grant them privileges to execute SHOW CONNECTION STRING for this table.
• B. Not grant them privileges to execute SHOW CONNECTION for this table.
• C. Not grant them privileges to execute SHOW TABLE STATUS for this table.
• D. Not grant them privileges to execute SHOW CREATE TABLE for this table.

Which of the following are methods to improve performance? (Choose two)

• A. Spreading single tables to different disks pointed to by a symbolic link.
• B. Moving the MySQL utilities such as mysqladmin to different disks pointed to by a symbolic link.
• C. Spreading whole databases to different disks pointed to by a symbolic link.
• D. Moving less needed parts of the MySQL install such as the documentation or the test files to different disks pointed to by a symbolic link.

Given the following MyISAM table structure:
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | | PRI | NULL | auto_increment |
| Name | char(35) | | | | |
| CountryCode | char(3) | | | | |
| District | char(20) | | | | |
| Population | int(11) | | MUL | 0 | |
+-------------+----------+------+-----+---------+----------------+
And the following SQL statement: SELECT Population FROM city WHERE Population = 10000
ORDER BY Population LIMIT 5; which of the following statements best describes how MySQL
optimizer executes the query?

• A. The optimizer uses the index on the Population column to search, filter and sort the Population column, and returns 5 records to the client. The optimizer does not need to read the data rows, and can return values from the index only because only those columns where specified in the SELECT statement.
• B. The optimizer uses the index on the Population column to search and filter the WHERE clause. A temporary table is used to perform a filesort on the results, and then only 5 records are returned to the client.
• C. The optimizer uses the index on the Population column to search, filter and sort the Population column, then returns 5 records to the client. The optimizer does not need to read the data rows, and can return values from the index only, because the index contains just integer values that form a leftmost prefix for the key.
• D. The optimizer will never read data from disk, since MySQL caches both data and index in the key buffer.
• E. The optimizer uses the primary key column ID to read the index values, then uses the index on Population to filter the results. The optimizer will always choose to use a unique index first, then use a secondary index if available.

Consider the following:
mysql> SHOW STATUS LIKE 'Qcache%'; +-------------------------+--------+ | Variable_name | Value | +-------------------------+--------+ | Qcache_free_blocks | 87 | | Qcache_free_memory | 318442 | | Qcache_hits | 11224 | | Qcache_inserts | 19215 | | Qcache_lowmem_prunes | 437 | | Qcache_not_cached | 9155 | | Qcache_queries_in_cache | 240 | | Qcache_total_blocks | 924 | +-------------------------+--------+
What can be derived from this output?

• A. There is a high utilization of the query cache.
• B. There is a low utilization of the query cache.
• C. There is a normal utilization of the query cache.