CODEWITHSUNDEEP
sqlsqlite
Macarse
Macarse

Reputation: 93133

How to know which sqlite queries is faster?

This are my tables:

table|debt|debt|4|CREATE TABLE `debt` (`id` INTEGER PRIMARY KEY AUTOINCREMENT , `title` VARCHAR )

table|debtor|debtor|6|CREATE TABLE `debtor` (`id` INTEGER PRIMARY KEY AUTOINCREMENT , `mail` VARCHAR , `amount` VARCHAR , `coinbaseTxId` VARCHAR , `paid` SMALLINT , `debt_id` INTEGER NOT NULL )
sqlite> select * from debtor;

I want to get every debt that has at least a debtor who didn't pay I tried this:

select * from debt inner join debtor on debt.id = debtor.debt_id where debtor.paid = 0 group by debt.id;

and this:

select * from debt where debt.id in (select debtor.debt_id from debtor where paid = 0 group by debtor.debt_id);

which one is faster?

Upvotes: 0

Views: 47

Answers (1)

CL.
CL.

Reputation: 180020

> EXPLAIN QUERY PLAN select * from debt inner join debtor on debt.id = debtor.debt_id where debtor.paid = 0 group by debt.id;
0|0|1|SCAN TABLE debtor
0|1|0|SEARCH TABLE debt USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|USE TEMP B-TREE FOR GROUP BY
> EXPLAIN QUERY PLAN select * from debt where debt.id in (select debtor.debt_id from debtor where paid = 0 group by debtor.debt_id);
0|0|0|SEARCH TABLE debt USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|0|SCAN TABLE debtor
1|0|0|USE TEMP B-TREE FOR GROUP BY

Both queries are executed in pretty much the same way.

You could speed up the second query by dropping the GROUP BY.

Upvotes: 1

Related Questions