Possibility to remove empty rows of searchindex table

[d--j]

Now that we have #11575 we can prune the search index of keywords that are not relevant for the specific use case of a CraftCMS installation. But CraftCMS still unconditionally creates a row for each searchable attribute of each element in the searchindex table.

Even a row with an empty keywords column has the three INT columns elementId, fieldId and siteId (4 bytes each) the attribute VARCHAR(25) (length + 1 byte) and the empty keywords MEDIUMTEXT (11 bytes in the row, 0 (length) + 3 bytes outside). This results in ~35 bytes (depending on the attribute length) per empty row. And this is only the data, calculating the bytes an empty row holds in the BTREE and FULLTEXT indexes is above my pay grade. ¹

~35 bytes per row might not seem much but it sums up to a sizeable amount.

Does CraftCMS actually need rows in the searchindex table where the keywords column is empty? Could you change the behavior so that the Search service would just skip inserting rows into searchindex when the keywords column is empty? ²

Proposed change: remove all empty rows

change

cms/src/services/Search.php

Lines 340 to 362 in 3db7fb2

	if ($this->hasEventHandlers(self::EVENT_BEFORE_INDEX_KEYWORDS)) {
	$event = new IndexKeywordsEvent([
	'element' => $element,
	'attribute' => $attribute,
	'fieldId' => $fieldId,
	'keywords' => $keywords,
	]);
	$this->trigger(self::EVENT_BEFORE_INDEX_KEYWORDS, $event);
	$keywords = $event->keywords;
	}
	// Save 'em
	$columns = [
	'elementId' => $element->id,
	'attribute' => $attribute ?? 'field',
	'fieldId' => $fieldId ? (string)$fieldId : '0',
	'siteId' => $site->id,
	];
	if ($keywords !== '') {
	// Add padding around keywords
	$keywords = ' ' . $keywords . ' ';
	}

to

        if ($this->hasEventHandlers(self::EVENT_BEFORE_INDEX_KEYWORDS)) {
            $event = new IndexKeywordsEvent([
                'element' => $element,
                'attribute' => $attribute,
                'fieldId' => $fieldId,
                'keywords' => $keywords,
            ]);
            $this->trigger(self::EVENT_BEFORE_INDEX_KEYWORDS, $event);
            $keywords = $event->keywords;
        }

        // Save 'em
        $columns = [
            'elementId' => $element->id,
            'attribute' => $attribute ?? 'field',
            'fieldId' => $fieldId ? (string)$fieldId : '0',
            'siteId' => $site->id,
        ];

        if ($keywords !== '') {
            // Add padding around keywords
            $keywords = ' ' . $keywords . ' ';
        } else {
            return;
        }

If it is generally necessary to have searchindex rows with empty keywords columns, could we conditionally skip empty rows?

Proposed change: skip row creation via event

change

cms/src/services/Search.php

Lines 340 to 349 in 3db7fb2

	if ($this->hasEventHandlers(self::EVENT_BEFORE_INDEX_KEYWORDS)) {
	$event = new IndexKeywordsEvent([
	'element' => $element,
	'attribute' => $attribute,
	'fieldId' => $fieldId,
	'keywords' => $keywords,
	]);
	$this->trigger(self::EVENT_BEFORE_INDEX_KEYWORDS, $event);
	$keywords = $event->keywords;
	}

to

        if ($this->hasEventHandlers(self::EVENT_BEFORE_INDEX_KEYWORDS)) {
            $event = new IndexKeywordsEvent([
                'element' => $element,
                'attribute' => $attribute,
                'fieldId' => $fieldId,
                'keywords' => $keywords,
            ]);
            $this->trigger(self::EVENT_BEFORE_INDEX_KEYWORDS, $event);
            $keywords = $event->keywords;
            if ($event->handled) { // or add a new boolean property $skip
                return;
            }
        }

---

Here's an example: I have a CraftCMS site that has a volume with managed assets. Currently there are ~16,000 assets in the volume but this will only increase. The volume is managed which means custom code syncs new assets from a DAM (WoodWing) to the volume and deletes assets that are no longer needed. The folder structure and filenames are managed, too, and have no meaning to humans. Therefore we do not need the filename, kind and extension rows in the searchindex table for these elements.

The following code can remove these keywords from the searchindex table in CraftCMS 4.2.0+:

use craft\elements\Asset;
use craft\events\IndexKeywordsEvent;
use craft\services\Search;
use yii\base\Event;

Event::on(
    Search::class,
    Search::EVENT_BEFORE_INDEX_KEYWORDS,
    static function (IndexKeywordsEvent $event) {
        $element = $event->element;
        if ($element instanceof Asset) {
            if (
                $element->getVolume()->handle == 'volumeWoodWing' &&
                in_array($event->attribute, ['filename', 'kind', 'extension'])
            ) {
                $event->keywords = '';
            }
        }
    }
);

After a ./craft resave/assets --update-search-index 1 the searchindex table size is 69.2 MiB³

When I manually delete the empty rows with DELETE FROM `searchindex` WHERE `attribute` in ('filename', 'kind', 'extension') AND `keywords` = '';⁴ the table size dropped to 59.2 MiB

That's a 14% size reduction.

Footnotes

1. Byte values taken from https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html#data-types-storage-reqs-innodb ↩

2. Maybe it is necessary to support the edge case body::""? Is this actually valid search syntax and if it is, is it used by someone? Nowadays the control panel users can use the new conditions UI to achieve this and searching in code always can use the specific query parameter. ↩

3. Unfortunately I forgot to write down the table size before making this optimization but I recall it had roughly the same size before the optimization. ↩

4. Deleting 125,388 empty rows from 743,552 total rows. ↩

[brandonkelly]

We do have a need for those rows: they are referenced for -attribute:* searches (find elements where attribute is empty). If there was no row, we wouldn’t be able to tell the difference between an empty field and one that just hadn’t been indexed yet.

[d--j]

OK when we generally need them, can we have the second proposed change (skip row creation via event) so that one can skip creating (empty) rows when it makes sense?

[brandonkelly]

Just made it possible to cancel indexing for an attribute/field via EVENT_BEFORE_INDEX_KEYWORDS for Craft 4.3, by setting $event->isValid = false. (479c40b)

[brandonkelly]

Craft 4.3 was released earlier this week with this change!