[sr-dev] [kamailio/kamailio] kamctl:Index "inserted_time"+"status" in watchers (PR #3182)

[Alessio Garzi]

<!-- Kamailio Pull Request Template -->

<!--
IMPORTANT:
  - for detailed contributing guidelines, read:
    https://github.com/kamailio/kamailio/blob/master/.github/CONTRIBUTING.md
  - pull requests must be done to master branch, unless they are backports
    of fixes from master branch to a stable branch
  - backports to stable branches must be done with 'git cherry-pick -x ...'
  - code is contributed under BSD for core and main components (tm, sl, auth, tls)
  - code is contributed GPLv2 or a compatible license for the other components
  - GPL code is contributed with OpenSSL licensing exception
-->

#### Pre-Submission Checklist
<!-- Go over all points below, and after creating the PR, tick all the checkboxes that apply -->
<!-- All points should be verified, otherwise, read the CONTRIBUTING guidelines from above-->
<!-- If you're unsure about any of these, don't hesitate to ask on sr-dev mailing list -->
- [x] Commit message has the format required by CONTRIBUTING guide
- [ ] Commits are split per component (core, individual modules, libs, utils, ...)
- [ ] Each component has a single commit (if not, squash them into one commit)
- [ ] No commits to README files for modules (changes must be done to docbook files
in `doc/` subfolder, the README file is autogenerated)

#### Type Of Change
- [x] Small bug fix (non-breaking change which fixes an issue)
- [ ] New feature (non-breaking change which adds new functionality)
- [ ] Breaking change (fix or feature that would change existing functionality)

#### Checklist:
<!-- Go over all points below, and after creating the PR, tick the checkboxes that apply -->
- [ ] PR should be backported to stable branches
- [x] Tested changes locally
- [ ] Related to issue #XXXX (replace XXXX with an open issue number)

#### Description
Function ps_watchers_db_timer_clean() inside src/modules/presence/subscribe.c is called repeatedly on my setup.
Each time is called it performs a delete inside table watchers comparing columns inserted_time and status which are not indexed:

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

Can we consider adding an index improving performance a bit?

```
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)
```



You can view, comment on, or merge this pull request online at:

  https://github.com/kamailio/kamailio/pull/3182

-- Commit Summary --

  * kamctl:Index "inserted_time"+"status" in watchers

-- File Changes --

    M utils/kamctl/mysql/presence-create.sql (2)
    M utils/kamctl/postgres/presence-create.sql (1)

-- Patch Links --

https://github.com/kamailio/kamailio/pull/3182.patch
https://github.com/kamailio/kamailio/pull/3182.diff

-- 
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/pull/3182
You are receiving this because you are subscribed to this thread.

Message ID: <kamailio/kamailio/pull/3182 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.kamailio.org/pipermail/sr-dev/attachments/20220711/d6ccf991/attachment-0001.htm>