dear matin,

below the table :

CREATE TABLE TRANSAC_SEARCH_RES_FS_A_2
(
ID_geo_place VARCHAR(100),
ID_holder_agency VARCHAR(15) NOT NULL,
ID_reader VARCHAR(15),
Property_category SMALLINT NOT NULL,
Advertisement_weight INTEGER NOT NULL,
Creation_date TIMESTAMP NOT NULL,
Total_sale_price NUMERIC(15,5),
Surface NUMERIC(12,4),
Nb_room SMALLINT,
Nb_bedroom SMALLINT,
ID_property VARCHAR(15) NOT NULL,
ID_transac VARCHAR(15) NOT NULL
);

commit;


/** single column **/
CREATE ASCENDING INDEX TRANSAC_SEARCH_103_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_transac);
CREATE ASCENDING INDEX TRANSAC_SEARCH_99_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(Total_sale_price);
CREATE ASCENDING INDEX TRANSAC_SEARCH_100_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(Surface);
CREATE ASCENDING INDEX TRANSAC_SEARCH_101_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(NB_room);
CREATE ASCENDING INDEX TRANSAC_SEARCH_102_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(NB_bedroom);

/** ID_geo_place, ID_reader, Transac_kind **/
CREATE DESCENDING INDEX TRANSAC_SEARCH_104_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_reader, Advertisement_weight);
CREATE DESCENDING INDEX TRANSAC_SEARCH_105_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_reader, Creation_date);
CREATE DESCENDING INDEX TRANSAC_SEARCH_106_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_reader, Total_sale_price);
CREATE ASCENDING INDEX TRANSAC_SEARCH_107_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_reader, Total_sale_price);
CREATE DESCENDING INDEX TRANSAC_SEARCH_108_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_reader, Surface);
CREATE DESCENDING INDEX TRANSAC_SEARCH_109_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_reader, NB_room);
CREATE DESCENDING INDEX TRANSAC_SEARCH_110_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_reader, NB_bedroom);

/** ID_geo_place, ID_reader, Transac_kind, Property_category **/
CREATE DESCENDING INDEX TRANSAC_SEARCH_111_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_reader, Property_category, Advertisement_weight);
CREATE DESCENDING INDEX TRANSAC_SEARCH_112_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_reader, Property_category, Creation_date);
CREATE DESCENDING INDEX TRANSAC_SEARCH_113_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_reader, Property_category, Total_sale_price);
CREATE ASCENDING INDEX TRANSAC_SEARCH_114_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_reader, Property_category, Total_sale_price);
CREATE DESCENDING INDEX TRANSAC_SEARCH_115_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_reader, Property_category, Surface);
CREATE DESCENDING INDEX TRANSAC_SEARCH_116_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_reader, Property_category, NB_room);
CREATE DESCENDING INDEX TRANSAC_SEARCH_117_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_reader, Property_category, NB_bedroom);

/** ID_geo_place, ID_holder_agency, ID_reader, Transac_kind **/
CREATE DESCENDING INDEX TRANSAC_SEARCH_1071_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_holder_agency, ID_reader, Advertisement_weight);
CREATE DESCENDING INDEX TRANSAC_SEARCH_1072_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_holder_agency, ID_reader, Creation_date);
CREATE DESCENDING INDEX TRANSAC_SEARCH_1073_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_holder_agency, ID_reader, Total_sale_price);
CREATE ASCENDING INDEX TRANSAC_SEARCH_1074_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_holder_agency, ID_reader, Total_sale_price);
CREATE DESCENDING INDEX TRANSAC_SEARCH_1075_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_holder_agency, ID_reader, Surface);
CREATE DESCENDING INDEX TRANSAC_SEARCH_1076_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_holder_agency, ID_reader, NB_room);
CREATE DESCENDING INDEX TRANSAC_SEARCH_1077_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_holder_agency, ID_reader, NB_bedroom);

