Monitoring and profiling in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to show the current running processes in MySQL.

MySQL

SHOW [1];

Drag options to blanks, or click blank then click option'

ATABLES

BPROCESSLIST

CDATABASES

DSTATUS

Attempts:

3 left

💡 Hint

Common Mistakes

Using SHOW TABLES instead of SHOW PROCESSLIST

Using SHOW DATABASES which lists databases, not processes

The SHOW PROCESSLIST; command displays the currently running threads in MySQL, which helps monitor active queries and connections.

2fill in blank

medium

Complete the code to enable the slow query log in MySQL.

MySQL

SET GLOBAL [1] = 'ON';

Drag options to blanks, or click blank then click option'

Alog_output

Bgeneral_log

Cslow_query_log

Dlog_error

Attempts:

3 left

💡 Hint

Common Mistakes

Enabling general_log instead of slow_query_log

Trying to set log_output to ON which expects a string like 'FILE'

Setting slow_query_log to ON enables logging of queries that take longer than the defined threshold, useful for profiling slow queries.

3fill in blank

hard

Fix the error in the command to show the slow query log file location.

MySQL

SHOW VARIABLES LIKE '[1]';

Drag options to blanks, or click blank then click option'

Aslow_query_log_file

Bslow_log_file

Cslowlog_file

Dslow_query_file

Attempts:

3 left

💡 Hint

Common Mistakes

Using incorrect variable names like slow_log_file

Misspelling the variable name

The correct variable name is slow_query_log_file, which shows the path to the slow query log file.

4fill in blank

hard

Fill both blanks to create a query that shows the top 5 longest running queries from the slow query log summary table.

MySQL

SELECT sql_text, [1] FROM mysql.slow_log ORDER BY [2] DESC LIMIT 5;

Drag options to blanks, or click blank then click option'

Astart_time

Bquery_time

Cend_time

Dlock_time

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by start_time instead of query_time

Selecting end_time instead of start_time

The start_time shows when the query began, and ordering by query_time descending lists the longest running queries first.

5fill in blank

hard

Fill all three blanks to create a query that counts queries grouped by user and orders by count descending.

MySQL

SELECT [1], COUNT(*) AS [2] FROM mysql.general_log WHERE command_type = 'Query' GROUP BY [3] ORDER BY [2] DESC;

Drag options to blanks, or click blank then click option'

Auser_host

Bquery_count

Dcommand_type

Attempts:

3 left

💡 Hint

Common Mistakes

Using command_type in GROUP BY instead of user_host

Not aliasing the count or ordering incorrectly

The query groups by user_host to count queries per user. The count is aliased as query_count and ordered descending to show the most active users.