indexed filtering on multiple bean properties

I have a web grid showing 250000 products.
the user can filter on of product properties
for example:
price > 30 AND
discount < 20 AND
type.contains("mx")

No problemo for sql. (even for type.contains('mx') -> oracle can create indexes on calculated values).
how about space4j?
Can I implement an index for this in space4j.
Would I have to create a separate index for each combination of bean properties?
Any help welcome.
Thanks,
Stefaan

You can use two indexes (one for price and one for discount) or one composite index.

Check the code below that can be found here: http://s4j.mentaframework.org/posts/list/5.page

Now for the type.contains("mx"), there is no index that can index this, not even in a relational database. So you have to loop and check for each record you return.

// If index does not exist, let's create it...
        if (!im.checkIndex(INDX_AGE)) {
            
            /*
             * Index name = INDX_AGE
             * Map in the Space that will be indexed = MAP_NAME
             * Type of index = MULTI = non-unique sorted index
             * Object that will be indexed = User.class
             * Atributes of the object that will be indexed = "age"
             * 
             * We want to create this index because we will be searching
             * for Users by age. The index will be SORTED because we want
             * to search for users in a age range. (age > min and age < max)
             */
         
            Index indx = new Index(INDX_AGE, MAP_NAME, Index.TYPE.MULTI_SORTED,
                    User.class, "age");
         
            boolean ok = im.createIndex(indx, space4j);
            
            if (ok) {
               
                System.out.println(indx + " created!");
               
            } else {
               
                System.out.println(indx + " could not be created!");
            }
        }

indexByLastName = im.getIndex(INDX_LASTNAME);

@Override
    public Collection<Object> findUsers(int minAge, int maxAge) {
       
       // Remember that our index is of the type MULTI_SORTED.
       // That's why we are doing this cast here.
       
       MultiSortedMap map = (MultiSortedMap) indexByAge.getMap();
       
       // A multi-sorted-map will store a Map for each key.
       // That's why our index is NON-UNIQUE meaning the same
       // key can be associated with many entries in the map.
       // The map is also sorted by its key, meaning we can
       // perform range searches efficiently.
       
       ConcurrentNavigableMap<Key, Object> subMap = map.subMap(new Key(minAge), true, new Key(maxAge), true);
       
       Collection<Object> users = new ArrayList<Object>(subMap.size() * 3);
       
       Iterator<Object> iter = subMap.values().iterator();
       
       while(iter.hasNext()) {
          
          // our collection here is actually a ConcurrentHashMap!
          
          Map<Object, Boolean> m = (Map<Object, Boolean>) iter.next();
          
          // this is not good as we are transversing the whole list twice!
          // TODO: Create custom iterator for MultiSortedMap to avoid this?
          
          Iterator<Object> iter2 = m.keySet().iterator();
          
          while(iter2.hasNext()) {
             
             users.add(iter2.next());
          }
       }
       
       return users;
    }

Hi thanks for the quit reply:

Now for the type.contains("mx"), there is no index that can index this,

Actually, oracle 10g can index calculated values, eg the x like '%bla%' or any other function used in a query.

Having said that, is it really necessary to iterate all values. Let's say we have an ordered index on the type property.
if a is an array of products ordered by type, and a[j] is the first value for which type.contains("mx") and a[k] is the last value for which type.contains("mx"), dont' then all
values between i and k also have type.contains("mx")?

Hummm. If the substring lookup is fixed then perhaps you can index it. But Space4J does not support this.

Something nice to be implemented by someone. Want to give it a try?

Or if the subset returned by price and discount is relatively small you can just iterate.

Thanks again,
Uhh. What I said before was actually untrue:
mxaaaaa
bbbbbbbb
zzzzzzzmx

1,3 contain mx, the middle one does not.

Want to give it a try?

maybe

Or if the subset returned by price and discount is relatively small you can just iterate.

users might only filter on type, so this would not work.