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

Groups > comp.databases > #132

Re: optimizing a query

Show all headers | View raw

On 2011-10-08, Luuk <Luuk@invalid.lan> wrote:

>> 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?

Postgresql. MS SQL Server 2008 apparently supports something similar too.
 
if you haven't got partial indexes to play with could try.

 CREATE INDEX some_index_name_a ON slot_purchases(status,position_price,ts);

or possibly 
 
 CREATE INDEX some_index_name_b ON slot_purchases(status IS NULL,position_price,ts);

if you're only expecting a few results omitting the ts column from the
index may give better performance

if a significant proportion have status IS NULL the index may not
actually pay for its upkeep.

-- 
⚂⚃ 100% natural

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