Problem: Places loading slowly if in access of 100,000

[johanpiet2]

Current Behavior

Steps to reproduce the behavior

1. Go to '...' We extracted all the places mentioned in archival documents and it is in access of 100,000 The query is slow and I did some enhancements

Expected Behavior

Quicker response

Possible Solution

CREATE INDEX idx_menu_parent_lft ON menu(PARENT_ID, LFT); CREATE INDEX idx_term_parent_taxonomy ON term(PARENT_ID, TAXONOMY_ID, ID); CREATE INDEX idx_term_i18n_id_culture ON term_i18n(id, culture); CREATE INDEX idx_term_parent_taxonomy_lft_source ON term(PARENT_ID, TAXONOMY_ID, LFT, ID, SOURCE_CULTURE); CREATE INDEX idx_term_parent_taxonomy_lft ON term(PARENT_ID, TAXONOMY_ID, LFT, ID);

I did some indexes and below code replacement I replaces public function getTreeViewSiblings(array $options = []) portion $criteria = new Criteria();

/*

    $criteria->add(QubitTerm::PARENT_ID, $this->parentId);
    $criteria->add(QubitTerm::TAXONOMY_ID, $this->taxonomyId);

    switch ($position) {
        case 'previous':
            $criteria->add(
                'name',
                'COALESCE(
                (CASE
                WHEN (current.NAME IS NOT NULL AND current.NAME <> "")
                    THEN current.NAME
                ELSE
                    source.NAME
                END), "") < '
                .Propel::getConnection()->quote($this->getName(['cultureFallback' => true])),
                Criteria::CUSTOM
            );

            $criteria->addDescendingOrderByColumn('name');
            $criteria->addDescendingOrderByColumn('lft');

            break;

        case 'next':
        default:
            $criteria->add(
                'name',
                'COALESCE(
                (CASE
                WHEN (current.NAME IS NOT NULL AND current.NAME <> "")
                    THEN current.NAME
                ELSE
                    source.NAME
                END), "") > '
                .Propel::getConnection()->quote($this->getName(['cultureFallback' => true])),
                Criteria::CUSTOM
            );

            $criteria->addAscendingOrderByColumn('name');
            $criteria->addAscendingOrderByColumn('lft');

            break;
    }

    $criteria = QubitCultureFallback::addFallbackCriteria($criteria, 'QubitTerm');
    $criteria->setLimit($limit);

    $results = [];

*/ $criteria->add(QubitTerm::PARENT_ID, $this->parentId); $criteria->add(QubitTerm::TAXONOMY_ID, $this->taxonomyId);

            switch ($position) {
                    case 'previous':
                            $criteria->add(QubitTerm::LFT, $this->lft, Criteria::LESS_THAN);
                            $criteria->addDescendingOrderByColumn(QubitTerm::LFT);
                            break;

                    case 'next':
                    default:
                            $criteria->add(QubitTerm::LFT, $this->lft, Criteria::GREATER_THAN);
                            $criteria->addAscendingOrderByColumn(QubitTerm::LFT);
                            break;
            }

            $criteria = QubitCultureFallback::addFallbackCriteria($criteria, 'QubitTerm');
            $criteria->setLimit($limit);

            // Apply name filtering in PHP after database retrieval
            $results = [];
            $terms = QubitTerm::get($criteria);
            foreach ($terms as $item) {
                    $displayName = $item->getName(['cultureFallback' => true]);

                    // Apply the original string comparison logic in PHP
                    $comparison = ($position === 'previous')
                            ? $displayName < $this->getName(['cultureFallback' => true])
                            : $displayName > $this->getName(['cultureFallback' => true]);

                    if ($comparison) {
                            $results[] = $item;
                            if (count($results) >= $limit) break;
                    }
            }

            return $results;

Context and Notes

No response

Version used

Atom2.8

Operating System and version

No response

Default installation culture

en

PHP version

PHP 7.4

Contact details

[email protected]