The built-in search index tables #

This page is for database administrators of a site that uses Exponential's built-in search engine. That engine keeps its index in the site's own database, so its tables are among the largest and busiest. This page answers: which tables hold the index, how content gets indexed, how to rebuild the index, and what to watch. Sources: 10.8 Search and Operating a site, numbers in the search index.

Contents #

1. The engine
2. The tables
3. When content is indexed
4. Rebuilding the index
5. Large numbers
6. Search statistics are personal data
7. Solr instead
8. Checklist

The engine #

The built-in engine is eZSearchEngine (site.ini [SearchSettings] SearchEngine). It works on every supported database. Related objects are indexed with the object that points to them, one level deep.

The tables #

When content is indexed #

At publish, unless [SearchSettings] DelayedIndexing is enabled (or classbased with DelayedIndexingClassList[]). With delayed indexing publishing only queues the work in ezpending_actions (index_object), and the default cronjob part's indexcontent.php indexes it later, oldest first, removing each row once done. This makes publishing faster for large objects.

SELECT action, COUNT(*), MIN(created) FROM ezpending_actions GROUP BY action;

A growing index_object count with an old MIN(created) means the cronjobs do not run (Housekeeping: cronjobs and queue tables).

Rebuilding the index #

After an import, a database move, a restore without the search tables, or when results are wrong:

php bin/php/updatesearchindex.php --clean --allow-root-user

--clean removes all search data before indexing. Other options from its help: --db-host, --db-user, --db-password, --db-database, --db-driver (--db-type) to index into another database, and --sql to display the queries. The console name is exp:updatesearchindex. A rebuild reads every object and writes the two large tables; run it off-peak and expect the database to grow its log files meanwhile. On MongoDB, run the cronjobs once after install to build the index (Setting up MongoDB).

Large numbers #

The engine stores each word with its numeric value in integer_value, so a search can filter on numbers. A very large number in a text (a 20-digit order number) no longer breaks indexing: each driver knows the range of its integer columns ($db::MAX_INT, $db::MIN_INT: signed 32-bit for MySQL, PostgreSQL and Oracle, signed 64-bit for SQLite and MongoDB), and $db->clampInteger() stores the largest value the column holds. Nothing to do on an upgrade.

Search statistics are personal data #

LogSearchStats is disabled by default, because it stores every phrase visitors type, which can be personal data. If you enable it, include ezsearch_search_phrase in your retention rules.

Solr instead #

Large sites often move search to Solr through the ezfind extension, when it is installed; php bin/php/console exp:solr controls a Solr server once it is installed and configured. The built-in tables then stop growing; Solr's index lives outside the database and needs its own backup or rebuild plan. With ezfind, moves and visibility changes queue index_moved_node actions as well.

Checklist #

• To do: The cronjobs run, so ezpending_actions does not grow.
• To do: A full rebuild is planned after imports, restores and engine moves.
• To do: ezsearch_object_word_link is included in capacity planning.
• To do: LogSearchStats stays disabled unless statistics are needed and covered by retention rules.

Related pages #

• Searching
• The tables a database administrator should know
• Search backends (for hosting companies)
• Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.