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....-tp23109024p23109024.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
