How to restrict mySQL queries to not affect server and take it down?

There are times that some heavy queries increase server load and after sometime both CPU and Memory reaches 100%. This is my.cnf configuration file: [mysqld] expire_logs_days= 2 pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/storage/mysql # LOGGING # log-error = /var/storage/mysql/mysql-error.log log-queries-not-using-indexes = 1 slow-query-log = 1 slow-query-log-file = /var/storage/mysql/mysql-slow.log # MyISAM # key-buffer-size = 32M myisam-recover-options = FORCE,BACKUP # SAFETY # max-allowed-packet = 16M max-connect-errors = 1000000 # BINARY LOGGING # log-bin = /var/storage/mysql/mysql-bin expire-logs-days = 14 sync-binlog = 1 # CACHES AND LIMITS # tmp-table-size = 32M max-heap-table-size = 32M query-cache-type = 0 query-cache-size = 0 max-connections = 500 thread-cache-size = 50 open-files-limit = 65535 table-definition-cache = 1024 table-open-cache = 2048 innodb_file_per_table innodb_flush_method=O_DIRECT innodb-log-files-in-group = 2 innodb_log_file_size=500M innodb_buffer_pool_size=6G innodb_flush_log_at_trx_commit=2 # recently added for compression, omit it if mysql has problems with it innodb_file_format=Barracuda # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 collation-server = utf8mb4_general_ci init-connect='SET NAMES utf8mb4' character-set-server = utf8mb4 skip-character-set-client-handshake [client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 The server has 6 Cores with 16 GB of RAM. Any suggestion to restrict MySQL and make it safe? ---------- **EDIT-1** This is one of the slow queries that took 4.3 Secs from MySQL to return the data and has examined about 3 Million rows: # Time: 190107 6:22:30 # User@Host: root[root] @ localhost [127.0.0.1] # Thread_id: 204732 Schema: my_db QC_hit: No # Query_time: 4.306051 Lock_time: 0.000058 Rows_sent: 0 Rows_examined: 3253235 # Rows_affected: 0 SET timestamp=1546842150; SELECT ((old_credit + (1*amount))/10) credit FROM credits WHERE user_id=' ' or user_id=(SELECT user_id FROM accounts WHERE email=' ') ORDER BY id DESC LIMIT 1; The query is created by a front-end BI application (it can be changed to some extent). Indexes of the table is: MariaDB [my_db]> show index from credits; +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | credits | 0 | PRIMARY | 1 | id | A | 2014915 | NULL | NULL | | BTREE | | | | credits | 1 | credit_id | 1 | credit_id | A | 2014915 | NULL | NULL | YES | BTREE | | | | credits | 1 | user_id | 1 | user_id | A | 134327 | NULL | NULL | YES | BTREE | | | +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec) ---------- **EDIT-2:** Database changed MariaDB [my_db]> show index from accounts; +-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | accounts | 0 | PRIMARY | 1 | id | A | 486034 | NULL | NULL | | BTREE | | | | accounts | 1 | user_id | 1 | user_id | A | 486034 | NULL | NULL | | BTREE | | | | accounts | 1 | email_idx | 1 | email | A | 486034 | 190 | NULL | | BTREE | | | | accounts | 1 | user_id_email_phone | 1 | user_id | A | 486034 | NULL | NULL | | BTREE | | | | accounts | 1 | user_id_email_phone | 2 | email | A | 486034 | 40 | NULL | | BTREE | | | | accounts | 1 | user_id_email_phone | 3 | phone | A | 486034 | 15 | NULL | YES | BTREE | | | +-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 6 rows in set (0.00 sec)