Feature req/idea: Improve Magento catalog performance by cross indexing with STATUS attribute || creating a new attribute in catalog_product_entity

[seansan]

Description (*)

Often Magento holds 'old data' products that are no longer sold or not now. One would use the status field to disable the products OR delete the products. Both have downsides.

• Disabling products = still slow as it requires an eav lookup and has no (direct) index
• Delete products = data is gone ... and one might need it again, can also cause problems in reports (and scripts) and other dependencies

Possible solution 1 It would be great if one could really! disable a product directly in the catalog_product_entity table the highest level in the db hierarchy - with for an example and indexed status column or indexed id/status column. All product related queries could then have 1 extra clause for example like "where archived = 0" (or "where active = 1") etc. that can be inserted as a default clause. This way the product data does still exist ... but is totally ignored.

Possible solution 2 Or another solution would be to store some view or logic in the DB where a direct relation (index) is set and used for the catalog_product_entity table and the eav_int table where product status is stored.

Expected behavior or idea (*)

Possible solution 1

• New column in catalog_product_entity e.g. archived
• Column archived defaults to 0
• Column can be set via massupdate in grid or edit product
• Add required indexes
• Add exclusion clause in (all) base product collection queries ... is there 1 base location for catalog_product_entity sql or does i need to be changed in more locations?
• Ability in config to enable or disable this behavior?

Update. Just added a thought => if we purge "archived" products then there must be a way in admin catalog grid to "force show" these products again if we want to mass update them back to "normal" (ie unarchived)

Possible solution 2
Created stored logic in modern DB? stored (warmed) view?

Benefits

Could significantly improve catalog performance in backend, frontend everywhere
Product data is not lost, but temporarily obsolete (and gone) for the system - until activated again

Additional information

Just an idea from a non techie ...