Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!gegeweb.org!de-l.enfer-du-nord.net!feeder2.enfer-du-nord.net!feeder.news-service.com!newsfeed.xs4all.nl!newsfeed5.news.xs4all.nl!xs4all!post.news.xs4all.nl!news.xs4all.nl!not-for-mail
Date: Sat, 08 Oct 2011 10:49:40 +0200
From: Luuk <Luuk@invalid.lan>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
Newsgroups: comp.databases
Subject: Re: optimizing a query
References: <097a5e8b-6bb5-47e5-aef0-39895a6111f4@k34g2000yqm.googlegroups.com> <j6oopr$pbl$1@reversiblemaps.ath.cx>
In-Reply-To: <j6oopr$pbl$1@reversiblemaps.ath.cx>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Message-ID: <0ef5m8-3se.ln1@luuk.invalid.lan>
Lines: 40
NNTP-Posting-Host: 80.101.116.74
X-Trace: 1318063801 news2.news.xs4all.nl 2505 80.101.116.74:47880
Xref: x330-a1.tempe.blueboxinc.net comp.databases:131

On 08-10-2011 07:58, Jasen Betts wrote:
> On 2011-10-06, Terrence Brannon<thequietcenter@gmail.com>  wrote:
>> I'm running a site with very specific queries. That is to say, there
>> are not lots of different queries on any particular table, so I'm not
>> out for general optimization, but want indexing specifically for this
>> query:
>>
>>   SELECT
>>        *
>>      FROM
>>        slot_purchases
>>      WHERE
>>        position_price = $amount
>>        AND status IS NULL
>>      ORDER BY
>>        ts DESC
>>
>> given this DDL (or DML, I never know the diff between those terms
>> (grin))
>
> DDL is CREATE, DROP etc,  DML is INSERT, UPDATE, DELETE (etc)
> IMO the generic term is SQL :)
>
>
> I'd go for a partial index (where status IS NULL) on position_price and ts
>
> something like this:
>
> CREATE INDEX some_index_name ON slot_purchases(position_price,ts) WHERE
> status IS NULL;
>
>

Error: near "where": syntax error
sqlite>

Which what database did you do that?

-- 
Luuk