Performance Discussion: Indexing languageCode in translatable entities

[asonnleitner]

I'd like to start a discussion about potentially indexing the languageCode column in translatable entities to improve query performance in multi-language stores.

Background

When working extensively with translated content in vendure channels, many queries for translatable entities end up including WHERE clauses filtering by languageCode, especially in services that heavily interact with language-specific content. Currently, the languageCode column in translatable entities (Product, Collection, etc.) is not indexed by default, which might lead to full table scans when searching for specific language content.

Points for Discussion

Performance Implications

• How significant is the performance impact of non-indexed languageCode queries in real-world scenarios?
• Are there existing stores experiencing performance issues related to language filtering?
• What's the typical ratio of reads vs writes for translatable entities in production environments?

Implementation Considerations

1. Storage Impact:

   • Additional disk space required for indexes
   • Minimal impact as languageCode is a small varchar field

2. Write Performance Trade-offs:

   • Minor impact on write operations (INSERT/UPDATE)
   • Would this significantly affect bulk import operations?

3. Implementation Strategy Options:

   • Could be implemented as an optional migration
   • Configurable per-entity or globally
   • Default behavior vs opt-in

Technical Details

Affected entities would include:

• ProductTranslation
• CollectionTranslation
• FacetValueTranslation
• Other translatable entities

Questions for the Community

1. Has anyone implemented similar indexes in their production environments? What were the results?
2. Are there specific use cases where this optimization would be particularly valuable?
3. Would this be better implemented as a configurable option rather than a default behavior?
4. Are there alternative approaches to optimizing language-specific queries that should be considered?

Your Experience

I'm particularly interested in hearing from community members who:

• Run multi-language stores with large product catalogs
• Have implemented custom optimizations for language-specific queries
• Have experience with similar optimizations in other e-commerce platforms

Let's discuss the potential benefits and drawbacks of this approach, and explore whether this would be a valuable addition to Vendure.