At 16:25 18/02/2008, you wrote: >I am new to SQLite and databases, so I am stil learning how to >optimize their use... > >I am working on a "shopping cart" type of feature, it is actually a >favorites feature for a system that displays images in multiple >galleries. There is a SQLite table that contains the user_id, >gallery_id, and image_id. When a index page is displayed, only a sub >set of the images in the gallery are displayed. So the question is >what will be faster: > >1: Doing a SELECT for each image on the favorites table to see if it >is selected >2: Doing one SELECT to get all the images for the current gallery and >store that into a PHP array and then simply look in the PHP for each >image? > >My thought is option 2. Is that correct?
My thought is that it would depend. I'd guess that If you have 100 images, and you are wanting to show 20, then (2) may be quicker, but if you have 1,000,000 images, then (1) would be quicker. (Assuming you have a usable index on the table). Leaving aside possible database design considerations, it's generally best to let the database engine do the work if it can. With SQLite, I'd qualify that to say that it's best to let SQLite do the work if the queries are simple enough that its optimiser will use indices to do the work. We have found that it can be quicker to do things partially in SQLite and partially in C++. When SQLite would have to do a sequential scan to get the result, it can be quicker to do, say, two indexed scans in SQLite and then operate on the two result sets (eg doing a union or intersect) to produce the final result set, but this is the exception rather than the rule (for us anyway). Paul Smith _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
