3 million data - filtering

[cunning turret]

I am using nestjs and mariadb with typeorm.

I have a postcode table that has 3 million entries. the table has fields like id, country, zipCode, city. I put indexes on country, zipCode, city. I can GET uses pagination. Even so, each GET with a limit of 10 takes 3 seconds to complete. It's definitely too much because this endpoint is to be used for input, that you write the name of the city and it tells you the city, it has to be fast. Can it be faster ?

@Entity('zip_codes')
@Index(["country", "zipCode", "city"],)
export class ZipCodeEntity {

  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: 'varchar', nullable: false, length: 2, charset: 'utf8mb3', collation: 'utf8mb3_unicode_ci' })
  // @Index("zip_codes_country", { synchronize: false })
  country: string;

  @Column({ type: 'varchar', nullable: false, length: 12, charset: 'utf8mb3', collation: 'utf8mb3_unicode_ci' })
  // @Index("zip_codes_zipCode", { synchronize: false })
  zipCode: string;

  @Column({ type: 'varchar', nullable: false, length: 50, charset: 'utf8mb3', collation: 'utf8mb3_unicode_ci' })
  // @Index("zip_codes_city", { synchronize: false })
  city: string;


  @Column({
    type: 'decimal',
    precision: 13,
    scale: 9,
    nullable: false,

  })
  latitude: number;

  @Column({
    type: 'decimal',
    precision: 13,
    scale: 9,
    nullable: false,
  })
  longitude: number;
}

async findAllZipCodes(
    filterData: ZipCodeListQuery,

  ): Promise<ApiModel.PaginatedResponse<ZipCodeEntity>> {
    let query = this._zipCodeRepository.createQueryBuilder('zipCode')

    if (filterData.country) {
      query = query.andWhere(
        '(zipCode.country = :country)',
        {
          country: filterData.country,
        },
      );
    }

    if (filterData.query) {
      query = query.andWhere(
        '((zipCode.city LIKE :query) OR (zipCode.country LIKE :query) OR (zipCode.zipCode LIKE :query))',
        {
          query: `%${filterData.query}%`,
        },
      );
    }


    const [data, totalCount]: [ZipCodeEntity[], number] | any = await query
      .select([
        'zipCode.id',
        'zipCode.city',
        'zipCode.zipCode',
        'zipCode.country',
      ])
      .orderBy("zipCode.city", "ASC")
      .skip((filterData.page - 1) * filterData.limit)
      .take(filterData.limit)
      .getManyAndCount()

    return this._preparePaginatedResponse(data, totalCount, filterData)
  }```

[timid raptor]

This post has been marked as resolved. :white_check_mark:
Please read through the conversation and resolution if you are having the same issue, and then re-open the post if you are still having trouble, providing as much extra information as possible.

[cunning turret]

the problem was with typeorm, when i use dataSource and ask db manuallny with raw sql - response was 15x times faster