How to clear the database of unused fields?

Question

I have one section with different entry types. One entry type uses two matrix fields, another one only one, and so on.

Since the frontend layout is always the same, I use one template for the whole section:

{% extends "_layouts/main" %}

{% set title = entry.title %}

{% block content %}
    {% include "_fields/content__circles" %}
    {% include "_fields/content__body" %}
    {% include "_fields/content__links" %}
{% endblock %}

This works great, except when I change an entry to another entry type: Because the field-data isn't deleted from the db and shows up anyway, even though the field isn't added to the new entry type.

This is actually great, because that way your data isn't gone if you delete a field by mistake etc., but over time the db gets bloated, which I want to prevent.

Question: How can I delete data of fields which aren't used without editing each entry by hand or going in the db?

Clearification: entrytype 1 has content__circles and content__body, entrytype 2 has only content__body. After switching from type 1 to 2, the data of content__circles is still kept in the database, and is even accessible inside the entry-variable.

Answer 1

You can get the fields that aren’t in use anywhere by running this SQL query:

SELECT f.*
FROM craft_fields f
LEFT JOIN craft_fieldlayoutfields l ON l.fieldId = f.id
WHERE l.id IS NULL

The easiest way to delete them would be from the Control Panel, but if you really want to limit your work to the database, you would need to delete those fields’ rows in craft_fields, and their corresponding column (“field_TheFieldHandle”) in craft_content. For Matrix fields, you would also need to drop the corresponding DB table (“craft_matrixcontent_TheFieldHandle”).