Writing the query as you said you would returns results in nearly instantly, within a few ms.
The problem is when you add a secondary ordering field. The intended indices are being used. The problem, as I've explained several times already is that there is no way to create a multicolumn index across tables. Sqlite is using the stationId index on the channel table to join showings via the stationId. The query plan shows it clearly. When ordering by startTime or even startTime and stationId, the results are returned in a few ms. But when adding channel number(i.e startTime and then channel number), it jumps to two minutes. This is because sqlite is bringing in nearly 100K records from the showing table, sorted by time, because of the startTime index, before it starts sorting by channel number. It would sure be nice to be able to get sqlite to sort on the channel number for each time group. i.e. once all the 10:00 PM showings have been returned and the first 10:30 PM showing is about to be returned, sort all of the 10:00 PM showings by channel number. Sqlite is NOT doing this but should. At this point, I'm going to assume that this is just a limitation in the sqlite engine and I'll have to proceed in other ways to solve this issue. Ian Walters wrote: > >> SELECT showing.startTime FROM showing JOIN channel ON >> showing.startTime >= >> 1239230000 AND showing.stationId = channel.stationId ORDER BY >> showing.startTime LIMIT 8; > > I don't know if it would be faster... but I would have written that. > > SELECT showing.startTime FROM showing JOIN channel USING (stationId) > WHERE startTime < 1239230000 ORDER BY startTime LIMIT 8. > > Also I know in the latest version of SQLite its possible to 'hint' > what indexes should be used, which might be helpful. There is also > something on the contrib page that lets you check a query to see what > indexes it does use. > > Sorry if the above lacks detail, its kinda a busy day. > > -- > Ian > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem....-tp23109024p23169251.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
