On 1/25/2013 9:45 AM, moti lahiani wrote:
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
At which point do you plan to apply your filters? In option 1, if you
first select top ten file IDs and *then* apply filters to them, then you
would likely end up with fewer than ten files. But if you apply filters
at the same time you choose your ten IDs, then you need to read
associated data anyway, and your option 1 becomes equivalent to option 2.
I'd say, just do
select * from files left join music_files using file_id
where YourFilterGoesHere
order by date_uploaded limit 10;
An index on files(date_uploaded) would likely prove beneficial.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
