MariaDB - Should I add index to my table?

Question

Recently I was checking my system logs and I noticed some of my queries are very slow.

I have a table that stores user activites. The table structure is id (int), user (int), type (int), object (varchar), extra (mediumtext) and date (timestamp).

Also I only have index for id (BTREE, unique).

I have performance issues for following query;

SELECT  DISTINCT object as usrobj
    from  ".MV15_PREFIX."useractivities
    WHERE  user='".$user_id."'
      and  type = '3'
    limit  0,1000000" 

Question is, should I also index user same as id? What should be the best practise I should follow?

This table is actively used and has over 500k+ rows in it. And there are 2k~ concurrent users online average on site.

The reason I am asking this question is I am not really good at managing DB, and also I have slow query issue on another table which has proper indexes.

Thanks in advance for suggestions.

Side note:

Result of mysqltuner

General recommendations:

Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Consider installing Sys schema from https://github.com/mysql/mysql-sys

Variables to adjust:

max_connections (> 768)
wait_timeout (< 28800)
interactive_timeout (< 28800)
join_buffer_size (> 64.0M, or always use indexes with joins)

(I will set max_connections > 768, not really sure about timeouts and as far I read topics/suggestions in Stackoverflow I think I shouldn't increase the size of join_buffer_size but I'd really appreciate getting feedback about these variables too.)

EDIT - SHOW INDEX result;

+--------------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table              | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ***_useractivities |          0 | PRIMARY         |            1 | id          | A         |      434006 |     NULL | NULL   |      | BTREE      |         |               |
| ***_useractivities |          1 | user_index      |            1 | user        | A         |       13151 |     NULL | NULL   |      | BTREE      |         |               |
| ***_useractivities |          1 | user_type_index |            1 | user        | A         |       10585 |     NULL | NULL   |      | BTREE      |         |               |
| ***_useractivities |          1 | user_type_index |            2 | type        | A         |       13562 |     NULL | NULL   |      | BTREE      |         |               |
+--------------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Answer 1

Most of these rules of thumb for PostgreSQL indexing apply to most SQL database management systems.

https://dba.stackexchange.com/a/31517/1064

So, yes, you will probably benefit from an index on user and an index on type. You might benefit more from an index on the pair user, type.

You'll benefit from learning how to read an execution plan, too.