AW: Query optimization problem

Euke Castellano wrote:
> > I have the following table definition:
> >
> > CREATE TABLE "CONSUM"(
> >    "ID" Integer NOT NULL,
> >    "HOTEL" Integer NOT NULL,
> >    "SERVICE" Integer NOT NULL,
> >    "SEGMENT" Integer NOT NULL,
> >    "GUEST" Integer,
> >    "COMPANY" Fixed (38,0),
> >    "AGENCY" Fixed (38,0),
> >    "REPRESENTATIVE" Fixed (38,0),
> >    "INVOICEDATE" Date,
> >    "CHARGEDATE" Date,
> >    "QUANTITY" Fixed (12,2) DEFAULT 0.00,
> >    "AMOUNT" Fixed (12,2) DEFAULT 0.00,
> >    "ROOMNIGHTS" Fixed (12,2) DEFAULT 0.00,
> >    "RATE" Integer,
> >    "SEASON" Char (1) ASCII,
> >    "PROCESSDATE" Date,
> >    PRIMARY KEY ("ID")
> > )
> >
> > I also have an UNIQUE INDEX created on column INVOICEDATE.
> > The table has ~25.000.000 rows.
> >
> > When I try a query like:    SELECT * FROM consum WHERE invoiceDate 
> > between '2004-01-01' AND '2004-12-31'
> > it takes very few seconds to show the information using 
> SQLStudio, but 
> > if I try this other query:
> >    SELECT * FROM consum WHERE invoiceDate between '2004-01-01' AND 
> > '2004-12-31' AND rate=9
> > it take several hours!! to process the information.
> >
> > What can I do to optimize this kind of queries? Should I create an 
> > index for each column that I need to query? Or is it better 
> to create 
> > an index that includes all the columns of the query?
> >
> >
> > Thank you very much for your help and sorry for my english.
> >
> >
> >
> The index is not UNIQUE.
> Sorry and thanks.

Hi,

did you see the whole result in SQL Studio for the first select 
or is it possible that you only have a look on the first n rows?

SQL Studio only fetches those rows which are requested by the user
when he scrolls through the result set.

So I suppose that you only gets the first rows very fast because 
many or all rows are in the range you asked for and it would last 
much longer to scroll through the whole result.

At your second query you looks for rows that have rate=9 
and if only few rows fulfil this condition it last much longer
to find the first n rows.

You could speed up your query with a multiple index over invoiceDate 
and rate: "create index i2 on consum (invoiceDate,rate)"

Kind regards
Holger




--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=gcdm-maxdb@xxxxxxxxxxx