Re: optimizing a query

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> (permalink)
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

Show key headers only | View raw

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

Thread

optimizing a query Terrence Brannon <thequietcenter@gmail.com> - 2011-10-06 10:23 -0700
  Re: optimizing a query CJ <cjsmith411@gmail.com> - 2011-10-07 09:22 -0700
  Re: optimizing a query Jasen Betts <jasen@xnet.co.nz> - 2011-10-08 05:58 +0000
    Re: optimizing a query Luuk <Luuk@invalid.lan> - 2011-10-08 10:49 +0200
      Re: optimizing a query Jasen Betts <jasen@xnet.co.nz> - 2011-10-09 04:00 +0000
  Re: optimizing a query David Kerber <dkerber@WarrenRogersAssociates.invalid> - 2011-10-17 09:00 -0400

csiph-web