Value lookups by items_id do not use the itemtype index and scan the whole values table

未關閉 適合新手
#1,310 1 則留言 0 個 reaction 已指派 0 人 在 GitHub 檢視

維護者通常 1 天內回覆

@marifex 已經在處理了。

開始於 2026年10月21日。

  • #1312 來自 @marifex —— 未關閉

評估

難度
2/5
預估耗時
1-3 小時
新手友好度
75/100
Issue 類型
缺陷
描述清晰度
描述清楚
活躍度
活躍
技術堆疊
mariadb, php

研究方向

先閱讀 PluginFieldsField::prepareHtmlFields() 和 PluginFieldsContainer::updateFieldsValues(),接著找出儲存路徑中提到的其他 getFromDBByCrit() 呼叫。檢查每次查詢如何取得其項目類型,並將查詢計畫與回報的 EXPLAIN 重現結果進行比較。當這些查詢可以使用現有的複合索引,並避免掃描大型資料表時,就算完成。

由索引模型根據 Issue 內容生成。

描述

Stale
GLPI Version

11.0.11

Plugin version

1.24.6. The same code is in 1.25.0 and on main.

Bug description

The values table of each container (for example glpi_plugin_fields_ticketticketdetails) has the unique index itemtype_item_container (itemtype, items_id, plugin_fields_containers_id) and no other index on items_id. Several lookups filter on items_id without itemtype, so the index cannot be used and every call reads the whole table:

  • PluginFieldsField::prepareHtmlFields(), $obj->find(['plugin_fields_containers_id' => ..., 'items_id' => ...]), run on every display of an item that has a container.
  • PluginFieldsContainer, getFromDBByCrit(['items_id' => $items_id]) in the save path (4 places, for example in updateFieldsValues()).

With 500,000 tickets and about 290,000 rows in the ticket container table, the first query read about 290,000 rows per call and took 156 ms on average at low load. Under heavy load the average rose to between 3.2 and 5.1 s per call. It was the fourth most expensive statement on the server at low load, and opening a ticket was slowed down by it.

Relevant log output
calls | total ms | avg ms | rows examined | no index | statement
268   | 41881    | 156.3  | 77890984      | 268      | SELECT * FROM `glpi_plugin_fields_ticketticketdetails` WHERE `plugin_fields_containers_id` = ? AND `items_id` = ?
430   | 745460   | 1733.6 | 124974340     | 430      | SELECT `id` FROM `glpi_plugin_fields_ticketticketdetails` WHERE `items_id` = ?

(performance_schema statement digests; the second line is from a step under heavy load)

Steps To reproduce
  1. Create a container on tickets with a few fields.
  2. Have a large number of tickets with values in it (hundreds of thousands).
  3. Open a ticket and run EXPLAIN SELECT * FROM glpi_plugin_fields_ticketticketdetails WHERE plugin_fields_containers_id = 1 AND items_id = 12345;

Expected: the lookup uses the unique index.

Actual: no index is used and the whole table is read.

Your GLPI setup information

Docker, PHP 8.x with Apache prefork, MariaDB 10.11 with an 8 GB buffer pool.

Anything else?

Each values table holds a single itemtype, so adding 'itemtype' => $item->getType() (or the known itemtype) to these criteria lets the existing unique index be used. An index on items_id would also work.

主要語言
PHP
星號
122
分支
85
平均合併
1 天 15 小時
30 天內合併 PR
20

環境準備

這個專案沒有提供開發容器、Dockerfile 或貢獻指南,環境需要你自己搭建:先看它的 README,通用步驟見我們的新手貢獻指南。

從這裡開始

  1. 先讀完整個 Issue,再讀專案的貢獻指南。
  2. 在 Issue 下留言說明你要接手 —— 這能避免兩個人做同樣的事。
  3. Fork 儲存庫,在一個分支上完成修改。
  4. 送出 Pull Request,並在描述裡引用這個 Issue 編號。

pluginsGLPI/fields 的其他 Issue

查看 pluginsGLPI/fields 的全部 Issue

相似的 Issue

更多 PHP Issue

把新 issue 寄到你的電子郵件信箱

精選適合新手參與的 GitHub issue 摘要。