Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Long running queries presumably from layered navigation #3924

Open
ioweb-gr opened this issue Apr 5, 2024 · 13 comments
Open

Long running queries presumably from layered navigation #3924

ioweb-gr opened this issue Apr 5, 2024 · 13 comments

Comments

@ioweb-gr
Copy link
Contributor

ioweb-gr commented Apr 5, 2024

Preconditions (*)

  1. Openmage 20.5.0
  2. PHP 8.1
  3. MariaDB 10.617
  4. Ubuntu 20.04

Steps to reproduce (*)

There are no specific steps to reproduce this , we just see it in the slow query logs

We keep observing some long running queries of this format

SELECT `extra_specs_idx`.`value`, COUNT(extra_specs_idx.entity_id) AS `count` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '15' INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 INNER JOIN `catalog_product_index_eav` AS `screen_size_idx` ON screen_size_idx.entity_id = e.entity_id AND screen_size_idx.attribute_id = 187 AND screen_size_idx.store_id = 1 AND screen_size_idx.value = '236' INNER JOIN `catalog_product_index_eav` AS `screen_type_idx` ON screen_type_idx.entity_id = e.entity_id AND screen_type_idx.attribute_id = 189 AND screen_type_idx.store_id = 1 AND screen_type_idx.value = '266' INNER JOIN `catalog_product_index_eav` AS `core_count_idx` ON core_count_idx.entity_id = e.entity_id AND core_count_idx.attribute_id = 193 AND core_count_idx.store_id = 1 AND core_count_idx.value = '41' INNER JOIN `catalog_product_index_eav` AS `memory_idx` ON memory_idx.entity_id = e.entity_id AND memory_idx.attribute_id = 197 AND memory_idx.store_id = 1 AND memory_idx.value = '61' INNER JOIN `catalog_product_index_eav` AS `primary_camera_idx` ON primary_camera_idx.entity_id = e.entity_id AND primary_camera_idx.attribute_id = 198 AND primary_camera_idx.store_id = 1 AND primary_camera_idx.value = '309' INNER JOIN `catalog_product_index_eav` AS `second_camera_idx` ON second_camera_idx.entity_id = e.entity_id AND second_camera_idx.attribute_id = 200 AND second_camera_idx.store_id = 1 AND second_camera_idx.value = '87' INNER JOIN `catalog_product_index_eav` AS `megethossim_idx` ON megethossim_idx.entity_id = e.entity_id AND megethossim_idx.attribute_id = 229 AND megethossim_idx.store_id = 1 AND megethossim_idx.value = '164' INNER JOIN `catalog_product_index_eav` AS `gdiktia_idx` ON gdiktia_idx.entity_id = e.entity_id AND gdiktia_idx.attribute_id = 232 AND gdiktia_idx.store_id = 1 AND gdiktia_idx.value = '168' INNER JOIN `catalog_product_index_eav` AS `ram_idx` ON ram_idx.entity_id = e.entity_id AND ram_idx.attribute_id = 196 AND ram_idx.store_id = 1 AND ram_idx.value = '52' INNER JOIN `catalog_product_index_eav` AS `nfcsosto_idx` ON nfcsosto_idx.entity_id = e.entity_id AND nfcsosto_idx.attribute_id = 258 AND nfcsosto_idx.store_id = 1 AND nfcsosto_idx.value = '376' INNER JOIN `catalog_product_index_eav` AS `extra_specs_idx` ON extra_specs_idx.entity_id = e.entity_id AND extra_specs_idx.attribute_id = 213 AND extra_specs_idx.store_id = '1' GROUP BY `extra_specs_idx`.`value`

Which pretty much never stop executing until they timeout

image

They appear to be related to counting terms for layered navigation.

The related tables range from 200 rows to 2000 rows so they're not extremely big and the execution shouldn't be that slow for any reason.

We have disabled showing the count in layered navigation

image

Here's another sample query with very long execution time and explain

