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
> > > [email protected]
> > > 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
> > [email protected]
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> ========================================================================
> Jeremy Hinegardner [email protected]
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
========================================================================
Jeremy Hinegardner [email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
