That last query should be: SELECT showing.startTime FROM showing WHERE showing.startTime >= 123923000 AND showing.stationId IN ( SELECT DISTINCT channel.stationId FROM channel ) ORDER BY showing.startTime LIMIT 8;
I forgot the order by. enjoy, -jeremy On Tue, Apr 21, 2009 at 11:07:15PM -0600, 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 -- ======================================================================== Jeremy Hinegardner jer...@hinegardner.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users