# Time: 240405  7:37:19
# User@Host: db_domain3[db_domain3] @ localhost [127.0.0.1]
# Thread_id: 8345298  Schema: db_domain3  QC_hit: No
# Query_time: 140.075668  Lock_time: 0.000176  Rows_sent: 1  Rows_examined: 15
# Rows_affected: 0  Bytes_sent: 148
# Tmp_tables: 1  Tmp_disk_tables: 0  Tmp_table_sizes: 253976
# Full_scan: No  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: No
#
# explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra
# explain: 1    SIMPLE  ram_idx ref     PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE     4       const   2       2.00    100.00  100.00  Using where; Using index; Using temporary; Using filesort
# explain: 1    SIMPLE  gdiktia_idx     ref     PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,idx_attribute_store_value_entity       IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID 8       db_domain3.ram_idx.entity_id,const,const      1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  primary_camera_idx      range   PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE     4       NULL    2       2.00    75.00   25.00   Using where; Using index; Using join buffer (flat, BNL join)
# explain: 1    SIMPLE  screen_resolution_idx   eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_domain3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  memory_idx      eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_domain3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  second_camera_idx       eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_domain3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  nfcsosto_idx    eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_domain3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  price_index     eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,IDX_CATALOG_PRODUCT_INDEX_PRICE_WEBSITE_ID,IDX_CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE        PRIMARY  8       db_domain3.ram_idx.entity_id,const,const      1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  core_count_idx  eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_domain3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  megethossim_idx eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_domain3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  cat_index       eq_ref  PRIMARY,IDX_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY,15D3C269665C74C2219037D534F4B0DC        PRIMARY 10      const,db_domain3.ram_idx.entity_id,const       1       1.00    100.00  100.00  Using where
# explain: 1    SIMPLE  e       eq_ref  PRIMARY PRIMARY 4       db_domain3.ram_idx.entity_id  1       1.00    100.00  100.00  Using index
#
SET timestamp=1712291839;
SELECT `gdiktia_idx`.`value`, COUNT(gdiktia_idx.entity_id) AS `count` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '17'
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
 INNER JOIN `catalog_product_index_eav` AS `screen_resolution_idx` ON screen_resolution_idx.entity_id = e.entity_id AND screen_resolution_idx.attribute_id = 188 AND screen_resolution_idx.store_id = 1 AND screen_resolution_idx.value = '234'
 INNER JOIN `catalog_product_index_eav` AS `core_count_idx` ON core_count_idx.entity_id = e.entity_id AND core_count_idx.attribute_id = 193 AND core_count_idx.store_id = 1 AND core_count_idx.value = '41'
 INNER JOIN `catalog_product_index_eav` AS `ram_idx` ON ram_idx.entity_id = e.entity_id AND ram_idx.attribute_id = 196 AND ram_idx.store_id = 1 AND ram_idx.value = '54'
 INNER JOIN `catalog_product_index_eav` AS `memory_idx` ON memory_idx.entity_id = e.entity_id AND memory_idx.attribute_id = 197 AND memory_idx.store_id = 1 AND memory_idx.value = '62'
 INNER JOIN `catalog_product_index_eav` AS `primary_camera_idx` ON primary_camera_idx.entity_id = e.entity_id AND primary_camera_idx.attribute_id = 198 AND primary_camera_idx.store_id = 1 AND primary_camera_idx.value = '315'
 INNER JOIN `catalog_product_index_eav` AS `second_camera_idx` ON second_camera_idx.entity_id = e.entity_id AND second_camera_idx.attribute_id = 200 AND second_camera_idx.store_id = 1 AND second_camera_idx.value = '90'
 INNER JOIN `catalog_product_index_eav` AS `megethossim_idx` ON megethossim_idx.entity_id = e.entity_id AND megethossim_idx.attribute_id = 229 AND megethossim_idx.store_id = 1 AND megethossim_idx.value = '164'
 INNER JOIN `catalog_product_index_eav` AS `nfcsosto_idx` ON nfcsosto_idx.entity_id = e.entity_id AND nfcsosto_idx.attribute_id = 258 AND nfcsosto_idx.store_id = 1 AND nfcsosto_idx.value = '377'
 INNER JOIN `catalog_product_index_eav` AS `gdiktia_idx` ON gdiktia_idx.entity_id = e.entity_id AND gdiktia_idx.attribute_id = 232 AND gdiktia_idx.store_id = '1' GROUP BY `gdiktia_idx`.`value`;