/** ID_geo_place, ID_holder_agency, ID_reader, Transac_kind, Property_category
**/
CREATE DESCENDING INDEX TRANSAC_SEARCH_1080_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_holder_agency, ID_reader, Property_category,
Advertisement_weight);
CREATE DESCENDING INDEX TRANSAC_SEARCH_1081_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_holder_agency, ID_reader, Property_category, Creation_date);
CREATE DESCENDING INDEX TRANSAC_SEARCH_1082_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_holder_agency, ID_reader, Property_category,
Total_sale_price);
CREATE ASCENDING INDEX TRANSAC_SEARCH_1083_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_holder_agency, ID_reader, Property_category,
Total_sale_price);
CREATE DESCENDING INDEX TRANSAC_SEARCH_1084_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_holder_agency, ID_reader, Property_category, Surface);
CREATE DESCENDING INDEX TRANSAC_SEARCH_1085_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_holder_agency, ID_reader, Property_category, NB_room);
CREATE DESCENDING INDEX TRANSAC_SEARCH_1086_IDX ON TRANSAC_SEARCH_RES_FS_A_2
(ID_geo_place, ID_holder_agency, ID_reader, Property_category, NB_bedroom);

commit;




with all these index, i can handle all the query like

select ... where
ID_geo_place=xxx
and ID_reader = yyy
and Property_category = www
order by
Creation_date desc

here for exemple i will simply specify in the query plan to use the

INDEX TRANSAC_SEARCH_112_IDX (ID_geo_place, ID_reader, Property_category, Creation_date);

and replace the "order by" by :
ID_geo_place desc,
ID_reader desc,
Property_category desc,
Creation_date desc

doesn't matter if (for exemple) ID_geo_place=xxx will return millions of row or just one, the result will be alway < 100 ms. with only my few set of index i can handle most of all the situation

thanks by advance
stephane

--- In firebird-support@yahoogroups.com, "Martijn Tonies" <m.tonies@...> wrote:
>
> Hi,
>
> > HUGELY exaggerated ?? you thing that the firebird engine is soo smart
> > enought to sort in < 100 ms thousand of thousand of records ??
> >
> > ok, i will gave you this simple exemple.
>
> What indices do you have on the below table?
>
> > we have a database of house, and field NB_room, surface, price, etc..
> >
> > table House
> > ID_owner,
> > ID_agent,
> > creation_date,
> > id_place,
> > NB_room,
> > surface,
> > price
> >
> > the result can be ordered by
> >
> > creation_date
> > id_place
> > nb_room (asc or desc)
> > surface (asc or desc)
> > price (asc or desc)
> > etc.
> >
> > the table have more than 10 000 000 records (actually many more)
> >
> > now we need to retrieve any kind of subset in any of the previous
> > available order by, and this in lower than 100 ms !
> >
> > how do you want to do that without index in all possible order by
> > combination ? it's will never work if you not do this and you will always
> > have a case where the subset will be to big to be sort faster in memory
> > (and the result can take more than 30 seconds to appear)
> >
> > for exemple, what plan you will use for
> > where Agent = xxx and nb_room > 2 and nb_room < 3 and id_location=yyy
> > order by price desc
> >
> > and now for
> >
> > where Agent = xxx and nb_room > 30 and id_location=yyy order by price desc
> >
> >
> > the only way i found is to use a plan that use the index on the Order by +
> > max as possible on filter
> >
> > for exemple below it's will be
> >
> > where Agent = xxx and nb_room > 30 and id_location=yyy order by agent
> > desc, id_location_desc, price desc
> > plan order index (IDagent_IDlocation_Price, nb_room)
> >
> > and don't thing it's can be in any other way, because it's simply cannot,
> > except if you can know before to do the query the number of items that the
> > query will return to you (to know if you need to use the index on the
> > filter or on the order by)
> >
>
> With regards,
>
> Martijn Tonies
> Upscene Productions
> http://www.upscene.com
>
> Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
> Anywhere, MySQL, InterBase, NexusDB and Firebird!
>
> Database questions? Check the forum:
> http://www.databasedevelopmentforum.com
>