Thank you set :)
Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Dienstag, 3. März 2015 10:07 An: firebird-support@yahoogroups.com Betreff: RE: [firebird-support] Index >Hello everybody, > >I'm looking in a table for an existing record bevore I insert it. Now I would set some indices. There are 4 fields I compare. >It is better I create one index with all 4 fields or for every field one? Difficult question with several potential answers. Generally, one combined index is a bit faster. So, if this is the only query with any of these four fields in the WHERE or JOIN clause OR the fields have lousy selectivity by themselves OR this is the one query where performance is vital, then go for the combined index. However, there are a few benefits from using single field indexes. -It is easy to understand which fields are used for a particular PLAN (hence easier to optimize) -It is easy to see whether you should consider adding new indexes -Each index can be used in more scenarios (the first field of a multifield index can be used for all queries with this field, the following fields only if the first field is compared for equality by the query) Generally, I find it simpler to generally have single field indexes and only rarely use combined indexes myself. So, I'm sorry to say there's no general answer for your question and that you have to consider for yourself what is most important in your particular case. >now I have insert an index for the first both fields, the ibexpert analysis shows me non indexed reads, why? I don't know, sorry... >The stored procedure also looking for two other fields. >SELECT 1 FROM rdb$database WHERE EXISTS(SELECT * FROM t_errordata WHERE kd_id = :t_timestamp > and li_id = :i_li and ftyp = :i_ftyp and ((fnr >= :i_fnr) and (fnr <= :i_fnr + (:fanzds-1)))) INTO :idexists; >kd_id and li_id now indexed (in one index) ftyp and fnr not. The analysis said there are 1 indexed read, >bevore I inserted the index there are thousends non indexed. I assume kd_id or li_id are fairly selective. My guess is that without any index, Firebird had to scan through thousands of pages before it found one that matched your criteria, with the index, Firebird can go straight to the correct kd_id and li_id and then the first matches with respect to i_fnr. HTH, Set