There also doesn't seem to be any form of lock causing this delay

Expected result (*)

  1. Queries run fast

Actual result (*)

  1. Queries timeout due to very long execution
@ioweb-gr ioweb-gr added the bug label Apr 5, 2024
@ioweb-gr ioweb-gr changed the title Long running queries from layered navigation Long running queries presumably from layered navigation Apr 5, 2024
@ADDISON74
Copy link
Contributor

Is the version you use in testing OpenMage v20.5.0 + Magento Sample Data?

@ioweb-gr
Copy link
Contributor Author

ioweb-gr commented Apr 5, 2024

I'm seeing this on a production site using OpenMage v20.5.0 so it's not using Magento Sample Data.

I've made sure it occurs with all 3rd party extensions removed as well.

It seems to come directly from core.

The more joins are added to the same table, the more the time of execution increases.

@ADDISON74 ADDISON74 removed the bug label Apr 5, 2024
@fballiano
Copy link
Contributor

would it be possible for you to remove some of those attributes from "available in product listing"?

@ioweb-gr
Copy link
Contributor Author

ioweb-gr commented Apr 5, 2024

Actually most of them are already set to No

Screenshot_2024-04-05-19-09-42-85_984e1414ae90666a90f12ff17ec14a7f.jpg

@fballiano
Copy link
Contributor

there are 9 visible in the query

@ioweb-gr
Copy link
Contributor Author

ioweb-gr commented Apr 5, 2024

I checked them all and only 3 were used in product listing. I changed them to No as well

@ioweb-gr
Copy link
Contributor Author

ioweb-gr commented Apr 6, 2024

Even after changing all to No I still get this query

SELECT `operating_system_idx`.`value`, COUNT(operating_system_idx.entity_id) AS `count` 
FROM `catalog_product_entity` AS `e` 
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '17' 
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 
INNER JOIN `catalog_product_index_eav` AS `screen_resolution_idx` ON screen_resolution_idx.entity_id = e.entity_id AND screen_resolution_idx.attribute_id = 188 AND screen_resolution_idx.store_id = 1 AND screen_resolution_idx.value = '265' 
INNER JOIN `catalog_product_index_eav` AS `screen_type_idx` ON screen_type_idx.entity_id = e.entity_id AND screen_type_idx.attribute_id = 189 AND screen_type_idx.store_id = 1 AND screen_type_idx.value = '39' 
INNER JOIN `catalog_product_index_eav` AS `core_count_idx` ON core_count_idx.entity_id = e.entity_id AND core_count_idx.attribute_id = 193 AND core_count_idx.store_id = 1 AND core_count_idx.value = '41' 
INNER JOIN `catalog_product_index_eav` AS `ram_idx` ON ram_idx.entity_id = e.entity_id AND ram_idx.attribute_id = 196 AND ram_idx.store_id = 1 AND ram_idx.value = '54' 
INNER JOIN `catalog_product_index_eav` AS `primary_camera_idx` ON primary_camera_idx.entity_id = e.entity_id AND primary_camera_idx.attribute_id = 198 AND primary_camera_idx.store_id = 1 AND primary_camera_idx.value = '309' 
INNER JOIN `catalog_product_index_eav` AS `megethossim_idx` ON megethossim_idx.entity_id = e.entity_id AND megethossim_idx.attribute_id = 229 AND megethossim_idx.store_id = 1 AND megethossim_idx.value = '164' 
INNER JOIN `catalog_product_index_eav` AS `nfcsosto_idx` ON nfcsosto_idx.entity_id = e.entity_id AND nfcsosto_idx.attribute_id = 258 AND nfcsosto_idx.store_id = 1 AND nfcsosto_idx.value = '377' 
INNER JOIN `catalog_product_index_eav` AS `operating_system_idx` ON operating_system_idx.entity_id = e.entity_id AND operating_system_idx.attribute_id = 195 AND operating_system_idx.store_id = '1' 
GROUP BY `operating_system_idx`.`value`

@ioweb-gr
Copy link
Contributor Author

