<h4 dir="auto">Pre-Submission Checklist</h4>



<ul class="contains-task-list">
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Commit message has the format required by CONTRIBUTING guide</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox"> Commits are split per component (core, individual modules, libs, utils, ...)</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox"> Each component has a single commit (if not, squash them into one commit)</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox"> No commits to README files for modules (changes must be done to docbook files<br>
in <code class="notranslate">doc/</code> subfolder, the README file is autogenerated)</li>
</ul>
<h4 dir="auto">Type Of Change</h4>
<ul class="contains-task-list">
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Small bug fix (non-breaking change which fixes an issue)</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox"> New feature (non-breaking change which adds new functionality)</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox"> Breaking change (fix or feature that would change existing functionality)</li>
</ul>
<h4 dir="auto">Checklist:</h4>

<ul class="contains-task-list">
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox"> PR should be backported to stable branches</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Tested changes locally</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox"> Related to issue #XXXX (replace XXXX with an open issue number)</li>
</ul>
<h4 dir="auto">Description</h4>
<p dir="auto">Function ps_watchers_db_timer_clean() inside src/modules/presence/subscribe.c is called repeatedly on my setup.<br>
Each time is called it performs a delete inside table watchers comparing columns inserted_time and status which are not indexed:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="MariaDB [kamailio]> explain delete from `watchers` where `inserted_time`<1656252889 AND `status`=2;
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | watchers | ALL  | NULL          | NULL | NULL    | NULL | 1    | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+"><pre class="notranslate"><code>MariaDB [kamailio]> explain delete from `watchers` where `inserted_time`<1656252889 AND `status`=2;
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | watchers | ALL  | NULL          | NULL | NULL    | NULL | 1    | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
</code></pre></div>
<p dir="auto">Can we consider adding an index improving performance a bit?</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="MariaDB [kamailio]> CREATE INDEX inserted_time_status_idx ON watchers (`inserted_time`, `status`);
Query OK, 0 rows affected (0.015 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [kamailio]> explain delete from `watchers` where `inserted_time`<1656252889 AND `status`=2;
+------+-------------+----------+-------+--------------------------+--------------------------+---------+------+------+-------------+
| id   | select_type | table    | type  | possible_keys            | key                      | key_len | ref  | rows | Extra       |
+------+-------------+----------+-------+--------------------------+--------------------------+---------+------+------+-------------+
|    1 | SIMPLE      | watchers | range | inserted_time_status_idx | inserted_time_status_idx | 4       | NULL | 1    | Using where |
+------+-------------+----------+-------+--------------------------+--------------------------+---------+------+------+-------------+
1 row in set (0.001 sec)"><pre class="notranslate"><code>MariaDB [kamailio]> CREATE INDEX inserted_time_status_idx ON watchers (`inserted_time`, `status`);
Query OK, 0 rows affected (0.015 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [kamailio]> explain delete from `watchers` where `inserted_time`<1656252889 AND `status`=2;
+------+-------------+----------+-------+--------------------------+--------------------------+---------+------+------+-------------+
| id   | select_type | table    | type  | possible_keys            | key                      | key_len | ref  | rows | Extra       |
+------+-------------+----------+-------+--------------------------+--------------------------+---------+------+------+-------------+
|    1 | SIMPLE      | watchers | range | inserted_time_status_idx | inserted_time_status_idx | 4       | NULL | 1    | Using where |
+------+-------------+----------+-------+--------------------------+--------------------------+---------+------+------+-------------+
1 row in set (0.001 sec)
</code></pre></div>

<hr>

<h4>You can view, comment on, or merge this pull request online at:</h4>
<p>  <a href='https://github.com/kamailio/kamailio/pull/3182'>https://github.com/kamailio/kamailio/pull/3182</a></p>

<h4>Commit Summary</h4>
<ul>
  <li><a href="https://github.com/kamailio/kamailio/pull/3182/commits/432e3022195e0bbdc28cad42cea330274d847d5c" class="commit-link">432e302</a>  kamctl:Index "inserted_time"+"status" in watchers</li>
</ul>

<h4 style="display: inline-block">File Changes </h4> <p style="display: inline-block">(<a href="https://github.com/kamailio/kamailio/pull/3182/files">2 files</a>)</p>
<ul>
  <li>
    <strong>M</strong>
    <a href="https://github.com/kamailio/kamailio/pull/3182/files#diff-4c099c8946c73fd25e3ddadfd6fecc90fee0cedd57098c012f7752aa9bc56599">utils/kamctl/mysql/presence-create.sql</a>
    (2)
  </li>
  <li>
    <strong>M</strong>
    <a href="https://github.com/kamailio/kamailio/pull/3182/files#diff-b42bfe876f1165f6e3a948d23e21c45bd6b0c32ef65fc524ba2ba807668117d8">utils/kamctl/postgres/presence-create.sql</a>
    (1)
  </li>
</ul>

<h4>Patch Links:</h4>
<ul>
  <li><a href='https://github.com/kamailio/kamailio/pull/3182.patch'>https://github.com/kamailio/kamailio/pull/3182.patch</a></li>
  <li><a href='https://github.com/kamailio/kamailio/pull/3182.diff'>https://github.com/kamailio/kamailio/pull/3182.diff</a></li>
</ul>

<p style="font-size:small;-webkit-text-size-adjust:none;color:#666;">—<br />Reply to this email directly, <a href="https://github.com/kamailio/kamailio/pull/3182">view it on GitHub</a>, or <a href="https://github.com/notifications/unsubscribe-auth/ABO7UZKWSI7YGHAIVEFKUODVTQQZ3ANCNFSM53HSRMSQ">unsubscribe</a>.<br />You are receiving this because you are subscribed to this thread.<img src="https://github.com/notifications/beacon/ABO7UZNC2ZPKIW675PFCPKTVTQQZ3A5CNFSM53HSRMS2YY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4TMHWWNA.gif" height="1" width="1" alt="" /><span style="color: transparent; font-size: 0; display: none; visibility: hidden; overflow: hidden; opacity: 0; width: 0; height: 0; max-width: 0; max-height: 0; mso-hide: all">Message ID: <span><kamailio/kamailio/pull/3182</span><span>@</span><span>github</span><span>.</span><span>com></span></span></p>
<script type="application/ld+json">[
{
"@context": "http://schema.org",
"@type": "EmailMessage",
"potentialAction": {
"@type": "ViewAction",
"target": "https://github.com/kamailio/kamailio/pull/3182",
"url": "https://github.com/kamailio/kamailio/pull/3182",
"name": "View Pull Request"
},
"description": "View this Pull Request on GitHub",
"publisher": {
"@type": "Organization",
"name": "GitHub",
"url": "https://github.com"
}
}
]</script>