Hi, following query takes very very long time (over 30 minutes) to finish.
I have database under disk with 2GB/s IO capabilities, and most cores are idle.. It takes probably one core to max, and tries to process the query. Perhaps some index is missing?
It is fresh Magento 2.3.3 installation with sampledata installed..
Should I file bug report?
INSERT INTO `search_tmp_5dc53d541f7be3_24515526`
SELECT `main_select`.`entity_id`,
Sum(score) AS `relevance`
FROM (SELECT DISTINCT `search_index`.`entity_id`,
( ( ( 0 ) + ( 0 ) + ( 0 ) + ( 0 ) + ( 0 ) + ( 0 ) + ( 0
) + ( 0
) + ( 0 ) + ( 0
)
+
( 0 ) + ( 0 ) ) * 1 ) AS `score`
FROM `catalog_product_index_eav` AS `search_index`
INNER JOIN `catalog_product_index_eav` AS `color_filter`
ON `search_index`.`entity_id` =
`color_filter`.`entity_id`
AND `color_filter`.`attribute_id` = 93
AND `color_filter`.`store_id` = 1
INNER JOIN `catalog_product_index_eav` AS `material_filter`
ON `search_index`.`entity_id` =
`material_filter`.`entity_id`
AND `material_filter`.`attribute_id` = 137
AND `material_filter`.`store_id` = 1
INNER JOIN `catalog_product_index_eav` AS `size_filter`
ON `search_index`.`entity_id` = `size_filter`.`entity_id`
AND `size_filter`.`attribute_id` = 142
AND `size_filter`.`store_id` = 1
INNER JOIN `catalog_product_index_eav` AS `eco_collection_filter`
ON `search_index`.`entity_id` =
`eco_collection_filter`.`entity_id`
AND `eco_collection_filter`.`attribute_id` = 143
AND `eco_collection_filter`.`store_id` = 1
INNER JOIN `catalog_product_index_eav` AS
`performance_fabric_filter`
ON `search_index`.`entity_id` =
`performance_fabric_filter`.`entity_id`
AND `performance_fabric_filter`.`attribute_id` = 144
AND `performance_fabric_filter`.`store_id` = 1
INNER JOIN `catalog_product_index_eav` AS `new_filter`
ON `search_index`.`entity_id` = `new_filter`.`entity_id`
AND `new_filter`.`attribute_id` = 146
AND `new_filter`.`store_id` = 1
INNER JOIN `catalog_product_index_eav` AS `sale_filter`
ON `search_index`.`entity_id` = `sale_filter`.`entity_id`
AND `sale_filter`.`attribute_id` = 147
AND `sale_filter`.`store_id` = 1
INNER JOIN `catalog_product_index_eav` AS `style_general_filter`
ON `search_index`.`entity_id` =
`style_general_filter`.`entity_id`
AND `style_general_filter`.`attribute_id` = 150
AND `style_general_filter`.`store_id` = 1
INNER JOIN `catalog_product_index_eav` AS `pattern_filter`
ON `search_index`.`entity_id` =
`pattern_filter`.`entity_id`
AND `pattern_filter`.`attribute_id` = 153
AND `pattern_filter`.`store_id` = 1
INNER JOIN `catalog_product_index_eav` AS `climate_filter`
ON `search_index`.`entity_id` =
`climate_filter`.`entity_id`
AND `climate_filter`.`attribute_id` = 154
AND `climate_filter`.`store_id` = 1
INNER JOIN `cataloginventory_stock_status` AS `stock_index`
ON stock_index.product_id = search_index.entity_id
AND `stock_index`.`website_id` = 0
AND `stock_index`.`stock_status` = 1
AND `stock_index`.`stock_id` = 1
INNER JOIN `cataloginventory_stock_status` AS
`sub_products_stock_index`
ON sub_products_stock_index.product_id =
search_index.source_id
AND `sub_products_stock_index`.`website_id` = 0
AND `sub_products_stock_index`.`stock_status` = 1
AND `sub_products_stock_index`.`stock_id` = 1
INNER JOIN `catalog_product_index_eav` AS `visibility_filter`
ON search_index.entity_id = visibility_filter.entity_id
AND `visibility_filter`.`attribute_id` = 99
AND `visibility_filter`.`value` IN ( '2', '4' )
AND `visibility_filter`.`store_id` = '1'
INNER JOIN `catalog_category_product_index_store1` AS
`category_ids_index`
ON search_index.entity_id = category_ids_index.product_id
AND category_ids_index.store_id = '1'
WHERE ( search_index.store_id = '1' )
AND ( category_ids_index.category_id = 17 )
AND ( color_filter.value = '56' )
AND ( material_filter.value = '152' )
AND ( size_filter.value = '171' )
AND ( eco_collection_filter.value = '0' )
AND ( performance_fabric_filter.value = '0' )
AND ( new_filter.value = '0' )
AND ( sale_filter.value = '0' )
AND ( style_general_filter.value = '135' )
AND ( pattern_filter.value = '197' )
AND ( climate_filter.value = '209' )) AS `main_select`
GROUP BY `entity_id`
ORDER BY `relevance` DESC,
`entity_id` DESC
LIMIT 10000
echo "installing magento"
php bin/magento setup:install --base-url=$BASE_URL --db-host=$DB_HOST --db-name=$DB_NAME --db-user=$DB_USER --db-password=$DB_PASS --admin-firstname=$ADMIN_FORNAME --admin-lastname=$ADMIN_LASTNAME --admin-email=$ADMIN_EMAIL --admin-user=$ADMIN_USER --admin-password=$ADMIN_PASS --language=$DEFAULT_LANG --currency=$DEFAULT_CURR --timezone=$DEFAULT_TIMEZONE --use-rewrites=1
echo "copying env.php and auth.json"
cp -f ../env.php ./app/etc/env.php
cp ../auth.json ./var/composer_home/auth.json
# install sample data
echo "copying sampledata:deploy"
bin/magento sampledata:deploy
echo "copying setup:di:compile"
bin/magento setup:di:compile
echo "copying setup:upgrade"
bin/magento setup:upgrade
#do release of phrases
echo "cache:clean"
bin/magento cache:clean
#bin/magento setup:static-content:deploy -f en_US de_DE de_CH sk_SK
bin/magento indexer:reindex
bin/magento i18n:collect-phrases -o "$VER.csv" -m
find . -type f -print0 | xargs -r0 chmod 660
find . -type d -print0 | xargs -r0 chmod 770
chmod -R g+w ./{pub,var}
chmod 0770 {bin,generated} -R
echo "setup:upgrade"
bin/magento setup:upgrade
echo "setup:di:compile"
bin/magento setup:di:compile
echo "cache:flush"
bin/magento cache:flush
echo "cache:clean"
bin/magento cache:clean
bin/magento setup:static-content:deploy -f
echo "indexer:reindex"
bin/magento indexer:reindex