ioweb-gr commented Apr 6, 2024

I'm thinking that the culprit may be here as it's one of the few areas I can see that match the query about count(attribute_code_idx)

//app/code/core/Mage/Catalog/Model/Layer/Filter/Attribute.php:115
protected function _getItemsData()
    {
        $attribute = $this->getAttributeModel();
        $this->_requestVar = $attribute->getAttributeCode();

        $key = $this->getLayer()->getStateKey() . '_' . $this->_requestVar;
        $data = $this->getLayer()->getAggregator()->getCacheData($key);

        if ($data === null) {
            $options = $attribute->getFrontend()->getSelectOptions();
            $optionsCount = $this->_getResource()->getCount($this);
            $data = [];
            foreach ($options as $option) {
                if (is_array($option['value'])) {
                    continue;
                }
                if (Mage::helper('core/string')->strlen($option['value'])) {
                    // Check filter type
                    if ($this->_getIsFilterableAttribute($attribute) == self::OPTIONS_ONLY_WITH_RESULTS) {
                        if (!empty($optionsCount[$option['value']])) {
                            $data[] = [
                                'label' => $option['label'],
                                'value' => $option['value'],
                                'count' => $optionsCount[$option['value']],
                            ];
                        }
                    } else {
                        $data[] = [
                            'label' => $option['label'],
                            'value' => $option['value'],
                            'count' => $optionsCount[$option['value']] ?? 0,
                        ];
                    }
                }
            }

            $tags = [
                Mage_Eav_Model_Entity_Attribute::CACHE_TAG . ':' . $attribute->getId()
            ];

            $tags = $this->getLayer()->getStateTags($tags);
            $this->getLayer()->getAggregator()->saveCacheData($data, $key, $tags);
        }
        return $data;
    }

@ioweb-gr
Copy link
Contributor Author

ioweb-gr commented Apr 6, 2024

Check how I replicated it.

I went in a category page with 16 filters in the sidebar's layered navigation (smartphones as products which have a lot of filters)

I progressively started clicking on filters

At around 12 of them even if it was showing only one product. I reached a query like this


SELECT `manufacturer2_idx`.`value`, COUNT(manufacturer2_idx.entity_id) AS `count`
FROM `catalog_product_entity` AS `e`
         INNER JOIN `catalog_category_product_index` AS `cat_index`
                    ON cat_index.product_id = e.entity_id AND cat_index.store_id = 1 AND
                       cat_index.visibility IN (2, 4) AND cat_index.category_id = '15'
         INNER JOIN `catalog_product_index_price` AS `price_index`
                    ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND
                       price_index.customer_group_id = 0
         INNER JOIN `catalog_product_index_eav` AS `screen_resolution_idx`
                    ON screen_resolution_idx.entity_id = e.entity_id AND screen_resolution_idx.attribute_id = 188 AND
                       screen_resolution_idx.store_id = 1 AND screen_resolution_idx.value = '249'
         INNER JOIN `catalog_product_index_eav` AS `screen_type_idx`
                    ON screen_type_idx.entity_id = e.entity_id AND screen_type_idx.attribute_id = 189 AND
                       screen_type_idx.store_id = 1 AND screen_type_idx.value = '37'
         INNER JOIN `catalog_product_index_eav` AS `core_count_idx`
                    ON core_count_idx.entity_id = e.entity_id AND core_count_idx.attribute_id = 193 AND
                       core_count_idx.store_id = 1 AND core_count_idx.value = '41'
         INNER JOIN `catalog_product_index_eav` AS `operating_system_idx`
                    ON operating_system_idx.entity_id = e.entity_id AND operating_system_idx.attribute_id = 195 AND
                       operating_system_idx.store_id = 1 AND operating_system_idx.value = '48'
         INNER JOIN `catalog_product_index_eav` AS `ram_idx`
                    ON ram_idx.entity_id = e.entity_id AND ram_idx.attribute_id = 196 AND ram_idx.store_id = 1 AND
                       ram_idx.value = '52'
         INNER JOIN `catalog_product_index_eav` AS `memory_idx`
                    ON memory_idx.entity_id = e.entity_id AND memory_idx.attribute_id = 197 AND
                       memory_idx.store_id = 1 AND memory_idx.value = '62'
         INNER JOIN `catalog_product_index_eav` AS `primary_camera_idx`
                    ON primary_camera_idx.entity_id = e.entity_id AND primary_camera_idx.attribute_id = 198 AND
                       primary_camera_idx.store_id = 1 AND primary_camera_idx.value = '309'
         INNER JOIN `catalog_product_index_eav` AS `second_camera_idx`
                    ON second_camera_idx.entity_id = e.entity_id AND second_camera_idx.attribute_id = 200 AND
                       second_camera_idx.store_id = 1 AND second_camera_idx.value = '87'
         INNER JOIN `catalog_product_index_eav` AS `megethossim_idx`
                    ON megethossim_idx.entity_id = e.entity_id AND megethossim_idx.attribute_id = 229 AND
                       megethossim_idx.store_id = 1 AND megethossim_idx.value = '164'
         INNER JOIN `catalog_product_index_eav` AS `nfcsosto_idx`
                    ON nfcsosto_idx.entity_id = e.entity_id AND nfcsosto_idx.attribute_id = 258 AND
                       nfcsosto_idx.store_id = 1 AND nfcsosto_idx.value = '377'
         INNER JOIN `catalog_product_index_eav` AS `manufacturer2_idx`
                    ON manufacturer2_idx.entity_id = e.entity_id AND manufacturer2_idx.attribute_id = 186 AND
                       manufacturer2_idx.store_id = '1'
