Discrepancy using subquery and join

Question

I am trying to improve some queries on a Android project and I read on many sites that using joins, properly of course, is faster than using subqueries. So i tried changing a query that uses 8 subqueries in one that uses 3 left join, with 3 selects. when running on a test DB i found that the first, using 8 subqueries, is faster than the one using joins.

I tried running the EXPLAIN QUERY PLAN command and got the following results:

Using subqueries

---

Using join

---

---

Am I doing something wrong on my joins? Why does the first query ends faster than the second? Note: Not using those 3 selects inside the joins proved to be quite hard as i need to count how many times a certain data appears.

Answer 1

The answer is simple: It is not true that using JOINs is always faster than using Subqueries. Instead, in my experience, it is more accurate to say that Subqueries are more likely to be used inefficiently.

More info:

Ultimately, as your aware, both Subqueries and JOINs are broken down into a Query plan by your database engine. Your database engine takes shortcuts in the Query plan where it thinks it can based on how it interprets your SQL. So, you can make your SQL more efficient by making it clear what shortcuts can be used.

In your case, your hard work to rewrite the SQL using JOINs seems to have made it harder for the database engine to devise an efficient Query Plan. Notably, the JOIN-based SQL includes an (relatively slow) AUTOMATIC COVERING INDEX; whereas, the database engine was able to stick with existing PRIMARY KEY INDEXes for the Subquery-based SQL.

That said, the JOIN-based Query Plan does appear to be shorter, so it is possible that it will run faster if you're able to build an INDEX accordingly.