Potentially redundant index talk_sessions.ts_attendee_id

[ChristophWurst]

How to use GitHub

• Please use the 👍 reaction to show that you are affected by the same issue.
• Please don't comment if you have no relevant information to add. It's just extra noise for everyone subscribed to this issue.
• Subscribe to receive notifications on status change and new comments.

---

Steps to reproduce

1. Install the app

Expected behaviour

Efficient database index usage

Actual behaviour

# ########################################################################
# talk_sessions
# ########################################################################

# ts_attendee_id is a left-prefix of ts_attendee_in_call
# Key definitions:
#   KEY `ts_attendee_id` (`attendee_id`),
#   KEY `ts_attendee_in_call` (`attendee_id`,`in_call`)
# Column types:
#	  `attendee_id` bigint(20) unsigned not null
#	  `in_call` int(11) not null default 0
# To remove this duplicate index, execute:
ALTER TABLE `talk_sessions` DROP INDEX `ts_attendee_id`;

Talk app

Talk app version: (see apps administration page: /index.php/settings/apps)

Custom Signaling server configured: yes/no and version (see Talk administration settings: /index.php/settings/admin/talk#signaling_server)

Custom TURN server configured: yes/no (see Talk administration settings: /index.php/settings/admin/talk#turn_server)

Custom STUN server configured: yes/no (see Talk administration settings: /index.php/settings/admin/talk#stun_server)

Browser

Microphone available: yes/no

Camera available: yes/no

Operating system: Windows/Ubuntu/Mac/...

Browser name: Firefox/Chrome/Safari/...

Browser version: 124/125/...

Browser log

Details

``` Insert your browser log here, this could for example include: a) The javascript console log b) The network log c) ... ```

Server configuration

Operating system: Ubuntu/RedHat/...

Web server: Apache/Nginx

Database: MySQL/Maria/SQLite/PostgreSQL

PHP version: 8.1/8.2/8.3

Nextcloud Version: (see administration page)

List of activated apps:

Details

If you have access to your command line run e.g.:
sudo -u www-data php occ app:list
from within your server installation folder

Nextcloud configuration:

Details

If you have access to your command line run e.g.:
sudo -u www-data php occ config:list system
from within your Nextcloud installation folder

Server log (data/nextcloud.log)

Details

Insert your server log here

[nickvergessen]

But we have quite a lot of queries were we simply left join based on attendee id

[ChristophWurst]

Can you share an example? You can use the ts_attendee_in_call index for lookups by attendee_id only

[nickvergessen]

Basically all getRoomFor*() in the manager:
https://github.com/nextcloud/spreed/blob/main/lib/Manager.php#L984

[ChristophWurst]

You are selecting one row to which you join the session. The index will work perfectly.

A dedicated index is only really advantageous if you have to scan a large range.