Why is this query running so slowly?

Question

My system (OpenMage / Magento 1) is running some queries like the following and even though it's not using any large tables it seems that they take an abnormally high amount of time to execute. Sometimes they take a few minutes, sometimes they will timeout after a day.

For example

# Time: 240405  7:37:19
# User@Host: db_example3[db_example3] @ localhost [127.0.0.1]
# Thread_id: 8345298  Schema: db_example3  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_example3.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_example3.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_example3.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_example3.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_example3.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_example3.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_example3.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_example3.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_example3.ram_idx.entity_id,const       1       1.00    100.00  100.00  Using where
# explain: 1    SIMPLE  e       eq_ref  PRIMARY PRIMARY 4       db_example3.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`;

or

# Time: 240405  7:34:29
# User@Host: db_example3[db_example3] @ localhost [127.0.0.1]
# Thread_id: 8344334  Schema: db_example3  QC_hit: No
# Query_time: 74.418149  Lock_time: 0.000100  Rows_sent: 0  Rows_examined: 0
# Rows_affected: 0  Bytes_sent: 142
# Tmp_tables: 1  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# 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  extra_specs_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_ATTRIBUTE_ID      2       const   1       0.00    100.00  100.00  Using where; Using index; Using temporary; Using filesort
# explain: 1    SIMPLE  manufacturer2_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_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  primary_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_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    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_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  ram_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_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    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_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  operating_system_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_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    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_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    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_example3.extra_specs_idx.entity_id,const,const      1       NULL    100.00  NULL    Using where
# 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_example3.extra_specs_idx.entity_id,const       1       NULL    100.00  NULL    Using where
# explain: 1    SIMPLE  e       eq_ref  PRIMARY PRIMARY 4       db_example3.extra_specs_idx.entity_id  1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  screen_type_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       NULL    75.00   NULL    Using where; Using index; Using join buffer (flat, BNL join)
#
SET timestamp=1712291669;
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 = '3'
 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 `manufacturer2_idx` ON manufacturer2_idx.entity_id = e.entity_id AND manufacturer2_idx.attribute_id = 186 AND manufacturer2_idx.store_id = 1 AND manufacturer2_idx.value = '6'
 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 `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 `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 `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 = '377'
 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' WHERE ((price_index.min_price -(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END)+((price_index.min_price-(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END)) >= 99.995000) AND ((price_index.min_price -(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END)+((price_index.min_price-(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END)) < 199.995000) GROUP BY `extra_specs_idx`.`value`;

As far as I can see the tables are using indexes, and generally nothing is written on the disk, everything is happening in memory so I'm not sure how to optimize and make them run faster.

My mariadb version is

Server version: 10.6.17-MariaDB-1:10.6.17+maria~ubu2004-log mariadb.org binary distribution

Here are the table definitions

create table db_example3.catalog_category_product_index
(
    category_id int unsigned      default 0 not null comment 'Category ID',
    product_id  int unsigned      default 0 not null comment 'Product ID',
    position    int                         null comment 'Position',
    is_parent   smallint unsigned default 0 not null comment 'Is Parent',
    store_id    smallint unsigned default 0 not null comment 'Store ID',
    visibility  smallint unsigned           not null comment 'Visibility',
    primary key (category_id, product_id, store_id),
    constraint FK_CATALOG_CATEGORY_PRODUCT_INDEX_STORE_ID_CORE_STORE_STORE_ID
        foreign key (store_id) references db_example3.core_store (store_id)
            on update cascade on delete cascade,
    constraint FK_CAT_CTGR_PRD_IDX_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID
        foreign key (category_id) references db_example3.catalog_category_entity (entity_id)
            on update cascade on delete cascade,
    constraint FK_CAT_CTGR_PRD_IDX_PRD_ID_CAT_PRD_ENTT_ENTT_ID
        foreign key (product_id) references db_example3.catalog_product_entity (entity_id)
            on update cascade on delete cascade
)
    comment 'Catalog Category Product Index' row_format = COMPRESSED;
create index 15D3C269665C74C2219037D534F4B0DC
    on db_example3.catalog_category_product_index (store_id, category_id, visibility, is_parent, position);
create index IDX_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY
    on db_example3.catalog_category_product_index (product_id, store_id, category_id, visibility);
create table db_example3.catalog_product_index_eav
(
    entity_id    int unsigned      not null comment 'Entity ID',
    attribute_id smallint unsigned not null comment 'Attribute ID',
    store_id     smallint unsigned not null comment 'Store ID',
    value        int unsigned      not null comment 'Value',
    primary key (entity_id, attribute_id, store_id, value),
    constraint FK_CATALOG_PRODUCT_INDEX_EAV_STORE_ID_CORE_STORE_STORE_ID
        foreign key (store_id) references db_example3.core_store (store_id)
            on update cascade on delete cascade,
    constraint FK_CAT_PRD_IDX_EAV_ATTR_ID_EAV_ATTR_ATTR_ID
        foreign key (attribute_id) references db_example3.eav_attribute (attribute_id)
            on update cascade on delete cascade,
    constraint FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID
        foreign key (entity_id) references db_example3.catalog_product_entity (entity_id)
            on update cascade on delete cascade
)
    comment 'Catalog Product EAV Index Table' row_format = COMPRESSED;
create index IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID
    on db_example3.catalog_product_index_eav (attribute_id);
create index IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID
    on db_example3.catalog_product_index_eav (entity_id);
create index IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID
    on db_example3.catalog_product_index_eav (store_id);
create index IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE
    on db_example3.catalog_product_index_eav (value);
create index idx_attribute_store_value_entity
    on db_example3.catalog_product_index_eav (attribute_id, store_id, value, entity_id);
create table db_example3.catalog_product_index_price
(
    entity_id         int unsigned                not null comment 'Entity ID',
    customer_group_id smallint unsigned           not null comment 'Customer Group ID',
    website_id        smallint unsigned           not null comment 'Website ID',
    tax_class_id      smallint unsigned default 0 null comment 'Tax Class ID',
    price             decimal(12, 4)              null comment 'Price',
    final_price       decimal(12, 4)              null comment 'Final Price',
    min_price         decimal(12, 4)              null comment 'Min Price',
    max_price         decimal(12, 4)              null comment 'Max Price',
    tier_price        decimal(12, 4)              null comment 'Tier Price',
    group_price       decimal(12, 4)              null comment 'Group price',
    primary key (entity_id, customer_group_id, website_id),
    constraint FK_CAT_PRD_IDX_PRICE_CSTR_GROUP_ID_CSTR_GROUP_CSTR_GROUP_ID
        foreign key (customer_group_id) references db_example3.customer_group (customer_group_id)
            on update cascade on delete cascade,
    constraint FK_CAT_PRD_IDX_PRICE_ENTT_ID_CAT_PRD_ENTT_ENTT_ID
        foreign key (entity_id) references db_example3.catalog_product_entity (entity_id)
            on update cascade on delete cascade,
    constraint FK_CAT_PRD_IDX_PRICE_WS_ID_CORE_WS_WS_ID
        foreign key (website_id) references db_example3.core_website (website_id)
            on update cascade on delete cascade
)
    comment 'Catalog Product Price Index Table' row_format = COMPRESSED;
create index IDX_CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID
    on db_example3.catalog_product_index_price (customer_group_id);
create index IDX_CATALOG_PRODUCT_INDEX_PRICE_MIN_PRICE
    on db_example3.catalog_product_index_price (min_price);
create index IDX_CATALOG_PRODUCT_INDEX_PRICE_WEBSITE_ID
    on db_example3.catalog_product_index_price (website_id);
create index IDX_CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE
    on db_example3.catalog_product_index_price (website_id, customer_group_id, min_price);

The counted rows on each table

select count(*) from catalog_product_index_eav; #418
select count(*) from catalog_product_index_price; #84
select count(*) from catalog_category_product_index; # 314

The server has an AMD Ryzen 9 3900 12-Core Processor with 128GB of RAM and Nvme disks.

Any help is appreciated

danblack · Accepted Answer · 2024-04-07T01:48:58.750

Edit:

With a properly indexed tables, setting optimizer_search_depth = 1 can reduce the overhead when 8-9+ tables are join.

Its unclear why catalog_product_entity is in the query as the entity_id in the other table could be used as the join criteria. Unless I'm missing something, (quite possible, its a little hard to read), its not used in the query.

catalog_category_product_index should have product_id (why not called entity_id like other tables?) as the first element of the primary key so that its join is simpler. This should show up using PK for this table in the query. This could make a difference if this table was earlier in the processing list.

It could be, that looking at ANALYZE FORMAT=JSON query, that there are multiple iterations of the join buffer causing execution inefficiencies.

As examples used BNL, including @Akina's variant (ref). Raise join_buffer and also join_buffer_space_limit (as a session variable) until there are less loops and see if that improves performance.

You can also experiment with join_cache_level to see if higher levels result in a better execution query plan.

ref: https://mariadb.com/kb/en/block-based-join-algorithms/

score 0 · Answer 2 · answered Apr 21 '24 at 23:58

Idea 1 I suspect that the INNER JOINs are to provide extra columns that are not used in this particular query, not for filtering. So change them to LEFT JOIN and see if the EXPLAIN gets much shorter while still giving the correct answer.

Idea 2

EAV is an especially ugly schema pattern to optimize for.

Let's work on turning the query inside-out; it may help, perhaps significantly. (Yeah, the framework, OpenMage, will get in the way.)

What you have is a lot of JOINing before eliminating most of the stuff via GROUP BY. The inside-out is to start with a "derived table" to find the ids of the rows needed:

SELECT ...
    FROM ( SELECT maybe as little as one id
               FROM as few tables as possible
               GROUP BY ...   -- see note
         ) AS step1
    JOIN back to the original table(s) if you need extra columns
    JOIN to each other table ON ... = step1....;

Note: Often, performing this inside-out obviates the need for the GROUP BY; this gives an extra boost.

SELECT step1.value, COUNT(*) as `count`
    FROM ( SELECT gdiktia_idx.id, gdiktia_idx.value
             FROM  catalog_product_index_eav` AS `gdiktia_idx`
             WHERE  gdiktia_idx.attribute_id = 232
               AND  gdiktia_idx.store_id = '1'
             GROUP BY  `gdiktia_idx`.`value` -- see note
         ) AS step1
    JOIN ...
    JOIN ...
    JOIN ...
    JOIN ... ;

Why is this query running so slowly?

2 Answers2