กลยุทธ์ดัชนีและการตรวจสอบแผนการสืบค้น

// Index register for e-commerce capstone
const indexRegister = [
  { collection: 'products', index: { slug: 1 },                        unique: true,  covers: 'product page' },
  { collection: 'products', index: { categoryId: 1, price: 1, _id: 1 }, unique: false, covers: 'category listing + keyset' },
  { collection: 'products', index: { 'vendor._id': 1 },                 unique: false, covers: 'vendor store page' },
  { collection: 'orders',   index: { userId: 1, createdAt: -1 },        unique: false, covers: 'user order history' },
  { collection: 'orders',   index: { status: 1, createdAt: 1 },         unique: false, covers: 'fulfillment queue' },
  { collection: 'reviews',  index: { productId: 1, createdAt: -1 },     unique: false, covers: 'reviews by product' }
]

// Query: products in category, price under 200, sorted by price
// E = categoryId (equality), S = price (sort), R = none
// Correct ESR order:
db.products.createIndex({ categoryId: 1, price: 1 })

// Query: orders by user, status = 'processing', sorted by date
// E = userId (equality) + status (equality), S = createdAt
db.orders.createIndex({ userId: 1, status: 1, createdAt: -1 })

// Verify with explain
db.orders.find({ userId: userId, status: 'processing' })
  .sort({ createdAt: -1 })
  .explain('executionStats')

// indexes/setup.js — run once per environment
async function createIndexes(db) {
  await db.collection('products').createIndexes([
    { key: { slug: 1 }, unique: true },
    { key: { categoryId: 1, price: 1, _id: 1 } },
    { key: { tags: 1 } },
    { key: { 'vendor._id': 1 } }
  ])

  await db.collection('orders').createIndexes([
    { key: { userId: 1, createdAt: -1 } },
    { key: { status: 1, createdAt: 1 } },
    { key: { 'items.productId': 1 } }
  ])

  await db.collection('reviews').createIndexes([
    { key: { productId: 1, createdAt: -1 } },
    { key: { userId: 1 } }
  ])

  console.log('All indexes created')
}

// Validate product category listing query
const result = await db.collection('products')
  .find({ categoryId: new ObjectId('...'), price: { $lte: 200 } })
  .sort({ price: 1 })
  .explain('executionStats')

const { winningPlan, totalKeysExamined, totalDocsExamined, nReturned, executionTimeMillis } = result.executionStats

console.log('Stage:', winningPlan.inputStage.stage)  // IXSCAN or COLLSCAN
console.log('Keys / Docs / Returned:', totalKeysExamined, totalDocsExamined, nReturned)
console.log('Time ms:', executionTimeMillis)

// Covered index for product listing cards
db.products.createIndex({
  categoryId: 1,
  price: 1,
  name: 1,
  slug: 1,
  imageUrl: 1
})

// This query is now covered — no FETCH stage
db.products.find(
  { categoryId: ObjectId('...') },
  { _id: 0, name: 1, slug: 1, price: 1, imageUrl: 1 }
).sort({ price: 1 }).explain('executionStats')
// Verify: no FETCH stage in winningPlan

// Find rarely used indexes
db.orders.aggregate([{ $indexStats: {} }])
  .then(stats => {
    stats.forEach(s => {
      console.log(s.name, 'accesses:', s.accesses.ops)
    })
  })
// Indexes with ops = 0 since last restart may be candidates for removal

// Drop a redundant index
db.orders.dropIndex('userId_1')  // if userId_1_createdAt_-1 already covers it

// Text index for product search
db.products.createIndex(
  { name: 'text', description: 'text', tags: 'text' },
  { weights: { name: 10, tags: 5, description: 1 }, name: 'product_text_idx' }
)

// Text search query with relevance sorting
db.products.find(
  { $text: { $search: 'wireless noise cancelling' } },
  { score: { $meta: 'textScore' } }
).sort({ score: { $meta: 'textScore' } }).limit(20)

// Partial index: only index orders that have a coupon
db.orders.createIndex(
  { couponCode: 1 },
  {
    partialFilterExpression: { couponCode: { $exists: true } },
    name: 'orders_with_coupon'
  }
)

// Partial index: only index active products
db.products.createIndex(
  { categoryId: 1, price: 1 },
  {
    partialFilterExpression: { isActive: true },
    name: 'active_products_by_category'
  }
)

// Unique indexes for business constraints
db.users.createIndex({ email: 1 }, { unique: true })
db.products.createIndex({ slug: 1 }, { unique: true })
db.vendors.createIndex({ slug: 1 }, { unique: true })

// Partial unique index: unique email only for verified users
db.users.createIndex(
  { email: 1 },
  {
    unique: true,
    partialFilterExpression: { emailVerified: true }
  }
)

// Enable profiling to capture slow queries
db.setProfilingLevel(1, { slowms: 50 })  // log queries > 50ms

// Query the profiler for the slowest recent operations
db.system.profile.find().sort({ millis: -1 }).limit(10).pretty()

// Check index usage statistics (reset on mongod restart)
db.products.aggregate([{ $indexStats: {} }])