Feature #20608

Gain speed with additional indices

Added by Maximilian Mack 8 months ago. Updated 8 months ago.

Status:Closed
Priority:Low
Assigned To:-
Category:Database
Target version:-
Difficulty:trivial Bugzilla link:
Found in release:nightly Pull request:https://github.com/theforeman/foreman/pull/4745
Story points-
Velocity based estimate-
Release1.16.0Release relationshipAuto

Description

Problem:
Some queries are using a full table scan. At 2100 queries/sec and 21 hours uptime this is the result:

query, exec_count, no_index_used_count 
'SELECT `ssh_keys` . * FROM `ss ... RE `ssh_keys` . `user_id` = ? ','1925175','1925178'
'SELECT `config_groups` . * FRO ... R BY `config_groups` . `name` ','428267','428269'
'SELECT `smart_proxies` . * FRO ... WHERE `features` . `name` = ? ','365721','365725'
'SELECT `taxonomies` . * FROM ` ... nomies` . `title` ASC LIMIT ? ','317024','317024'
'SELECT `config_groups` . `id`  ... R BY `config_groups` . `name` ','186254','186254'
'SELECT `smart_proxies` . * FRO ...  `features` . `name` IN (...) ','169015','169015'
'SELECT `architectures` . * FRO ... itectures` . `id` ASC LIMIT ? ','158512','158511'
'SELECT `environments` . * FROM ... nvironments` . `name` LIMIT ? ','158418','158418'
'SELECT `models` . * FROM `mode ...  BY `models` . `name` LIMIT ? ','158418','158418'
'SELECT `domains` . * FROM `dom ... BY `domains` . `name` LIMIT ? ','158417','158418'

Steps to Reproduce:

select query, exec_count, no_index_used_count from sys.`statements_with_full_table_scans` ORDER BY  no_index_used_count DESC LIMIT 10;

Expected result:
Use indices for querying

Associated revisions

Revision d102db23
Added by Maximilian Mack 8 months ago

Fixes #20608 - Adding index for ssh_keys

Adds a index :user_id to ssh_keys

History

#1 Updated by The Foreman Bot 8 months ago

  • Status changed from New to Ready For Testing
  • Pull request https://github.com/theforeman/foreman/pull/4745 added

#2 Updated by Timo Goebel 8 months ago

  • Release set to 1.16.0

#3 Updated by Maximilian Mack 8 months ago

  • Status changed from Ready For Testing to Closed
  • % Done changed from 0 to 100

Also available in: Atom PDF