[TikiWiki-commits] [Git][tikiwiki/tiki][24.x] 2 commits: [ENH] mysql index optimization: allow queries to specify only a subset of the...

Victor Emanouilov pushed to branch 24.x at Tiki Wiki CMS Groupware / Tiki


Commits:
f20576c9 by Victor Emanouilov at 2025-09-22T13:19:44+03:00
[ENH] mysql index optimization: allow queries to specify only a subset of the fields to be returned; use this in objectlib getFormattedTitle (used extensively by Relation field) and search lookup formatted results

- - - - -
788033d0 by Victor Emanouilov at 2025-09-22T13:20:07+03:00
[ENH] ability to select fields from list context to reduce memory usage - especially useful for large indexes where calc/subtotal needs to run over many results

- - - - -


6 changed files:

- lib/core/Search/Elastic/Index.php
- lib/core/Search/MySql/Index.php
- lib/core/Search/Query.php
- lib/core/Search/Query/WikiBuilder.php
- lib/core/Services/Search/Controller.php
- lib/objectlib.php


Changes:

=====================================
lib/core/Search/Elastic/Index.php
=====================================
@@ -406,6 +406,14 @@ class Search_Elastic_Index implements Search_Index_Interface, Search_Index_Query
                 ]];
             }
 
+            if ($selectFields = $query->getSelectionFields()) {
+                $sourcePart = [
+                    "_source" => $selectFields,
+                ];
+            } else {
+                $sourcePart = [];
+            }
+
             $indices = [$this->index];
 
             $foreign = array_map(function ($query) use ($builder) {
@@ -456,6 +464,7 @@ class Search_Elastic_Index implements Search_Index_Interface, Search_Index_Query
                 $facetPart,
                 $rescorePart,
                 $postFilterPart,
+                $sourcePart,
                 [
                     "from" => $resultStart,
                     "size" => $resultCount,


=====================================
lib/core/Search/MySql/Index.php
=====================================
@@ -132,7 +132,13 @@ class Search_MySql_Index implements Search_Index_Interface
 
             $order = $this->getOrderClause($query, (bool) $scoreFields);
 
-            $selectFields = $this->table->all();
+            if ($selectFields = $query->getSelectionFields()) {
+                $selectFields = array_map(function($field) {
+                    return $this->tfTranslator->shortenize($field);
+                }, $selectFields);
+            } else  {
+                $selectFields = $this->table->all();
+            }
 
             if ($scoreFields) {
                 $str = $this->db->qstr(implode(' ', $words));


=====================================
lib/core/Search/Query.php
=====================================
@@ -15,6 +15,7 @@ class Search_Query implements Search_Query_Interface
     private $count = 50;
     private $weightCalculator = null;
     private $identifierFields = null;
+    private $selectionFields = null;
 
     private $postFilter;
     private $subQueries = [];
@@ -46,6 +47,16 @@ class Search_Query implements Search_Query_Interface
         $this->identifierFields = $fields;
     }
 
+    public function setSelectionFields(array $fields)
+    {
+        $this->selectionFields = $fields;
+    }
+
+    public function getSelectionFields()
+    {
+        return $this->selectionFields;
+    }
+
     public function addObject($type, $objectId)
     {
         if (is_null($this->objectList)) {


=====================================
lib/core/Search/Query/WikiBuilder.php
=====================================
@@ -115,6 +115,11 @@ class Search_Query_WikiBuilder
         $this->paginationArguments['max'] = max(1, (int) $value);
     }
 
+    public function wpquery_select_fields($query, $value)
+    {
+        $query->setSelectionFields(preg_split('/[, ]+/', $value));
+    }
+
     public function wpquery_filter_editable($query, $editableType, array $arguments)
     {
         $fields = $this->get_fields_from_arguments($arguments);


=====================================
lib/core/Services/Search/Controller.php
=====================================
@@ -157,7 +157,9 @@ class Services_Search_Controller
             /** @var UnifiedSearchLib $lib */
             $lib = TikiLib::lib('unifiedsearch');
 
-            if (! empty($filter['title']) && preg_match_all('/\{(\w+)\}/', $format, $matches)) {
+            $format_pattern = '/\{([\w\.]+)\}/';
+
+            if (! empty($filter['title']) && preg_match_all($format_pattern, $format, $matches)) {
                 // formatted object_selector search results should also search in formatted fields besides the title
                 $titleFilter = $filter['title'];
                 unset($filter['title']);
@@ -185,14 +187,18 @@ class Services_Search_Controller
             $query->setOrder($input->sort_order->text() ?: 'title_asc');
             $query->setRange($input->offset->int(), $input->maxRecords->int() ?: $prefs['maxRecords']);
 
+            if (preg_match_all($format_pattern, $format, $m)) {
+                $query->setSelectionFields($m[1]);
+            }
+
             $result = $query->search($lib->getIndex());
 
-            $result->applyTransform(function ($item) use ($format, $smarty) {
+            $result->applyTransform(function ($item) use ($format, $format_pattern, $smarty) {
                 $transformed = [
                     'object_type' => $item['object_type'],
                     'object_id' => $item['object_id'],
                     'parent_id' => $item['gallery_id'],
-                    'title' => preg_replace_callback('/\{(\w+)\}/', function ($matches) use ($item, $format) {
+                    'title' => preg_replace_callback($format_pattern, function ($matches) use ($item, $format) {
                         $key = $matches[1];
                         if (isset($item[$key])) {
                             // if this is a trackeritem we do not want only the name but also the trackerid listed when setting up a field


=====================================
lib/objectlib.php
=====================================
@@ -607,9 +607,13 @@ class ObjectLib extends TikiLib
                 'object_type' => $type,
                 'object_id'   => $id
             ]);
+            $format_pattern = '/\{([\w\.]+)\}/';
+            if (preg_match_all($format_pattern, $format, $m)) {
+                $query->setSelectionFields($m[1]);
+            }
             $result = $query->search($lib->getIndex());
-            $result->applyTransform(function ($item) use ($format) {
-                return preg_replace_callback('/\{(\w+)\}/', function ($matches) use ($item, $format) {
+            $result->applyTransform(function ($item) use ($format, $format_pattern) {
+                return preg_replace_callback($format_pattern, function ($matches) use ($item, $format) {
                     $key = $matches[1];
                     if (isset($item[$key])) {
                         return $item[$key];



View it on GitLab: https://gitlab.com/tikiwiki/tiki/-/compare/13623755fdbb78cd0cfb7daf77ff425daba50c9e...788033d0acbf3846830d8be4d8a250007ffd61ad

-- 
View it on GitLab: https://gitlab.com/tikiwiki/tiki/-/compare/13623755fdbb78cd0cfb7daf77ff425daba50c9e...788033d0acbf3846830d8be4d8a250007ffd61ad
You're receiving this email because of your account on gitlab.com.

_______________________________________________
TikiWiki-cvs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/tikiwiki-cvs