MySQL and PostgreSQL don't have covering indexes

[dkindev]

There're many covering indexes in migrations, but they are not used in MySQL and PostgreSQL databases. Example:

nopCommerce/src/Libraries/Nop.Data/Migrations/Installation/Indexes.cs

Lines 65 to 75 in 480dbd5

	Create.Index("IX_PSAM_SpecificationAttributeOptionId_AllowFiltering").OnTable(NameCompatibilityManager.GetTableName(typeof(ProductSpecificationAttribute)))
	.OnColumn(nameof(ProductSpecificationAttribute.SpecificationAttributeOptionId)).Ascending()
	.OnColumn(nameof(ProductSpecificationAttribute.AllowFiltering)).Ascending()
	.WithOptions().NonClustered()
	.Include(nameof(ProductSpecificationAttribute.ProductId));
	Create.Index("IX_PSAM_AllowFiltering").OnTable(NameCompatibilityManager.GetTableName(typeof(ProductSpecificationAttribute)))
	.OnColumn(nameof(ProductSpecificationAttribute.AllowFiltering)).Ascending()
	.WithOptions().NonClustered()
	.Include(nameof(ProductSpecificationAttribute.ProductId))
	.Include(nameof(ProductSpecificationAttribute.SpecificationAttributeOptionId));

How to reproduce the problem?

• Perform a clean installation of nopCommerce for each DBMS: SQLServer, MySQL, PostgreSQL
• View indexes via the UI or SQL
  • PostgreSQL indexes do not have an Include() statement
  • MySQL indexes do not include additional columns

SQLServer

SELECT 
    TableName = OBJECT_NAME(i.object_id),
    IndexName = i.name,
    IndexType = i.type_desc,
    ColumnName = c.name,
    IsIncludedColumn = ic.is_included_column,
    ColumnPosition = ic.index_column_id
FROM 
    sys.indexes i
INNER JOIN 
    sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
INNER JOIN 
    sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE 
    i.object_id = OBJECT_ID('Product_SpecificationAttribute_Mapping')
ORDER BY 
    i.name, 
    ic.is_included_column DESC,
    ic.index_column_id;

MySQL

SELECT DISTINCT 
    TABLE_NAME, 
    INDEX_NAME, 
    COLUMN_NAME
FROM 
   INFORMATION_SCHEMA.STATISTICS
WHERE 
   TABLE_SCHEMA = 'nopdb' AND TABLE_NAME = 'Product_SpecificationAttribute_Mapping';

PostgreSQL

SELECT
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    tablename = 'Product_SpecificationAttribute_Mapping';