This is a follow up to the changes made in #1031010: Support revisions for field collections which added revisions support to field collections. The code committed in that issue added a column to each field collection field table called {$field_collection_name}_revision_id. Once revisions support is added in field collection module (and be definition in the Entity API module) then EntityFieldQuery queries such as this are run:

EntityFieldQuery::finishQuery

SELECT
  field_data_field_collection_slider0.entity_type AS entity_type, 
  field_data_field_collection_slider0.entity_id AS entity_id, 
  field_data_field_collection_slider0.revision_id AS revision_id, 
  field_data_field_collection_slider0.bundle AS bundle 
FROM 
  field_data_field_collection_slider field_data_field_collection_slider0 
WHERE 
  (field_data_field_collection_slider0.field_collection_slider_revision_id = :db_condition_placeholder_0) AND
  (field_data_field_collection_slider0.deleted = :db_condition_placeholder_1)

I saw slow queries via Devel query log for the above query and noticed that since there is no index on the {$field_collection_name}_revision_id field that the query returned 9k rows. The attached patch adds a module update based on logic in module update 7001 to add an index on the {$field_collection_name}_revision_id column for each field collection field table and updates field_collection_field_schema().

CommentFileSizeAuthor
field_collection-add_index.patch1.19 KBjaydub
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

marcoka’s picture

marcoka CreditAttribution: marcoka commented

i have set up a testsystem with 200.000 nodes and i have a performance nightmarle lik viewing a node takes up to 40 seconds (a field collection with 100 items). i applied the patch but the update is not beeing run if i call "drush updb"

jaydub’s picture

jaydub CreditAttribution: jaydub commented

Not sure what the deal was with your attempt @marcoka. I just did a quick install of a basic d7 site with the current dev snapshot of Field Collection. I then applied the patch. Running update.php or drush updatedb both bring up the update 7003 as intended and update applied cleanly.

marcoka’s picture

marcoka CreditAttribution: marcoka commented

my bad. was a multisite and drush somehow downloaded the module in sitedev.foo.de/modules instead of sites/all/modules so i patched a wrong module.
so it works and everything is fine. it gave me some pretty boost in generating the pages (i use a pre caching script in drush)

this one seems interesing too #1350076: Add index to FIELDNAME_value column on field_collection field tables

jaydub’s picture

jaydub CreditAttribution: jaydub commented

Thanks @marcoka for the other issue ref. Looks like that patch is based on old version of FC before revisioning went in. The EntityFieldQuery that is run now is not on 'value' field but is on 'revision_id' field so patch here takes care of that and no need to index 'value' field.

jamix’s picture

jamix CreditAttribution: jamix commented

Thank you, that worked beautifully over here.

Kars-T’s picture

Kars-T CreditAttribution: Kars-T commented
Status: Needs review » Reviewed & tested by the community

Hi

we have a lot of very slow queries because of the missing index on one of our larger sites. The result of the missing index are full table scans on thousands of rows.

The patch is quiet simple and all our field collections table got the index added.
Without the patch we have a full table scan with 59048 rows. With the patch we have just 1 row.

I RTBC this even if there is no \n at file end.

fago’s picture

fago
Primary language German
Location Vienna
CreditAttribution: fago commented
Status: Reviewed & tested by the community » Fixed

Thanks - good catch. I updated the update number and committed it.

fago’s picture

fago
Primary language German
Location Vienna
CreditAttribution: fago commented

>I RTBC this even if there is no \n at file end.
Fixed that also ;-)

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

chr.fritsch’s picture

chr.fritsch
he/him
Primary language German
Location 🇩🇪🇪🇺🌍
CreditAttribution: chr.fritsch commented

Dont't forget to update your features after applying this path. Otherwise there might be issues with drush cc or drush fr

Pls’s picture

Pls CreditAttribution: Pls commented

I can only confirm that without this patch server and overall performance is just awful. Full table scans are real expensive, when there's about 60k rows in each revision field..

Applied the patch and it solved the problem. I think it should be released in beta6, as dev for most of users seems unstable.

acbramley’s picture

acbramley CreditAttribution: acbramley commented

We are getting errors on updb after applying this patch and upgrading features:

Cannot add index <em class="placeholder">field_course_block_revision_id</em> to table <em class="placeholder">field_data_field_course_block</em>: index already exists.
acbramley’s picture

acbramley CreditAttribution: acbramley commented

Nevermind, found the fix in beta10 :)