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

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

Closed

Activities

	wojciech.kapcia@tigase.net 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
	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.net 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.

Type	Bug
Priority	Critical
Assignee	wojciech.kapcia@tigase.net
RedmineID	6657

Issue Votes (0)

Watchers (0)

Reference

tigase/_server/tigase-message-archiving#43

Please wait...

Page is in error, reload to recover