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

Groups > comp.databases > #130

Re: optimizing a query

Show all headers | 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