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.

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... (AT) gmail (DOT) com> wrote:

On 2011-10-06, Terrence Brannon <thequietcenter (AT) gmail (DOT) com> wrote:
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 (AT) netfront (DOT) net ---

On 08-10-2011 07:58, Jasen Betts wrote:
Error: near "where": syntax error
sqlite>

Which what database did you do that?

--
Luuk

On 2011-10-08, Luuk <Luuk (AT) invalid (DOT) lan> wrote:

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

[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 (AT) gmail (DOT) com says...
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.