The real answer is try both and see which is faster. My guess is #1 is probably faster since I don't think there's an easy way to limit the left-hand side of a left-join operation to do #2 without touching all the music_file records, is there? The join would have to match on file_id so would hit the entire music_files against . I'm sure somebody will correct me if I'm wrong here...I'm not confident that the join isn't smarter than that.
Is the join done BEFORE the order by and limit? Or after? I would think it's done before. Or will the order by and limit apply to the left-hand side first before the join when it can? -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of moti lahiani Sent: Friday, January 25, 2013 8:45 AM To: sqlite-users@sqlite.org Subject: [sqlite] faster query - help Hi I have a data base with the following tables 1) files - each file in the system have his information in this table. that table include 12 different columns like file_id as integer primary key autoincrement, date_uploaded as integer, file_type as integer and so.. 2) music_files - hold more information on file in case the file is music file. columns like file_id as integer, artist, genre, album_name and more I need to query those 2 tables (with both all information) according to some filters/rule that I have, order the results according to date_uploaded and return the top(10) as final result my question is what is faster and have the best performance: (the table files can have more than 1500000 rows) option 1: select the file_id and date_uploaded, order by date_uploaded and get the top(10) file_id and than select all information from both(files and music_files) tables according to those top(10) results option 2: select all information from both(files+music files) tables, order by date_uploaded and return the top(10) as results Thanks _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
