Hi,

can you give us a database statistics? I didn't see it anymore. Are the
commit times the same

Further the selectivity of the keys is interesting, without them the
optimizer can't find a good way.

I will give you a few ideas to try:
- It seems that most indexe are used either with (ID_geo_place,
ID_reader) or (ID_geo_place, ID_holder_agency). You can create just four
compound keys for those both ascending and descending. On the other
fields create keys for each single field in both directions. Try this
without giving a plan.
- is it necessary to have a Varchar(100) field for the geo_place or may
you can normalize it ? As you know, temporary space is needed for the
complete 100 byte for each row
- use SELECT FIRST x .... FROM ... ORDER BY ... to fetch just a couple
of rows.

Hope that helps,

Karsten

svanderclock schrieb:

>
> 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
> <mailto:firebird-support%40yahoogroups.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 <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
> <http://www.databasedevelopmentforum.com>
> >
>
>