Project

General

Profile

Actions

Feature #20608

closed

Gain speed with additional indices

Added by Maximilian Mack over 7 years ago. Updated over 6 years ago.

Status:
Closed
Priority:
Low
Assignee:
-
Category:
Database
Target version:
Difficulty:
trivial
Triaged:
Fixed in Releases:
Found in Releases:

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

Actions #1

Updated by The Foreman Bot over 7 years ago

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

Updated by Timo Goebel over 7 years ago

  • Translation missing: en.field_release set to 240
Actions #3

Updated by Maximilian Mack over 7 years ago

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

Also available in: Atom PDF