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

Groups > comp.databases > #128 > unrolled thread

optimizing a query

Back to article view | Back to comp.databases

Contents

  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

#128 — optimizing a query

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

CREATE TABLE IF NOT EXISTS `slot_purchases` (
  `user_id` int(11) NOT NULL,
  `position_price` int(11) NOT NULL,
  `status` int(11) default NULL,
  `transaction_id` varchar(45) NOT NULL,
  `transaction_details` text NOT NULL,
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`user_id`,`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Because this query will be executed from Perl/DBI the $amount will be
turned into a placeholder by SQL::Interp before sending to the -
>prepare() call of DBI.

[toc] | [next] | [standalone]

#129

In general, the perfect index on a table matches exactly the fields
referenced in the query.

In this particular case, you definitely want an index on the fields of
your criteria ('position_price' and 'status') - these should be
combined in a single index. It might also help to have an index on
'ts' to help speed up the ordering - but this would be a separate
index.

Christopher "CJ" Smith
President
BeWise Consulting Group

On Oct 6, 1:23 pm, Terrence Brannon <thequietcen...@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))
>
> CREATE TABLE IF NOT EXISTS `slot_purchases` (
>   `user_id` int(11) NOT NULL,
>   `position_price` int(11) NOT NULL,
>   `status` int(11) default NULL,
>   `transaction_id` varchar(45) NOT NULL,
>   `transaction_details` text NOT NULL,
>   `ts` timestamp NOT NULL default CURRENT_TIMESTAMP,
>   PRIMARY KEY  (`user_id`,`transaction_id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
> Because this query will be executed from Perl/DBI the $amount will be
> turned into a placeholder by SQL::Interp before sending to the -
>
>
>
>
>
>
>
> >prepare() call of DBI.

[toc] | [prev] | [next] | [standalone]

#130

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

[toc] | [prev] | [next] | [standalone]

#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

[toc] | [prev] | [next] | [standalone]

#132

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

[toc] | [prev] | [next] | [standalone]

#135

[This followup was posted to comp.databases and a copy was sent to the 
cited author.]

In article <097a5e8b-6bb5-47e5-aef0-39895a6111f4
@k34g2000yqm.googlegroups.com>, thequietcenter@gmail.com says...
> 
> 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))

You can remember which is which if you remember what the abbreviations 
stand for:  DDL is "Data Definition Language", which means Defining the 
organization (fields, tables, etc) of the data.  DML is "Data 
Manipulation Language", where you manipulate (INSERT, DELETE, SELECT) 
the data.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases

csiph-web