Database Indexes
TechCore Mini ERP implements a comprehensive indexing strategy to optimize query performance across all major tables. These indexes significantly improve search, filtering, and join operations throughout the application.
Overview
The database includes 28 indexes across 11 tables, covering:
- Foreign key relationships for efficient joins
- Frequently searched columns (names, emails, dates)
- Status and state filters
- Filtered indexes for common query patterns
Performance Impact: Properly designed indexes can improve query performance by 10-1000x, especially for large datasets. The indexes below are strategically placed on columns used in WHERE clauses, JOIN conditions, and ORDER BY statements.
Role Table Indexes
IDX_rol_habilitado
CREATE INDEX IDX_rol_habilitado ON rol(habilitado)
- Loading role dropdowns (only active roles)
- User permission checks
- Role management screens
User Table Indexes
IDX_users_code (Unique)
CREATE UNIQUE INDEX IDX_users_code ON users(code)
IDX_users_idrol
CREATE INDEX IDX_users_idrol ON users(idrol)
rol table for permission queries
IDX_users_email
CREATE INDEX IDX_users_email ON users(email)
The username column already has a unique constraint via table definition, which automatically creates an index.
Category Table Indexes
IDX_categoria_codigo (Unique)
CREATE UNIQUE INDEX IDX_categoria_codigo ON categoria(codigo)
IDX_categoria_nombre
CREATE INDEX IDX_categoria_nombre ON categoria(nombre)
IDX_categoria_estado
CREATE INDEX IDX_categoria_estado ON categoria(estado)
Client Table Indexes
IDX_clientes_nombre
CREATE INDEX IDX_clientes_nombre ON clientes(nombre)
IDX_clientes_estado
CREATE INDEX IDX_clientes_estado ON clientes(estado)
IDX_clientes_email
CREATE INDEX IDX_clientes_email ON clientes(email)
Supplier Table Indexes
IDX_proveedores_nombre
CREATE INDEX IDX_proveedores_nombre ON proveedores(nombre)
IDX_proveedores_estado
CREATE INDEX IDX_proveedores_estado ON proveedores(estado)
Product Table Indexes
IDX_productos_descripcion
CREATE INDEX IDX_productos_descripcion ON productos(descripcion)
IDX_productos_idcategoria
CREATE INDEX IDX_productos_idcategoria ON productos(codCategoria)
IDX_productos_estado
CREATE INDEX IDX_productos_estado ON productos(estado)
IDX_productos_stock (Composite)
CREATE INDEX IDX_productos_stock ON productos(stock, stockMinimo)
-- This query uses the composite index efficiently
SELECT * FROM productos
WHERE stock <= stockMinimo
ORDER BY stock
Composite Index: The stock, stockMinimo index is particularly useful for inventory alerts. It allows efficient queries that compare stock levels against minimum thresholds without scanning the entire table.
Sales Table Indexes
IDX_ventas_codclien
CREATE INDEX IDX_ventas_codclien ON ventas(codclien)
IDX_ventas_codvend
CREATE INDEX IDX_ventas_codvend ON ventas(codvend)
IDX_ventas_fecha
CREATE INDEX IDX_ventas_fecha ON ventas(fecha)
IDX_ventas_tipoPago
CREATE INDEX IDX_ventas_tipoPago ON ventas(tipoPago)
IDX_ventas_nula (Filtered Index)
CREATE INDEX IDX_ventas_nula ON ventas(nula) WHERE nula = 0
vw_CuotasVencidas, vw_CuotasPorVencer, vw_EstadoCuenta)
Filtered Index: This index only includes rows where nula = 0 (valid sales). It’s smaller and faster than a full index, perfect for views that always filter out voided transactions.
Sales Detail Table Indexes
IDX_ventasDetalle_norden
CREATE INDEX IDX_ventasDetalle_norden ON ventasDetalle(norden)
IDX_ventasDetalle_codprod
CREATE INDEX IDX_ventasDetalle_codprod ON ventasDetalle(codprod)
Purchase Table Indexes
IDX_compras_codprov
CREATE INDEX IDX_compras_codprov ON compras(codprov)
IDX_compras_codusu
CREATE INDEX IDX_compras_codusu ON compras(codusu)
IDX_compras_fecha
CREATE INDEX IDX_compras_fecha ON compras(fecha)
IDX_compras_estado
CREATE INDEX IDX_compras_estado ON compras(estado)
Purchase Detail Table Indexes
IDX_comprasDetalle_norden
CREATE INDEX IDX_comprasDetalle_norden ON comprasDetalle(norden)
IDX_comprasDetalle_codprod
CREATE INDEX IDX_comprasDetalle_codprod ON comprasDetalle(codprod)
Payment Plan Table Indexes
IDX_planPagos_norden
CREATE INDEX IDX_planPagos_norden ON planPagos(norden)
IDX_planPagos_fechaVencimiento
CREATE INDEX IDX_planPagos_fechaVencimiento ON planPagos(fechaVencimiento)
vw_CuotasVencidas and vw_CuotasPorVencer views
IDX_planPagos_pagada (Filtered Index)
CREATE INDEX IDX_planPagos_pagada ON planPagos(pagada) WHERE pagada = 0
Filtered Index Benefits: By only indexing unpaid installments (pagada = 0), this index:
- Reduces index size by ~50% over time as payments are made
- Speeds up queries that only care about pending payments
- Requires less maintenance and storage
Payment Table Indexes
IDX_abonosVentas_norden
CREATE INDEX IDX_abonosVentas_norden ON abonosVentas(norden)
vw_EstadoCuenta view to calculate total payments
IDX_abonosVentas_fecha
CREATE INDEX IDX_abonosVentas_fecha ON abonosVentas(fecha)
Index Maintenance
Monitoring Index Health
-- Check index fragmentation
SELECT
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
ips.avg_fragmentation_in_percent,
ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
ORDER BY ips.avg_fragmentation_in_percent DESC
Rebuilding Indexes
-- Rebuild fragmented indexes
ALTER INDEX IDX_ventas_fecha ON ventas REBUILD
-- Rebuild all indexes on a table
ALTER INDEX ALL ON ventas REBUILD
Performance Consideration: Index rebuilds can be resource-intensive. Schedule them during low-traffic periods (nights/weekends). For production systems, consider using REORGANIZE instead of REBUILD for indexes with 10-30% fragmentation.
Index Usage Statistics
-- View which indexes are being used
SELECT
OBJECT_NAME(s.object_id) AS TableName,
i.name AS IndexName,
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates,
s.last_user_seek,
s.last_user_scan
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE database_id = DB_ID('TechCore')
ORDER BY s.user_seeks + s.user_scans + s.user_lookups DESC
- Use indexes in WHERE clauses: Queries benefit most when filtering on indexed columns
- Join on indexed columns: All foreign keys have indexes for optimal join performance
- Avoid functions on indexed columns:
WHERE YEAR(fecha) = 2024 won’t use the index; use WHERE fecha >= '2024-01-01' instead
- Leverage filtered indexes: The
nula = 0 and pagada = 0 filtered indexes are optimized for their specific use cases
- Monitor query plans: Use SQL Server’s execution plan analyzer to verify index usage