Table AAA

ID (PrimaryKey)
Progres (Integer)
DateAndTime (datetime)
xxx (Integer)

to execute this select on a big table
SELECT ID,Progres WHERE Progres=243 AND Date(DateAndTime) > '11-02-2008'

For performance ..
Is better create an index on Progres ?
Is better create an index on Progres and DateTime ?
Is it equal ?

The creation of an index on a large table can ask for time, how I make
understand if ended?

Thaks' a lot
--

Paolo Holzl <software (AT) toglispamtek-up (DOT) com> wrote:
That depends on the distribution of the data.

If there are only three rows in the table where progres=243, the best index
would be:
CREATE INDEX a_index ON aaa(progres);

If half the rows in the table have progres=243, but only few rows
have date(dateandtime) > date('11-02-2008'), the following index would help:
CREATE INDEX b_index ON aaa(date(dateandtime));

If, however, half of your table rows have progres=243 and all rows have
date(dateandtime) > date('11-02-2008'), no concievable index can speed
up your query and the best thing for the planner to do is a sequential
table scan.

You see, the correct index to speed up a query depends
a) on the data in the table and
b) on the constants in the query

CREATE INDEX is synchronous, that is, the command will "hang" until it
is done. Once the command returns, the index is created.

Yours,
Laurenz Albe

Paolo Holzl <software (AT) toglispamtek-up (DOT) com> wrote:
That depends on the distribution of the data.

If there are only three rows in the table where progres=243, the best index
would be:
CREATE INDEX a_index ON aaa(progres);

If half the rows in the table have progres=243, but only few rows
have date(dateandtime) > date('11-02-2008'), the following index would help:
CREATE INDEX b_index ON aaa(date(dateandtime));

If, however, half of your table rows have progres=243 and all rows have
date(dateandtime) > date('11-02-2008'), no concievable index can speed
up your query and the best thing for the planner to do is a sequential
table scan.

You see, the correct index to speed up a query depends
a) on the data in the table and
b) on the constants in the query

CREATE INDEX is synchronous, that is, the command will "hang" until it
is done. Once the command returns, the index is created.

Yours,
Laurenz Albe

Paolo Holzl <software (AT) toglispamtek-up (DOT) com> wrote:
That depends on the distribution of the data.

If there are only three rows in the table where progres=243, the best index
would be:
CREATE INDEX a_index ON aaa(progres);

If half the rows in the table have progres=243, but only few rows
have date(dateandtime) > date('11-02-2008'), the following index would help:
CREATE INDEX b_index ON aaa(date(dateandtime));

If, however, half of your table rows have progres=243 and all rows have
date(dateandtime) > date('11-02-2008'), no concievable index can speed
up your query and the best thing for the planner to do is a sequential
table scan.

You see, the correct index to speed up a query depends
a) on the data in the table and
b) on the constants in the query

CREATE INDEX is synchronous, that is, the command will "hang" until it
is done. Once the command returns, the index is created.

Yours,
Laurenz Albe

Paolo Holzl <software (AT) toglispamtek-up (DOT) com> wrote:
That depends on the distribution of the data.

If there are only three rows in the table where progres=243, the best index
would be:
CREATE INDEX a_index ON aaa(progres);

If half the rows in the table have progres=243, but only few rows
have date(dateandtime) > date('11-02-2008'), the following index would help:
CREATE INDEX b_index ON aaa(date(dateandtime));

If, however, half of your table rows have progres=243 and all rows have
date(dateandtime) > date('11-02-2008'), no concievable index can speed
up your query and the best thing for the planner to do is a sequential
table scan.

You see, the correct index to speed up a query depends
a) on the data in the table and
b) on the constants in the query

CREATE INDEX is synchronous, that is, the command will "hang" until it
is done. Once the command returns, the index is created.

Yours,
Laurenz Albe

Paolo Holzl <software (AT) toglispamtek-up (DOT) com> wrote:
That depends on the distribution of the data.

If there are only three rows in the table where progres=243, the best index
would be:
CREATE INDEX a_index ON aaa(progres);

If half the rows in the table have progres=243, but only few rows
have date(dateandtime) > date('11-02-2008'), the following index would help:
CREATE INDEX b_index ON aaa(date(dateandtime));

If, however, half of your table rows have progres=243 and all rows have
date(dateandtime) > date('11-02-2008'), no concievable index can speed
up your query and the best thing for the planner to do is a sequential
table scan.

You see, the correct index to speed up a query depends
a) on the data in the table and
b) on the constants in the query

CREATE INDEX is synchronous, that is, the command will "hang" until it
is done. Once the command returns, the index is created.

Yours,
Laurenz Albe

Paolo Holzl <software (AT) toglispamtek-up (DOT) com> wrote:
That depends on the distribution of the data.

If there are only three rows in the table where progres=243, the best index
would be:
CREATE INDEX a_index ON aaa(progres);

If half the rows in the table have progres=243, but only few rows
have date(dateandtime) > date('11-02-2008'), the following index would help:
CREATE INDEX b_index ON aaa(date(dateandtime));

If, however, half of your table rows have progres=243 and all rows have
date(dateandtime) > date('11-02-2008'), no concievable index can speed
up your query and the best thing for the planner to do is a sequential
table scan.

You see, the correct index to speed up a query depends
a) on the data in the table and
b) on the constants in the query

CREATE INDEX is synchronous, that is, the command will "hang" until it
is done. Once the command returns, the index is created.

Yours,
Laurenz Albe

Paolo Holzl <software (AT) toglispamtek-up (DOT) com> wrote:
That depends on the distribution of the data.

If there are only three rows in the table where progres=243, the best index
would be:
CREATE INDEX a_index ON aaa(progres);

If half the rows in the table have progres=243, but only few rows
have date(dateandtime) > date('11-02-2008'), the following index would help:
CREATE INDEX b_index ON aaa(date(dateandtime));

If, however, half of your table rows have progres=243 and all rows have
date(dateandtime) > date('11-02-2008'), no concievable index can speed
up your query and the best thing for the planner to do is a sequential
table scan.

You see, the correct index to speed up a query depends
a) on the data in the table and
b) on the constants in the query

CREATE INDEX is synchronous, that is, the command will "hang" until it
is done. Once the command returns, the index is created.

Yours,
Laurenz Albe

Paolo Holzl <software (AT) toglispamtek-up (DOT) com> wrote:
That depends on the distribution of the data.

If there are only three rows in the table where progres=243, the best index
would be:
CREATE INDEX a_index ON aaa(progres);

If half the rows in the table have progres=243, but only few rows
have date(dateandtime) > date('11-02-2008'), the following index would help:
CREATE INDEX b_index ON aaa(date(dateandtime));

If, however, half of your table rows have progres=243 and all rows have
date(dateandtime) > date('11-02-2008'), no concievable index can speed
up your query and the best thing for the planner to do is a sequential
table scan.

You see, the correct index to speed up a query depends
a) on the data in the table and
b) on the constants in the query

CREATE INDEX is synchronous, that is, the command will "hang" until it
is done. Once the command returns, the index is created.

Yours,
Laurenz Albe