TL;DR

The SQL query from ActiveRecord was easy but inefficient. I rewrote it in raw SQL, making it 4x faster (0.58s to 0.14s).

Context

Preparing for technical job interviews can feel like shooting in the dark—you never know what kinds of questions a company will ask. These could be technical, behavioral, or situational, and they vary greatly across companies. You might be rejected from a role just by being a bit unlucky. Yikes.

To address this, we launched NodeFlair Interviews four months ago to help job seekers research past job interview questions from various companies. Since then, it has become one of our most popular products.

The Issue

As the number of interview questions in our database and user engagement have increased, the query used to retrieve all companies for the filter, sorted by the number of questions, has also grown.

Before (ActiveRecord)

A simple benchmark for the query shows it took an average of ~0.58 seconds. This isn’t a huge issue by itself, but with a large number of requests, it can quickly add up.

I examined the query used to generate the options and saw a typical ActiveRecord query, which didn’t seem to have much room for optimization.

Or does it…?

Company
.joins(:interview_questions)
.merge(CompanyInterviewQuestion.showable)
.group("companies.id")
.order("COUNT(company_interview_questions.id) DESC")

After (Raw SQL)

Inspecting the underlying SQL query generated by ActiveRecord revealed it wasn’t optimized for our needs. This led me to consider rewriting it in raw SQL.

Company
.joins("
  INNER JOIN (#{
    CompanyInterviewQuestion
    .showable
    .select(:company_id)
    .group(:company_id)
    .order('COUNT(id) DESC')
    .to_sql
  }) AS ciq ON ciq.company_id = companies.id
")

After running a few benchmarks, I’m glad that it actually worked!

The refactored method using raw SQL now averages ~0.14 seconds, a 4x improvement!