I can't think of any reason why this would result in a faster query. But I went ahead and tried it on the off chance that maybe specifying the channel.ChannelMajorNumber inside an inner select might somehow trick sqlite into doing the right thing. Unfortunately, the result was the same. A few ms without the channel number and over 2 minutes with it. Again, sqlite is bringing in all results, not just the limit before it sorts on the channel number.
I tried your query but had to alias the inner columns so they could be selected in the outer query. SELECT startTime FROM (SELECT showing.startTime as startTime, channel.ChannelMajorNumber as ChannelMajorNumber FROM showing JOIN channel ON showing.startTime >= 1240362000 AND showing.stationId = channel.stationId ) ORDER BY startTime, ChannelMajorNumber LIMIT 8; 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....-tp23109024p23168567.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
