A brief note on writing efficient Graphcool queries

@Graphcool is GraphQL database service that provides a typed API. In contrast to SQL, GraphQL is somewhat less powerful, but has a huge advantage: Querying nested data becomes very convenient. Combined with the built-in permission system, it is a powerful tool and saves plenty of development time.

I have been using Graphcool at a client project for some time, and found it somewhat difficult to predict the efficiency of the queries I was writing. Especially queries involving relations between models (or tables in SQL jargon) can take quite long if done poorly.

A look into Graphcool’s engine

Luckily, the Graphcool framework is open source, so it is possible to investigate. The interesting bits are the schema generating code, and the data resolver.

My main takeaways are:

• Graphcool maps all operations to a MySQL database.
• Graphcool does only index primary and foreign key columns per default.
• Graphcool resolves queries recursively, queries for related nodes are batched.
• Related nodes are found via their parent.
• Filters across relations are done using an exist statement and a corellated subquery.

How to write slow queries

With that in mind, let’s have a look at a query I wrote for querying emails that are associated with users:

query {
 allEmails(filter: { subject_contains: "Hello", user: { id: 1 }}) {
 text
 subject
 }
}

This query is hellishly slow. On a database with 500.000 emails and about 200 users it takes almost 5 seconds!

Lets break it down with the insights we had before:

• MySQL is likely to do a full table scan to find all emails with subjects containing Hello.
• For each email that was found, MySQL will execute a correlated subquery with a join and check if there is a user with id 1.

Ouch!

How to write fast queries

The good news: We can easily optimize the above query by exploiting how Graphcool looks the data up.

query {
 User(id: 1) {
 emails(filter: { text_contains: "Hello" }} {
 text
 subject
 }
}

This query is very fast! In fact it completes in less than 200 milliseconds, including the overhead of the HTTP call to Graphcool’s API from my machine.

So what does this query do?

• It queries a single user, using its primary key.
• It queries all articles which are related to the user and applies the filter using a LIKE statement.

To resolve the relation between user and email, the indexed primary and foreign keys are exploited. This happens once. Then, the expensive filtering by text is only applied on a small subset of the data.

Conclusion

This example shows that the whole Graphcool framework is optimized for fetching nested data along relations, not for performing nested filters. That’s fine, as it is the GraphQL way of doing things. However, keeping this limitations in mind can save a lot of headache when scaling up a software project built on Graphcool.