Project

General

Profile

Bug #18323

Missing Database Index on mysql/mariadb

Added by Anonymous over 4 years ago. Updated over 4 years ago.

Status:
Duplicate
Priority:
Normal
Assignee:
-
Category:
Database
Target version:
-
Difficulty:
easy
Triaged:
No
Bugzilla link:
Pull request:
Fixed in Releases:
Found in Releases:

Description

We are managing 150 Hosts in our foreman environment and when we press on Hosts -> All Hosts it takes about 40 Seconds to load.
I have no enabled slow query logging in mariadb Server 5.5.53 and figured out that this query takes about 35 Seconds:

Reading mysql slow query log from ex10000.emmi.ch.slow-queries
Count: 1 Time=36.02s (36s) Lock=0.00s (0s) Rows_sent=149.0 (149), Rows_examined=78330.0 (78330), root[root]@localhost
SELECT * FROM `reports` WHERE `reports`.`host_id` IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N) GROUP BY `reports`.`host_id` ORDER BY created_at

We figured out that there seems to missing a Index on Column type in table reports, thus we have added a index with the following SQL Statement:

CREATE INDEX index_reports_on_type_and_host_id ON reports (type,host_id);

Now the query duration dropped down to 0.165 Seconds and the "All Hosts" page is loaded in 4 Seconds.
I would propose to add this index.


Related issues

Is duplicate of Foreman - Feature #18136: Reports type column should have an indexClosed2017-01-18

History

#1 Updated by Ohad Levy over 4 years ago

  • Is duplicate of Feature #18136: Reports type column should have an index added

#2 Updated by Ohad Levy over 4 years ago

  • Status changed from New to Duplicate

thanks, we are already tracking the issue at #18136

Also available in: Atom PDF