Avoiding Loop Queries: Performance Optimizations for MySQL, MongoDB, and Redis

Poorly written code that repeatedly queries a database inside a loop can severely impact maintainability, performance, and team collaboration; therefore, designing before implementation is essential.

When the author first took over a company’s reporting system, two main issues were identified: missing or sub‑optimal indexes and numerous loop‑based queries that caused daily reports to run extremely slowly.

The article presents three practical examples covering MySQL, MongoDB, and Redis to illustrate how to eliminate loop queries and boost performance.

1. Replace for‑loop with IN query (MySQL)

Original code (simplified):

sql = 'SELECT A.real_name, A.phone, A.gender FROM tb_user AS A INNER JOIN tb_trade AS B on A.id=B.user_id WHERE B.id=%s;'
for id in trade_ids:
    user = db_mysql.find(sql, [id])
    # TODO: do some work

This approach queries the database for each trade ID, leading to increasing latency as the number of trades grows, even with indexes.

Optimized code:

sql = 'SELECT A.real_name, A.phone, A.gender, B.id FROM tb_user AS A INNER JOIN tb_trade AS B on A.id=B.user_id WHERE B.id IN (%s);'
place_holders = ','.join(map(lambda x: '%s', id_list))
users = db_mysql.findAll(sql % place_holders, [trade_ids])
for user in users:
    # TODO: do some work

By using an IN clause, all required rows are fetched in a single query, and the loop only processes the already‑retrieved data.

2. Use aggregation to replace for‑loop (MongoDB)

Original code (simplified):

avaliable_companies = []
condition = {
  'is_active': True,
  'create_time': {'$lt': datatime.now()},
  'suspended': False
}
for company in companies:
    condition['company'] = company['_id']
    job = db_mongo.job.find_one(condition)
    if job:
        avaliable_companies.append(job)
count = len(avaliable_companies)

This loop performs a separate query for each company, which becomes slower as the dataset expands.

Optimized code using aggregation:

pipeline = [
  {'$match': {
    'is_active': True,
    'create_time': {'$lt': datatime.now()},
    'suspended': False,
    'company': {'$in': map(lambda x: x['_id'], companies)}
  }},
  {'$group': {'_id': 'company'}}
]
agg_result = db_mongo.job.aggregate(pipeline)
count = len(list(agg_result))

The aggregation pipeline retrieves the result set in a single operation, dramatically reducing query overhead.

3. Use Redis pipeline to batch requests

Original code (simplified):

redis_cli = get_redis()
for id in user_ids:
    result = redis_cli.get('user_last_active_time:%d' % id)

Fetching each key individually creates a separate I/O request, which is inefficient for large user lists.

Optimized code with Redis pipeline:

redis_cli = get_redis()
pipeline = redis_cli.pipeline(transaction=False)
for id in user_ids:
    pipeline.get('user_last_active_time:%d' % id)
active_time_list = pipeline.execute()

Batching the GET commands via a pipeline reduces round‑trip latency and can be dozens of times faster for large datasets.

Through these three examples, the author emphasizes that thoughtful design and bulk‑operation techniques are key to writing maintainable, high‑performance code.

Enjoy the article? Please like, bookmark, and share!