CODEWITHSUNDEEP
mysqldatabasemariadbquery-performance
Pelin
Pelin

Reputation: 457

MariaDB - Should I add index to my table?

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      |         |               |
+--------------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Upvotes: 3

Views: 1532

Answers (2)

Rick James
Rick James

Reputation: 142258

For that query, either of these is optimal:

INDEX(user, type)
INDEX(type, user)

Separate indexes (INDEX(user), INDEX(type)) is likely to be not nearly as good.

MySQL's InnoDB has only BTree, not Hash. Anyway, BTree is essentially as good as Hash for 'point queries', and immensely better for 'range' queries.

Indexing tips.

Indexes help SELECTs and UPDATEs, sometimes a lot. Use them. The side effects are minor -- such as extra disk space used.

Upvotes: 0

Mike Sherrill &#39;Cat Recall&#39;
Mike Sherrill &#39;Cat Recall&#39;

Reputation: 95532

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.

Upvotes: 2

Related Questions