OpenMage
Improved performance for catalog queries using UNIQUE INDEX catalog_product_entity_int(entity_id,attribute_id,store_id,value)
Description (*)
Just sharing, close if not applicable
We are refining some DB updated and slow queries.
Especially about improving catalog load speed by making visibility and status related queries a lot faster ...
visibility and status are used in most if not all catalog related queries (and have made the case before to move these 2 values to catalog_product_entity or even creating a new column in catalog_product_entity called archived)
Today we added 1 index, specifically for the table catalog_product_entity_int because the INT table contains many of the ON/OFF and SELECT OPTION items that apply to products ...
The index (ahum) almost comprises all colums of the table ... so it looks unnatural maybe ... but cut off 65% of query wait time for product lookups related to queries that next to entity_id,attribute_id,store_id also later in the clause actually query the value for, for example visibility and status
CREATE UNIQUE INDEX UNQ_OZSS_CAT_PRD_ENTT_INT_ENTT_ID_ATTR_ID_STORE_ID_VALUE
ON catalog_product_entity_int(entity_id,attribute_id,store_id,value);
Expected behavior (*)
Improve catalog loading speeds
Benefits
Improve catalog loading speeds
Additional information
At the cost of a very large index but the index is used often!
Would appreciate feedback, some testing maybe on yor own system
So, why is it speeding up the query? Is there a query doing a lookup on the value or is it just because the value is now in memory?
Perhaps some explains to show the before and after?
queries use entity_id,attribute_id,store_id in the where clause
only for status and visibility does it add another where clause (often at
then end) for value from the same table
so 1 index can be used instead of 2 or 1 or none ;)
On Tue, Sep 1, 2020 at 4:44 PM joshua-bn [email protected] wrote:
So, why is it speeding up the query? Is there a query doing a lookup on the value or is it just because the value is now in memory?
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/OpenMage/magento-lts/issues/1176#issuecomment-684907319, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAE7I22THFGCFQYD244NXW3SDUCGBANCNFSM4QRY7JVA .