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