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