*SQL table indexes for (little) performance gains

Hi,

I've tracked SQL slow queries and added "missing" indexes for performance gains:

create index parent_share_with_index on oc_share (parent,share_with);
create index share_type_share_type_item_type on oc_share(share_type,share_with,item_type)
create index occurred_subnet_action on oc_bruteforce_attempts (occurred,subnet,action);
create index last_checked_reserved_at on oc_jobs(last_checked,reserved_at);

Steps to reproduce

MariaDB [nextcloud]> explain SELECT * FROM `oc_bruteforce_attempts` WHERE (`occurred` > '1521656110') AND (`subnet` = '10.192.96.14/32') AND (`action` = 'login');
+------+-------------+------------------------+------+----------------------------+------+---------+------+------+-------------+
| id   | select_type | table                  | type | possible_keys              | key  | key_len | ref  | rows | Extra       |
+------+-------------+------------------------+------+----------------------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | oc_bruteforce_attempts | ALL  | bruteforce_attempts_subnet | NULL | NULL    | NULL | 1093 | Using where |
+------+-------------+------------------------+------+----------------------------+------+---------+------+------+-------------+


MariaDB [nextcloud]> create index occurred_subnet_action on oc_bruteforce_attempts (occurred,subnet,action);

MariaDB [nextcloud]> explain SELECT * FROM `oc_jobs` WHERE `reserved_at` <= 1521667487 ORDER BY `last_checked` ASC LIMIT 1;
+------+-------------+---------+-------+--------------------------------------+-------------+---------+------+------+---------------------------------------+
| id   | select_type | table   | type  | possible_keys                        | key         | key_len | ref  | rows | Extra                                 |
+------+-------------+---------+-------+--------------------------------------+-------------+---------+------+------+---------------------------------------+
|    1 | SIMPLE      | oc_jobs | range | reserved_at,reserved_at_last_checked | reserved_at | 5       | NULL |   92 | Using index condition; Using filesort |
+------+-------------+---------+-------+--------------------------------------+-------------+---------+------+------+---------------------------------------+
.


MariaDB [nextcloud]> explain SELECT * FROM `oc_bruteforce_attempts` WHERE (`occurred` > '1521656110') AND (`subnet` = '10.192.96.14/32') AND (`action` = 'login');
+------+-------------+------------------------+-------+---------------------------------------------------+------------------------+---------+------+------+-----------------------+
| id   | select_type | table                  | type  | possible_keys                                     | key                    | key_len | ref  | rows | Extra                 |
+------+-------------+------------------------+-------+---------------------------------------------------+------------------------+---------+------+------+-----------------------+
|    1 | SIMPLE      | oc_bruteforce_attempts | range | bruteforce_attempts_subnet,occurred_subnet_action | occurred_subnet_action | 4       | NULL |    1 | Using index condition |
+------+-------------+------------------------+-------+---------------------------------------------------+------------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

MariaDB [nextcloud]> create index last_checked_reserved_at on oc_jobs(last_checked,reserved_at);

Expected behaviour

MariaDB [nextcloud]> explain SELECT * FROM `oc_jobs` WHERE `reserved_at` <= 1521667487 ORDER BY `last_checked` ASC LIMIT 1;
+------+-------------+---------+-------+--------------------------------------+--------------------------+---------+------+------+-------------+
| id   | select_type | table   | type  | possible_keys                        | key                      | key_len | ref  | rows | Extra       |
+------+-------------+---------+-------+--------------------------------------+--------------------------+---------+------+------+-------------+
|    1 | SIMPLE      | oc_jobs | index | reserved_at,reserved_at_last_checked | last_checked_reserved_at | 10      | NULL |    1 | Using where |
+------+-------------+---------+-------+--------------------------------------+--------------------------+---------+------+------+-------------+


MariaDB [nextcloud]> explain SELECT * FROM `oc_bruteforce_attempts` WHERE (`occurred` > '1521656110') AND (`subnet` = '10.192.96.14/32') AND (`action` = 'login');
+------+-------------+------------------------+-------+---------------------------------------------------+------------------------+---------+------+------+-----------------------+
| id   | select_type | table                  | type  | possible_keys                                     | key                    | key_len | ref  | rows | Extra                 |
+------+-------------+------------------------+-------+---------------------------------------------------+------------------------+---------+------+------+-----------------------+
|    1 | SIMPLE      | oc_bruteforce_attempts | range | bruteforce_attempts_subnet,occurred_subnet_action | occurred_subnet_action | 4       | NULL |    1 | Using index condition |
+------+-------------+------------------------+-------+---------------------------------------------------+------------------------+---------+------+------+-----------------------+

The issue provides MariaDB CREATE INDEX statements and EXPLAIN output for oc_share, oc_bruteforce_attempts, and oc_jobs, but names no source files or tests. Start by locating the schema definitions for these tables and checking whether the indexes already exist; done means the required indexes are represented safely and the reported query plans improve without breaking supported database setups.