What happened?
Description
We've got a quite large section called object (with an entry type called object as well) that currently has about 25k entries. All of the entries in this section are imported and updated via a FeedMe import. As we ran into performance issues with Craft's native search we were trying to make less fields available to the search index. So we reduced the number of searchable fields and ran
craft resave/entries --update-search-index --section object --limit 10000 (The limit of 10.000 was just a test, we are planning to run the same command for all entries).
Before calling this command we ran this SQL query to get the sizes of all tables to compare before and after.
> SELECT
table_name as "Table",
table_rows as "Rows",
data_length as "Length",
index_length as "Index",
round(((data_length + index_length) / 1024 / 1024),2) as "Size (mb)"
FROM information_schema.TABLES
WHERE table_schema = '%%NAME OF YOUR CRAFT DB%%'
ORDER BY `Size (mb)` DESC;
+----------------------------+--------+-----------+----------+-----------+
| Table | Rows | Length | Index | Size (mb) |
+----------------------------+--------+-----------+----------+-----------+
| searchindex | 714985 | 103481344 | 37339136 | 134.30 |
| content | 122278 | 80330752 | 12140544 | 88.19 |
| changedfields | 278828 | 12075008 | 27328512 | 37.58 |
| elements_sites | 130086 | 14172160 | 18432000 | 31.09 |
| elements | 64341 | 7880704 | 19038208 | 25.67 |
[...]
After the command finished, we ran the query again:
> SELECT
table_name as "Table",
table_rows as "Rows",
data_length as "Length",
index_length as "Index",
round(((data_length + index_length) / 1024 / 1024),2) as "Size (mb)"
FROM information_schema.TABLES
WHERE table_schema = '%%NAME OF YOUR CRAFT DB%%'
ORDER BY `Size (mb)` DESC;
+----------------------------+--------+-----------+----------+-----------+
| Table | Rows | Length | Index | Size (mb) |
+----------------------------+--------+-----------+----------+-----------+
| searchindex | 438577 | 103464960 | 37257216 | 134.20 |
| changedfields | 534244 | 38354944 | 62029824 | 95.73 |
| content | 122278 | 80330752 | 12140544 | 88.19 |
| elements_sites | 130086 | 14172160 | 18432000 | 31.09 |
| elements | 64341 | 7880704 | 19038208 | 25.67 |
[...]
As you can see, the changedfields table grew with a factor of 2.5x. If we had resaved all of the entries in this section we would easily have come up with a table of over 200mb in size.
Now we were interested to see what fields are saved in the changedfields table:
> SELECT COUNT(*) as num_search_entry, f.handle
FROM changedfields c, fields f
WHERE c.fieldId = f.id
GROUP BY f.handle
ORDER BY num_search_entry DESC;
+------------------+-----------------------------+
| num_search_entry | handle |
+------------------+-----------------------------+
| 37552 | objectImages |
| 23916 | objectInventory |
| 23916 | objectId |
| 23915 | objectArtist |
| 23622 | objectRelation |
| 23622 | objectLiterature |
| 23622 | objectOnDisplay |
| 23622 | objectDateStart |
| 23622 | objectDateEnd |
| 20599 | objectDescription |
| 20597 | objectDate |
| 20597 | objectLocality |
| 20597 | objectDimensions |
| 20597 | objectAcquisition |
| 20597 | objectMaterial |
| 20597 | objectLocation |
| 20308 | objectProvenance |
| 20303 | objectPlacement |
| 20303 | objectTerm |
| 1645 | linkField |
| 1579 | description |
[...]
As you can see, all of those fields are used for this entry type. linkField and description are two fields used for other sections/entry types.
We couldn't find much information about what the changedfields table actually does and whether it is safe to truncate it.
Any help is appreciated.
Craft CMS version
4.5.6.1
PHP version
8.0/8.2
Operating system and version
No response
Database type and version
MariaDB
Image driver and version
No response
Installed plugins and versions
What happened?
Description
We've got a quite large section called
object(with an entry type calledobjectas well) that currently has about 25k entries. All of the entries in this section are imported and updated via a FeedMe import. As we ran into performance issues with Craft's native search we were trying to make less fields available to the search index. So we reduced the number of searchable fields and rancraft resave/entries --update-search-index --section object --limit 10000(The limit of 10.000 was just a test, we are planning to run the same command for all entries).Before calling this command we ran this SQL query to get the sizes of all tables to compare before and after.
After the command finished, we ran the query again:
As you can see, the
changedfieldstable grew with a factor of 2.5x. If we had resaved all of the entries in this section we would easily have come up with a table of over 200mb in size.Now we were interested to see what fields are saved in the
changedfieldstable:As you can see, all of those fields are used for this entry type.
linkFieldanddescriptionare two fields used for other sections/entry types.We couldn't find much information about what the
changedfieldstable actually does and whether it is safe to truncate it.Any help is appreciated.
Craft CMS version
4.5.6.1
PHP version
8.0/8.2
Operating system and version
No response
Database type and version
MariaDB
Image driver and version
No response
Installed plugins and versions