Re: Query optimization problem

Euke Castellano wrote:

> Hi all,
>
> 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.


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