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
