A note on INNER JOIN and sqlite

April 29, 2009 at 12:20 pm | Posted in Uncategorized | Leave a comment

Compare

robert@forkel02:~/projects/lwt/trunk/lwt
14:11:15$ time sqlite3 devdata.sqlite "select distinct l.name from source_word_donor_language as swdl inner join source_word as sw inner join word_source_word as wsw inner join word as w inner join language as l on swdl.source_word_id = sw.id and sw.id = wsw.source_word_id and wsw.word_id = w.id and w.language_name = l.name where swdl.donor_language_id = 873046508145964;"
Imbabura Quechua (Quichua)
Yaqui
Otomi
Tzotzil of Zinacantan
Mapudungun
Q'eqchi'
Kali'na
Wichí [Matacoan]
Berber
Dutch
English
Ceq Wong
Indonesian
Japanese
Romanian
Seychelles Creole
Hawaiian

real 4m34.178s
user 3m59.379s
sys 0m32.766s

with

robert@forkel02:~/projects/lwt/trunk/lwt
14:10:43$ time sqlite3 devdata.sqlite "select distinct l.name from source_word_donor_language as swdl, source_word as sw, word_source_word as wsw, word as w, language as l where swdl.source_word_id = sw.id and sw.id = wsw.source_word_id and wsw.word_id = w.id and w.language_name = l.name and swdl.donor_language_id = 873046508145964;"
Imbabura Quechua (Quichua)
Yaqui
Otomi
Tzotzil of Zinacantan
Mapudungun
Q'eqchi'
Kali'na
Wichí [Matacoan]
Berber
Dutch
English
Ceq Wong
Indonesian
Japanese
Romanian
Seychelles Creole
Hawaiian

real 0m12.060s
user 0m12.017s
sys 0m0.048s

Wikipedia says the above SQL statements are equivalent. But that doesn’t mean an SQL engine will treat them equivalently.

Advertisements

Leave a Comment »

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: