Missing index on tig_ma_jids and lot's of select executions (#43)

Wojciech Kapcia (Tigase) opened 7 years ago

It looks like there is a missing index on tig_ma_jids conflicted with multiples select calls:

During this time the below query runs 4,548 times. It also looks like it scans 692,582 rows in each run.

An accumulation of such SELECT statements can have an effect on CPU usage.

It is also show in the results of - mysql> SHOW FULL PROCESSLIST;

Are you aware of this query? And is there an index on table "tig_ma_jids" on column "jid"?

select jid, jid_id from tig_ma_jids where jid = 'xmppb…@…' or jid = 'xmpp…@…';

User@Host: tigase[tigase] @ [10.3.10.175] Id: 17723

Query_time: 0.930243 Lock_time: 0.000056 Rows_sent: 1 Rows_examined: 692581

Activities

Andrzej Wójcik (Tigase) commented 7 years ago

I've added a code responsible for adding this missing index. I've verified that fix add the index and that index is used by MySQL during execution of this query.

Below is result of execution of following explain in JSON.

explain select jid, jid_id from tig_ma_jids where jid = 'admin@localhost';

[
  {
    "id": "1",
    "select_type": "SIMPLE",
    "table": "tig_ma_jids",
    "partitions": null,
    "type": "ref",
    "possible_keys": "tig_ma_jids_jid_index",
    "key": "tig_ma_jids_jid_index",
    "key_len": "1023",
    "ref": "const",
    "rows": "1",
    "filtered": 100,
    "Extra": "Using where"
  }
]

I've verified SQL schemas for version 2.0.0, but there is no need for an index on jid field as for MySQL jid_id is looked up using newly introduced jid_sha1 field which already has an index.

Wojciech Kapcia (Tigase) commented 7 years ago

%andrzej.wojcik shouldn't this index be added to other databases as well (MS SQL Server, Postgresql)?
Andrzej Wójcik (Tigase) commented 7 years ago

Other DB schemas already contain this index. Only MySQL schema was missing this index.
Login to comment

Type	Bug
Priority	Critical
Assignee	Wojciech Kapcia (Tigase)
RedmineID	6657

Issue Votes (0)

Watchers (0)

Reference

tigase/_server/tigase-message-archiving#43