<!-- 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.patchhttps://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(a)github.com>