Solution: Slow Queries not using Indexes

There are 2 different ways to solve this task.

The first way is by enabling the variable log_queries_not_using_indexes (MariaDB/MySQL). This can be done eigther online:

sql> SHOW GLOBAL VARIABLES LIKE 'log_quer%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+

sql> SET GLOBAL log_queries_not_using_indexes = ON;

or offline in the my.cnf configuration file:

[mysqld]
log_queries_not_using_indexes = 1

As soon as this configuration is active your MariaDB/MySQL database server will write all queries not using an index into the Slow Query Log file which its location you can find as follows:

sql> SHOW GLOBAL VARIABLES WHERE variable_name IN ('log_output')
 OR variable_name LIKE 'slow_query%';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| log_output          | FILE     |
| slow_query_log      | OFF      |
| slow_query_log_file | slow.log |
+---------------------+----------+

The second way is to look into the PERFORMANCE_SCHEMA:

sql> use PERFORMANCE_SCHEMA;

sql> SHOW GLOBAL VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

sql> SELECT * FROM setup_consumers WHERE name LIKE 'events_statements%';
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_statements_current      | YES     |
| events_statements_history      | NO      |
| events_statements_history_long | NO      |
+--------------------------------+---------+

sql> SELECT sql_text, current_schema FROM events_statements_current WHERE no_index_used = 1 OR no_good_index_used = 1;
+------------------------------------------------------------------------------------------------------------------+--------------------+
| sql_text                                                                                                         | current_schema     |
+------------------------------------------------------------------------------------------------------------------+--------------------+
| SELECT sql_text, current_schema FROM events_statements_current WHERE no_index_used = 1 OR no_good_index_used = 1 | performance_schema |
+------------------------------------------------------------------------------------------------------------------+--------------------+