try this select showing.startTime, from ( SELECT showing.startTime, channel.ChannelMajorNumber FROM showing JOIN channel on showing.stationId = channel.stationId Where showing.startTime >= 1239230000) ORDER BY showing.startTime, channel.ChannelMajorNumber LIMIT 8; --- On Tue, 4/21/09, sorka <sorka95...@gmail.com> wrote:
From: sorka <sorka95...@gmail.com> Subject: Re: [sqlite] Multi column ORDER BY across table peformance problem.... To: sqlite-users@sqlite.org Date: Tuesday, April 21, 2009, 9:46 PM It's in my original post above. sorka wrote: > > This should be simple but apparently it isn't. > > I have two tables: > "CREATE TABLE showing ( " > " showingId INTEGER PRIMARY KEY, " > " stationId INTEGER, " > " startTime INTEGER, " > ") " > CREATE INDEX showing_startTime on showing(startTime); > > > AND > "CREATE TABLE channel ( " > " channelId INTEGER PRIMARY KEY, " > " ChannelNumber INTEGER, " > " stationId INTEGER, " > " ) " > CREATE INDEX channel_ChannelNumber on channel(ChannelNumber); > CREATE INDEX channel_stationId on channel(stationId); > > When I do this select: > SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >= > 1239230000 AND showing.stationId = channel.stationId ORDER BY > showing.startTime LIMIT 8; > > I get back the correct 8 results in about 3 milliseconds. > > If I throw a secondary order term in there say ChannelNumber: > > SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >= > 1239230000 AND showing.stationId = channel.stationId ORDER BY > showing.startTime, channel.ChannelMajorNumber LIMIT 8; > > It now takes over 120 seconds!!!!!!! > > I've tried various multi-column indices including one on > channel(stationId, ChannelNumber). > > No difference. > > As far as I can tell, when ordering on columns that cross tables, sqlite > will bring in all the records that match the equality or inequality search > term on the first column before it does a secondary sort. > > I have over 100,000 records in the showing table and about 100 records in > the channel table. > > Sqlite should be smart enough to do the secondary sort on ChannelNumber as > soon as it sees that the records coming back have a later startTime than > the previous one. i.e. Say the first 5 records have the same startTime and > different channel numbers. Then the next 5 records have a later start time > than the first. Sqlite should be smart enough to see this as the results > come back and do a secondary sort on ChannelNumber on the first 5 results > and then rinse and repeat. > > What appears to be happening is that even though startTime is indexed, > sqlite is bringing all 100K records into memory sorted by time before it > starts to sort by channel number. > > Is there a way to get sqlite to do the right thing? If there was only a > way to have a multi-column index that included columns from different > tables. Oh wait, there is, it's called an intermediate table. However the > cost of doing this is pretty high for reasons I can't go into here. > > Any ideas? Maybe I'm just doing something wrong and this should be simple. > > Thanks. > > > > -- View this message in context: http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem....-tp23109024p23168281.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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