GROUP BY `manufacturer2_idx`.`value`

This query stopped working entirely giving timeout after 1 hour.

Unfortunately on the demo I can only apply 7-8 filters

image

They're not enough to test this on default

However check this URL:

After disabling cache open up this url which will load slightly fast.

https://demo.openmage.org/sale/women.html?apparel_type=33&color=18&length=84&occasion=31&price=220-&size=81&sleeve_length=45

It has 7 filters selected

Try to select the last filter

For me it took a couple of seconds more than the previous request.

Progressively adding more should showcase the issue

@fballiano
Copy link
Contributor

ah ok so those attributes are still filterable, I don't know if there's anything possible here, the query is very complex

@ioweb-gr
Copy link
Contributor Author

ioweb-gr commented Apr 6, 2024

True it's very complex but I think the problem is still that count query. Since I configured the backend not to show the count, shouldn't this count query be totally eliminated?

It also is a significant issue because it makes filters unusable after a point and also with crawlers in the mix it can bring a whole db server down because they will drill down through all the filters

@ioweb-gr
Copy link
Contributor Author

ioweb-gr commented Apr 6, 2024

I posted this on https://dba.stackexchange.com/questions/338347/why-is-this-query-running-so-slowly/338372#338372

Where Dan Black was kind enough to take a look at this. His last suggestion was to set the

optimizer_search_depth = 1

From the default value of 62 as suggested in this KB reference https://mariadb.com/kb/en/entity-attribute-value-implementation/ for EAV implementations.

As it turns out this can also be set for the session only and it made the query run instantly.

I was wondering if we can put this in the mix here because for pretty much all collection related queries that use many joins along so many tables it will give a performance boost.

I'm still exploring this over dba.stackexchange.com if you would like to add some input there as well for this.

I am thinking that basically this is only affecting OpenMage because it's using EAV system which is considered an anti-pattern. Therefore it wouldn't make sense in hosting environments to change this globally, and it may not be good for other types of queries so would it be possible to set it in the OpenMage DB adapter class?

@ioweb-gr
Copy link
Contributor Author

ioweb-gr commented Apr 6, 2024

@fballiano I found the way to mitigate this for now.

On local.xml there's a section called

  <initStatements><![CDATA[SET NAMES utf8]]></initStatements>

I modified it like this for the site experiencing the issue

   <initStatements><![CDATA[SET NAMES utf8;SET SESSION optimizer_search_depth = 1;]]></initStatements>

The problem is successfully mitigated for my case.

The underlying issue is still there though about the multiple joins and we need to find a permanent way to do this for the queries that actually suffer from this and not for the whole connection in my opinion.

For now though this helps

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants