FWD » Core Development » Database

• Index selection notes: #8357-119, #8357-121, #8357-130
• Testing examples: #8357-120, #8357-122, #8357-124, #8357-125.

The current worry is that the order of rules in the Index Selection phase might be wrong. I'll summarize in the following post some examples, and I will also check the order of rules in both 4GL and FWD to get some findings/results.

Here's a summary of what happened so far:

Example 1 ShowHide

After this step, I added to the test table one more index: idx2 which is an unique index on the f2 and f3 fields (both integers). This leads us to example 2.

Example 2 ShowHide

Example 3 ShowHide

Then, we were interested in what is taken into consideration when sorting records from a word-index perspective.

Example 4 ShowHide

The next step is to check the order from the Index Selection Rules in both 4GL and FWD.

Greg Shah wrote (note #8357-130):

Index Selection Rules shows that USE-INDEX (rule 1) and "unique index with all components in equality matches" (rule 2) should have preference over CONTAINS (rule 3).

• About Rule 1: In 4GL, a query cannot have both CONTAINS and USE-INDEX. If I write such query, I get the error USE-INDEX cannot be used with CONTAINS. (3316). This happens when I save the working file (i.e. at compile time).
• About Rule 2: If the WHERE clause has all the components (of a unique index) in equality matches AND a CONTAINS clause, both indexes are evaluated. The WHERE looks like word-index check AND unique index check.
  • The xref compile report shows both SEARCH fwd.test idx_word (word-index) and SEARCH fwd.test idx2 (unique index). In 4GL, what happens in this case? Are both indexes used or is the last index the chosen one?
  • Important note: The order of these SEARCH statements for the AND case is always the same, despite rearranging the operands in the WHERE clause. The word-index SEARCH always appears before the unique index SEARCH in the xref report.
• About Rule 2 part 2: If I replace the AND in the WHERE clause with an OR, so that the WHERE looks like word-index check OR unique index check, still both indexes are evaluated.
  • However, the order of SEARCH in the xref report depends on the order of operands in the WHERE clause.
    • If the where is word-index check OR unique index check, xref looks like SEARCH fwd.test idx_word (word-index) and SEARCH fwd.test idx2 (unique index).
    • If the where is unique index check OR word-index check, xref looks like SEARCH fwd.test idx2 (unique index) and SEARCH fwd.test idx_word (word-index).
• Other notes about Rule 2:
  • If I go from "all components in equality matches" to "all components except one in equality matches", and that other component having "range matches", then 4GL chooses the word-index, while FWD chooses the unique index.
• About Rule 3: In a query with a WHERE clause that has a CONTAINS and nothing else (no BY clauses, no nothing), 4GL chooses the word-index, while FWD chooses the primary index.
  • So even in this simple case FWD is unable to select the right index (the word-index).

All in all, I think the order of rules in the wiki is right. Maybe, and this is just a presumption, FWD is unable to select the word-index at all (i.e. the rules point to other indexes and never to the word-index)?

Good findings. Please edit the wiki page to include your important clarifications.

Hi all, we have completed the "indeterminate sorting" review and are now starting to make the necessary changes, hence I thought I'd check out what the latest is on the CONTAINS topic. I've read the previous ticket and this one and please correct me if I'm wrong, but my conclusion from it is that FWD will not use the same WORD-INDEX rules as the 4GL so we will have to add a BY clause to get the sequence we had previously?
Also, I'm clear that FWD will pick the same records as the 4GL but if it's not going to use the WORD-INDEX then is there going to be a performance issue with queries using CONTAINS in FWD? Just in 4GL terms if you're using the PRIMARY index on a table to do a 'CONTAINS' type function in a query then it will be much slower?
Thanks, Andy.

Eric Faulhaber wrote:

Andy Edwards wrote:

[...] FWD will not use the same WORD-INDEX rules as the 4GL so we will have to add a BY clause to get the sequence we had previously?

Until this ticket is resolved, yes. However, I'm not exactly sure what that needs to look like, since specifying BY <word-indexed field> is not going to have the same meaning as sorting by the words matching the CONTAINS expression. Ovidiu, Radu, any ideas for a temporary workaround here?

I do not think how a by clause would sort according to matched word in contains, as seen in #9194-3, 4th example. Sorting on a field or one element of array is possible, though.

Also, I'm clear that FWD will pick the same records as the 4GL but if it's not going to use the WORD-INDEX then is there going to be a performance issue with queries using CONTAINS in FWD? Just in 4GL terms if you're using the PRIMARY index on a table to do a 'CONTAINS' type function in a query then it will be much slower?

I don't think this should cause a performance issue. Each word-indexed field causes a separate, secondary "word table" to be created in the schema, which contains the results of parsing the words in the word-indexed column of the primary table. This secondary table is indexed by word, so this should allow the database to create as efficient a query plan as possible, given the complexity of the original query in the 4GL code.

Currently, we don't structure the ORDER BY portion of the converted query to sort by the words in that secondary table. But I think this is a matter of sorting, rather than performance. Fixing this is the focus of this ticket.

Related to Rule 3. The way CONTAINS is implemented in FWD will cause the word index to be always used. At least in the meaning FWD has for this index. It is used for looking up the word, not for sorting.
So, when talking of performance, I think FWD does the best effort: it splits the 'haystack' into words as they are flushed and index them. At the moment of the query the information is already half processed and only needs to be 'collected' and assembled. But, as this task states, the problem is the order in which the assembly is performed.

OK, thank you for your response and the one previous about the projects (I hadn't been made aware of this).

I have created 9194a for further changes. First, I'll check why the word-index is not selected. After that, I'll see if the generated order by clause needs to be changed accordingly. Maybe the correct selection of the index fixes the second part, we'll see.

Radu Apetrii wrote:

I think I'll run the same test with Hotel next.

Well, nothing changed in Hotel with word-index selection enabled. I will check a large customer application next.