Database Indexing - maps for faster searching

Cartographers! Consul Caesar.js noticed that searching for tributes in our journals takes longer and longer. It's time to create indexes - special maps that will allow you to instantly find any tribute in our collection!

What are indexes in the world of legionaries?

Imagine you have thousands of tribute maps in a large chest. Without organization:

• Every search = going through all maps one by one
• Finding a tribute can take hours
• The more maps, the longer the search

Indexes are like a library catalog - they allow you to instantly find the right map!

Basic indexes in TypeORM

1// tribute.entity.ts
2import { Entity, Column, Index, PrimaryGeneratedColumn } from 'typeorm';
3
4@Entity('tributes')
5@Index(['location', 'collectedAt']) // Composite index
6@Index(['value'], { where: 'value > 1000' }) // Partial index
7export class Tribute {
8 @PrimaryGeneratedColumn()
9 id: number;
10
11 @Column()
12 @Index() // Simple index on name
13 name: string;
14
15 @Column('decimal')
16 @Index('idx_tribute_value') // Named index
17 value: number;
18
19 @Column()
20 @Index() // Index on location for geospatial queries
21 location: string;
22
23 @Column({ type: 'enum', enum: ['GOLD', 'SILVER', 'GEMS'] })
24 @Index() // Index on type for filtering
25 type: string;
26
27 @Column({ type: 'timestamp' })
28 @Index() // Index on timestamp for date ranges
29 collectedAt: Date;
30
31 @Column({ unique: true })
32 serialNumber: string; // Automatic unique index
33
34 @Column('text')
35 @Index({ fulltext: true }) // Full-text search index (MySQL)
36 description: string;
37
38 @Column('point', { spatialFeatureType: 'Point', srid: 4326 })
39 @Index({ spatial: true }) // Spatial index for geo queries
40 coordinates: string;
41}

Strategic indexes for different use cases

1// indexing-strategy.service.ts
2@Injectable()
3export class IndexingStrategyService {
4 constructor(
5 @InjectRepository(Tribute) private tributeRepo: Repository<Tribute>,
6 @InjectDataSource() private dataSource: DataSource,
7 ) {}
8
9 // Checking index usage
10 async analyzeIndexUsage(): Promise<any> {
11 const queries = [
12 // Check the query execution plan
13 `EXPLAIN SELECT * FROM tributes WHERE type = 'GOLD' AND value > 1000`,
14 `EXPLAIN SELECT * FROM tributes WHERE location LIKE 'Hispania%'`,
15 `EXPLAIN SELECT * FROM tributes WHERE discovered_at BETWEEN '2023-01-01' AND '2023-12-31'`,
16 ];
17
18 const results = [];
19 for (const query of queries) {
20 const plan = await this.dataSource.query(query);
21 results.push({ query, plan });
22 }
23
24 return results;
25 }
26
27 // Creating dynamic indexes
28 async createPerformanceIndexes(): Promise<void> {
29 const queryRunner = this.dataSource.createQueryRunner();
30 
31 try {
32 // Index for frequent searches by value and type
33 await queryRunner.query(`
34 CREATE INDEX IF NOT EXISTS idx_tribute_type_value 
35 ON tributes(type, value DESC)
36 `);
37
38 // Partial index only for expensive tributes
39 await queryRunner.query(`
40 CREATE INDEX IF NOT EXISTS idx_expensive_tributes 
41 ON tributes(discovered_at) 
42 WHERE value > 10000
43 `);
44
45 // Composite index for geospatial + time queries
46 await queryRunner.query(`
47 CREATE INDEX IF NOT EXISTS idx_location_time 
48 ON tributes(location, discovered_at)
49 `);
50
51 // Covering index (contains all needed columns)
52 await queryRunner.query(`
53 CREATE INDEX IF NOT EXISTS idx_tribute_summary 
54 ON tributes(type, location) 
55 INCLUDE (name, value, discovered_at)
56 `);
57
58 console.log('✅ Performance indexes created!');
59 } catch (error) {
60 console.error('❌ Error while creating indexes:', error);
61 } finally {
62 await queryRunner.release();
63 }
64 }
65
66 // Optimizing queries that use indexes
67 async getOptimizedTributeSearches() {
68 // ✅ Uses index on (type, value)
69 const goldTributes = await this.tributeRepo
70 .createQueryBuilder('tribute')
71 .where('tribute.type = :type AND tribute.value > :minValue', {
72 type: 'GOLD',
73 minValue: 1000
74 })
75 .orderBy('tribute.value', 'DESC')
76 .limit(100)
77 .getMany();
78
79 // ✅ Uses partial index on expensive tributes
80 const expensiveTributes = await this.tributeRepo
81 .createQueryBuilder('tribute')
82 .where('tribute.value > 10000')
83 .andWhere('tribute.collectedAt > :date', { 
84 date: new Date('2023-01-01') 
85 })
86 .getMany();
87
88 // ✅ Uses spatial index
89 const nearbyTributes = await this.dataSource.query(`
90 SELECT * FROM tributes 
91 WHERE ST_DWithin(
92 coordinates, 
93 ST_GeomFromText('POINT(-74.006 40.7128)', 4326), 
94 1000
95 )
96 `);
97
98 return { goldTributes, expensiveTributes, nearbyTributes };
99 }
100}

