SQLite different execution time for same result

Question

I've got a database with movies, and I wanted to extract every person, who starred in movies from the year 2004, ordered by birth. I've tried different querys which are doing the same, but the first one takes more than 10 times longer to execute than the other. Can someone explain why this happens? (I was using the DB Browser for SQLite, but on the CLI the same thing happens)

First query (exec time about 12-13 Seconds):

SELECT DISTINCT people.name
FROM people 
JOIN stars ON people.id = stars.person_id 
JOIN movies ON movies.id = stars.movie_id 
WHERE movies.year = 2004 
ORDER BY people.birth

Second query (exec time about 1-2 Seconds)

SELECT DISTINCT people.name
FROM movies
JOIN stars ON movies.id = stars.movie_id
JOIN people ON stars.person_id = people.id
WHERE movies.year = 2004
ORDER BY people.birth

DB File: Google Drive (Dont know where to host this any other way sorry :/ )

Well, I think in first example it makes join and after that where, while in the second one it will make where firstly, but I cannot find the proofs =) — Andrey Bessonov, Oct 24 '22 at 20:00
https://stackoverflow.com/questions/30749913/which-performs-first-where-clause-or-join-clause here is a similar subject — Andrey Bessonov, Oct 24 '22 at 20:07
**Runtime** and **running time** are two quite different things. — PChemGuy, Oct 25 '22 at 03:08

score 0 · Answer 1 · answered Oct 25 '22 at 04:32

0

Your database looks pretty poorly. I see missing primary keys, not a single index or the stats table. I think it is pointless to do any performance evaluation until you have the basics properly set.

answered Oct 25 '22 at 04:32

PChemGuy

1,582
3
6
18

SQLite different execution time for same result

1 Answers1