For this particular query, the only reason for the join on the channel table
is simply to order by channel number and that's it.
Currently, what I'm doing is leaving off the order by channel number. Since
this is done through the C++ interface, I'm stepping through results until a
showing with a
Ah, i see, so you are going to be calling this many times, and slowly
incrementing the value of startTime. Yes, in this query, if you have more than
8 shows at a particular start time, then when you increment the time to go to
the next 'batch' of shows you will miss all the ones from then end of
ime
LIMIT 8 ) AS s
on S.StationId = C.stationId
ORDER BY s.startTime, c.ChannelMajorNumber
limit 8
--- On Wed, 4/22/09, sorka <sorka95...@gmail.com> wrote:
From: sorka <sorka95...@gmail.com>
Subject: Re: [sqlite] Multi column ORDER BY across table peformance problem
Um, well, except that I'd have huge gaping holes. i.e. say there are 100
shows at or after the specified time all on different channels. The above
query would limit to the first 8 showings on or after that time *before*
sorting by channel number.
Jeremy Hinegardner wrote:
>
> How about
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,
How about trying:
SELECT s.startTime
FROM ( SELECT showing.startTime AS startTime
,showing.stationId AS stationId
FROM showing
WHERE showing.startTime >= 123923
ORDER BY showing.startTime
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
> SELECT showing.startTime FROM showing JOIN channel ON
> showing.startTime >=
> 123923 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
That's completely untrue. The only reason for the difference in the name is
because I changed it recently. MajoreChannelNumber is ChannelNumber, the
name just changed.
I've included the full schema and all the indices and indicated about how
many records are in each table.
The schema example is
sorry. Ive had good results with subqueries in resolving similar performance
issues.
--- 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@sql
On 22/04/2009 10:48 AM, sorka wrote:
> Wow. Anybody? I figured this would be a simple question for the gurus on this
> board. Seriously, nobody knows a better way to do this?
Seriously, consider alternative possibilities
>
>
> sorka wrote:
>> This should be simple but apparently it isn't.
>>
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
T 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:
&
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 ( "
> "showingIdINTEGER PRIMARY KEY, "
> "stationId INTEGER, "
>
send me your original query please.
thanks
Woody
--- 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, 2
No. This can't be broken down into a query within a query. Perhaps maybe if
you spell out an example of what you're thinking?
Thanks.
sorka wrote:
>
> This should be simple but apparently it isn't.
>
> I have two tables:
> "CREATE TABLE showing ( "
> "
did you try the subquery method i suggested?
Woody
--- 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,
Wow. Anybody? I figured this would be a simple question for the gurus on this
board. Seriously, nobody knows a better way to do this?
sorka wrote:
>
> This should be simple but apparently it isn't.
>
> I have two tables:
> "CREATE TABLE showing ( "
> "
just curious; have you tried doing the select in a subquery, and then the order
by in the outer query?
woody
--- On Fri, 4/17/09, sorka <sorka95...@gmail.com> wrote:
From: sorka <sorka95...@gmail.com>
Subject: [sqlite] Multi column ORDER BY across table peformance problem....
To:
This should be simple but apparently it isn't.
I have two tables:
"CREATE TABLE showing ( "
"showingIdINTEGER PRIMARY KEY, "
"stationId INTEGER, "
"startTime INTEGER, "
20 matches
Mail list logo
