1Z0-909 OCP MySQL 8.0 Database Developer Practice Questions

Using index means the query is performing an index scan. Using where means that after fetching the rows via the index, the storage engine still needs to apply a WHERE clause filter to them because the index couldn't resolve all conditions. This is different from a covering index (Using index alone), where the index contains all necessary data and no table access is needed. It's a reasonably efficient access method, but it's not a covering index.

utf8mb4 is the recommended character set for storing Unicode data in MySQL 8.0. It supports the full Unicode standard, including 4-byte characters like emojis. The older utf8 (now an alias for utf8mb3) only supports up to 3-byte characters and cannot store emojis. The TEXT type is appropriate for storing long description strings that may exceed the length limits of VARCHAR.

A SAVEPOINT creates a named marker within the current transaction. This allows you to use ROLLBACK TO SAVEPOINT savepoint_name to undo all changes made since that savepoint was set, without aborting the entire transaction. This is useful for complex operations where you might want to retry a sub-task without losing all the work done earlier in the transaction.

A function should be declared as DETERMINISTIC if it always produces the same result for the same input parameters. This is a hint to the optimizer. If a function is deterministic, a call with a given set of arguments can be replaced with a constant value if it's called multiple times within a single query. A customer lifetime value function that only queries historical data is deterministic. Declaring it as such can lead to performance improvements.

This statement is false. For a composite (multi-column) index, the query optimizer can only use the index if the WHERE clause includes a condition on the left-most prefix of the index columns. For an index on (A, B), the optimizer can use it for conditions on A or on A AND B. It cannot use the index to search for conditions on B alone.

Changing the isolation level to READ COMMITTED for these specific transactions is the best solution. REPEATABLE READ uses gap locks, which can lock the space between index records, leading to a higher chance of deadlocks when inserting into a sequentially indexed column. READ COMMITTED does not use gap locks for ordinary statements, which significantly reduces the likelihood of this type of deadlock. Switching to SERIALIZABLE would worsen the problem by increasing locking. Using LOCK TABLES is too coarse and would serialize access, killing concurrency. Retrying transactions is a valid strategy but doesn't solve the root cause of the frequent deadlocks.

JSON_MERGE_PATCH is the correct function. It follows RFC 7396 semantics, where it recursively merges JSON documents. It updates existing key values and adds new key-value pairs. In this case, it would update theme.color and add notifications.enabled. JSON_SET can update existing values and add new ones, but requires specifying each path and value pair individually, making it less concise for merging objects. JSON_REPLACE only updates existing values and does not add new ones. JSON_MERGE_PRESERVE would create an array if keys conflict, which is not the desired behavior here.

The HAVING clause is used to filter groups after aggregation has been performed, whereas the WHERE clause filters rows before aggregation. To filter based on the result of an aggregate function like SUM(), you must use HAVING. The alias total_sales cannot be used in the WHERE clause of the same query level where it is defined.

Applying a function like SUBSTRING() or INSTR() to an indexed column in the WHERE clause prevents the MySQL optimizer from using the index on that column. This makes the predicate non-SARGable (Search-Argument-able). To make the query use the index, the WHERE clause should be rewritten to compare the column directly, for example, WHERE email LIKE '%@example.com'. Note that even with LIKE, a leading wildcard (%) can also prevent index usage, but the function application is the definite cause here.

The OUT parameter mode is used for parameters that the procedure will set and return to the caller. Since the procedure needs to return the department name and manager's name, these should be OUT parameters.

While using OUT parameters is one way to return values, a common and often preferred method for returning tabular data (even a single row with multiple columns) is to have the stored procedure execute a SELECT statement. This produces a result set that the calling application can then process.