Too many duplicate entries in "blitz_elementfieldcaches" table.

[simajkeee]

Bug Report

Hey craft-blitz team!

In "blitz_elementfieldcaches" table, multiple cacheId's are stored for the same elementId and fieldId. The main issue is that the table is growing very fast (currently, it contains 30+ million rows.) Please take a look at the screenshot. Is it a normal behaviour or a bug? Today we've updated from 4.10.3 to 4.12.2 version. Would the update to 4.12.2 address this issue? Is there any way to elimenate duplicates? If not, can these entries be removed without causing any issues with the plugin's functionality?

Plugin Version

4.10.3

Craft CMS Version

4.7.1

PHP Version

8.2.13

[bencroker]

This is not normal behaviour. Duplicates shouldn’t be allowed, since the primary key is the unique combination of cacheId, elementId, fieldId. Can you check whether this primary key is in place? If not, then perhaps the plugin installation migration was not successfully completed and it may be worth reinstalling the plugin.

SHOW INDEXES FROM blitz_elementfieldcaches WHERE key_name = "PRIMARY";

[simajkeee]

i can see the same query output. Could you please answer these: "Is there any way to elimenate duplicates? If not, can these entries be removed without causing any issues with the plugin's functionality?"

[bencroker]

Are you sure you have duplicates? I have a feeling your query needs to include cacheId in the GROUP BY clause to catch duplicate combinations.

SELECT
    elementId,
    fieldId,
    count(cacheId) AS count
FROM
    blitz_elementfieldcaches
WHERE
    elementId = 12345
GROUP BY
    elementId,
    fieldId,
    cacheId
HAVING
    count > 1

[simajkeee]

One row can look like this: cacheId = 1, fieldId = 1, elementId = 1. The second can look like: cacheId = 2, fieldId = 1, elementId = 1. In the screenshot I've sent you, you can see that there are many rows with different cacheIds but identical elementIds and fieldIds, which seems like excessive duplication, although I am not familiar with all the specifics.

[bencroker]

Those are two distinct rows, there’s no duplication here. 30 million rows does sound rather excessive, though. What does the main Blitz diagnostics utility show in terms of tracked items?

[simajkeee]

Hey @bencroker,

[bencroker]

Ok, so you seem to have a large site with a lot of content! There’s not much you can do besides address the recommendations you see in the screenshot above. Is the large number of rows impacting the site, besides growing the database?

[simajkeee]

A large database is the main issue. Can we clean the table? Won't it cause any issues with the plugin's operation?

[bencroker]

Yes, you can clear, then flush the cache, which will remove all data from the database tables. My advice would be to begin by auditing the tracked pages and query string params, to ensure that there isn’t too much noise in there. Query string params, especially, can sometimes get out of hand, and setting more explicit include patterns can help a lot. Also, getting eager loading in place and following the other other recommendations can all help.