Full-text Search for tribute descriptions

1// full-text-search.service.ts
2@Injectable()
3export class FullTextSearchService {
4 constructor(
5 @InjectRepository(Tribute) private tributeRepo: Repository<Tribute>,
6 @InjectDataSource() private dataSource: DataSource,
7 ) {}
8
9 async setupFullTextSearch(): Promise<void> {
10 const queryRunner = this.dataSource.createQueryRunner();
11 
12 try {
13 // PostgreSQL full-text search
14 await queryRunner.query(`
15 ALTER TABLE tributes 
16 ADD COLUMN IF NOT EXISTS search_vector tsvector
17 `);
18
19 await queryRunner.query(`
20 UPDATE tributes 
21 SET search_vector = to_tsvector('english', name || ' ' || description)
22 `);
23
24 await queryRunner.query(`
25 CREATE INDEX IF NOT EXISTS idx_tribute_search 
26 ON tributes USING gin(search_vector)
27 `);
28
29 // Trigger for automatic updates
30 await queryRunner.query(`
31 CREATE OR REPLACE FUNCTION update_tribute_search_vector()
32 RETURNS TRIGGER AS $$
33 BEGIN
34 NEW.search_vector := to_tsvector('english', NEW.name || ' ' || NEW.description);
35 RETURN NEW;
36 END;
37 $$ LANGUAGE plpgsql;
38 `);
39
40 await queryRunner.query(`
41 DROP TRIGGER IF EXISTS tribute_search_vector_update ON tributes;
42 CREATE TRIGGER tribute_search_vector_update
43 BEFORE INSERT OR UPDATE ON tributes
44 FOR EACH ROW EXECUTE FUNCTION update_tribute_search_vector();
45 `);
46
47 console.log('✅ Full-text search configured!');
48 } catch (error) {
49 console.error('❌ Error configuring full-text search:', error);
50 } finally {
51 await queryRunner.release();
52 }
53 }
54
55 async searchTributes(searchTerm: string): Promise<any[]> {
56 // PostgreSQL full-text search with ranking
57 const results = await this.dataSource.query(`
58 SELECT 
59 t.*,
60 ts_rank(search_vector, plainto_tsquery('english', $1)) as rank
61 FROM tributes t
62 WHERE search_vector @@ plainto_tsquery('english', $1)
63 ORDER BY rank DESC, value DESC
64 LIMIT 50
65 `, [searchTerm]);
66
67 return results;
68 }
69
70 async advancedSearch(query: {
71 text?: string;
72 type?: string;
73 minValue?: number;
74 location?: string;
75 dateRange?: { start: Date; end: Date };
76 }): Promise<any[]> {
77 const qb = this.tributeRepo.createQueryBuilder('tribute');
78
79 // Full-text search condition
80 if (query.text) {
81 qb.addSelect(`ts_rank(tribute.search_vector, plainto_tsquery('english', :searchText))`, 'rank')
82 .where('tribute.search_vector @@ plainto_tsquery('english', :searchText)', {
83 searchText: query.text
84 });
85 }
86
87 // Additional filters (use indexes)
88 if (query.type) {
89 qb.andWhere('tribute.type = :type', { type: query.type });
90 }
91
92 if (query.minValue) {
93 qb.andWhere('tribute.value >= :minValue', { minValue: query.minValue });
94 }
95
96 if (query.location) {
97 qb.andWhere('tribute.location LIKE :location', { 
98 location: `%${query.location}%` 
99 });
100 }
101
102 if (query.dateRange) {
103 qb.andWhere('tribute.collectedAt BETWEEN :start AND :end', {
104 start: query.dateRange.start,
105 end: query.dateRange.end
106 });
107 }
108
109 // Sorting: first by relevance, then by value
110 if (query.text) {
111 qb.orderBy('rank', 'DESC').addOrderBy('tribute.value', 'DESC');
112 } else {
113 qb.orderBy('tribute.value', 'DESC');
114 }
115
116 return await qb.limit(100).getRawAndEntities();
117 }
118}