Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases > #130

Re: optimizing a query

Path csiph.com!x330-a1.tempe.blueboxinc.net!newsfeed.hal-mli.net!feeder3.hal-mli.net!news.glorb.com!news.netfront.net!reversiblemaps.ath.cx!not-for-mail
From Jasen Betts <jasen@xnet.co.nz>
Newsgroups comp.databases
Subject Re: optimizing a query
Date 8 Oct 2011 05:58:19 GMT
Organization Dis (not Dat) Organisation
Lines 35
Message-ID <j6oopr$pbl$1@reversiblemaps.ath.cx> (permalink)
References <097a5e8b-6bb5-47e5-aef0-39895a6111f4@k34g2000yqm.googlegroups.com>
NNTP-Posting-Host 118.90.51.106
Mime-Version 1.0
Content-Type text/plain; charset=UTF-8
Content-Transfer-Encoding 8bit
X-Trace adenine.netfront.net 1318053812 66930 118.90.51.106 (8 Oct 2011 06:03:32 GMT)
X-Complaints-To news@netfront.net
NNTP-Posting-Date Sat, 8 Oct 2011 06:03:32 +0000 (UTC)
X-Face ?)Aw4rXwN5u0~$nqKj`xPz>xHCwgi^q+^?Ri*+R(&uv2=E1Q0Zk(>h!~o2ID@6{uf8s;a +M[5[U[QT7xFN%^gR"=tuJw%TXXR'Fp~W;(T"1(739R%m0Yyyv*gkGoPA.$b,D.w:z+<'"=-lVT?6 {T?=R^:W5g|E2#EhjKCa+nt":4b}dU7GYB*HBxn&Td$@f%.kl^:7X8rQWd[NTc"P"u6nkisze/Q;8 "9Z{peQF,w)7UjV$c|RO/mQW/NMgWfr5*$-Z%u46"/00mx-,\R'fLPe.)^
User-Agent slrn/pre1.0.0-18 (Linux)
Xref x330-a1.tempe.blueboxinc.net comp.databases:130

Show key headers only | View raw

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;


-- 
⚂⚃ 100% natural

--- Posted via news://freenews.netfront.net/ - Complaints to news@netfront.net ---

Back to comp.databases | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

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