Slow database query

Problem/Motivation

Our directory listing pages were taking a very long time to load - anywhere from 6 seconds on a good day to 30+ seconds when under load.

I profiled the code and tracked most of that time down to this function call in MetadataEventSubscriber::setMetadata():

        $data = $this->createData($name, $fid, $file, $subdir_fid);
        $query = \Drupal::entityQuery('filebrowser_metadata_entity')
          ->condition('fid', $fid)
          ->condition('module', 'filebrowser')
          ->condition('name', $name);
        $entity_id = $query->execute();

Adding an index to the table reduced the page load time to around 2.5 seconds on a good day (not sure about a bad day yet), and the total database query time from around 4 seconds to just 235 ms (measured by WebProfiler).

CREATE INDEX fid_module_name USING BTREE ON filebrowser_metadata_entity (fid, module, name);

Steps to reproduce

Not sure exactly... We have 34,033 records in that particular table, so maybe just having a lot of files?

Proposed resolution

If adding the index automatically is an option, I would recommend it.

Otherwise I guess this is just for others who might have the same problem to try.

Thanks!

Remaining tasks

User interface changes

API changes

Data model changes