mercredi 21 janvier 2015

Optmizing table comparisons (joins) query in sqlite 3

I have two tables audio and folder with following schema


AUDIO



-------------------------------
ID | NAME | FILESIZE | FID
-------------------------------


FOLDER



-------------------------
ID | NAME
-------------------------


Audio table holds the audio files scanned from device and folder table holds the folder of the file. Now I want content change detection,to determine if there is any new song adde/deleted from the device, in subsequent scan of the device. So I have two replica tables newaudio and newfolder.


So for comparison I am running the following query



select case
when (select count(audio.id) from audio,newaudio where audio.filesize=newaudio.filesize and audio.name=newaudio.name and (select name from folder where id=audio.pid)=(select name from newfolder where id=newaudio.pid))=(select count(*) from newaudio)
then 1
else 0
end as matchResult


Is there any better opmtimized query to do the same thing??


EDIT


Audio and folder tables hold entries from the last scan. Now when I delete or add songs and again sync the device, entries go in newaudio and newfolder tables.


So comparison is between new and old entries.


For next scan, newaudio and newfolder replace audio and folder and new* tables are created again, and so on


Aucun commentaire:

Enregistrer un commentaire