Um, well, except that I'd have huge gaping holes. i.e. say there are 100 shows at or after the specified time all on different channels. The above query would limit to the first 8 showings on or after that time *before* sorting by channel number.
Jeremy Hinegardner wrote: > > How about trying: > > SELECT s.startTime > FROM ( SELECT showing.startTime AS startTime > ,showing.stationId AS stationId > FROM showing > WHERE showing.startTime >= 1239230000 > ORDER BY showing.startTime > LIMIT 8 ) AS s > JOIN channel AS c > ON s.stationId = c.stationId > ORDER BY s.startTime, c.ChannelMajorNumber > > I believe this should limit your table of 100,000 rows down to just the 8 > you > want on the inner query, and then join that against the 100 rows in the > channel > table. > > Although, to tell you the truth, I don't see the purpose in your original > query: > > SELECT showing.startTime > FROM showing > JOIN channel > ON showing.startTime >= 1239230000 > AND showing.stationId = channel.stationId > ORDER BY showing.startTime, channel.ChannelMajorNumber > LIMIT 8; > > You are not doing anything with the channel table data other than joining > it > agains the showing tables. To me that means you are joining against the > channel > table in order to filter out rows in the showing table that have > stationId's > that do not exist in the channel table. > > Is that correct? If that is correct, then the query I gave will not do > what you > want. > > Can the goal you are attempting be stated as? > > Find next or earliest showings from the showings table for channels in > the > channel table? > > If this is the case, maybe this query will work: > > SELECT showing.startTime > FROM showing > WHERE showing.startTime >= 123923000 > AND showing.stationId IN ( SELECT DISTINCT channel.stationId FROM > channel ) > LIMIT 8; > > enjoy, > > -jeremy > > > On Tue, Apr 21, 2009 at 08:58:56PM -0700, sorka wrote: >> >> 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 > > -- > ======================================================================== > Jeremy Hinegardner jer...@hinegardner.org > > _______________________________________________ > 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....-tp23109024p23169800.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
