| org.mmbase.storage.search.FunctionValueConstraint
All known Subclasses: org.mmbase.storage.search.implementation.BasicFunctionValueConstraint,
| FunctionValueConstraint | | public interface FunctionValueConstraint extends FieldCompareConstraint(Code) | | This class can solve the following.
= PROBLEM ==
The following query will be fired upon the database when somebody
tries to login:
8000ms:
SELECT otype,owner,number,firstname,account,lastname,email,description,password
FROM vpro4_users users WHERE lowerEmail(email)='' AND lowerEmail(password)=''
The lower-function is slowing down the login-procedure, because the lower-function
will force a sequential-scan.
So an functional index should be used to query the table, but informix can't put an index
on a table with a function which is not variant;
-= SOLUTION =-
Use a wrapper to facilitate the variant version of lower and use this to query the database.
Squirrel-the-database-client seems to have a problem with these kinds of queries; use the
utility classes in cinema-importers -> importer -> CreateProcedure
- create an notvariant function of lower:
javac CreateProcedure.java && java -cp /usr/local/SQuirreL\ SQL\ Client/lib/ifxjdbc.jar:. CreateProcedure
CREATE FUNCTION lowerNotVariant(field VARCHAR(255))
RETURNING VARCHAR(255) WITH (NOT VARIANT);
RETURN LOWER(field);
END FUNCTION;
- set an index on the field to be queried:
CREATE INDEX vpro4_users_email_lower on vpro4_users(lowerNotVaraint(email));
- now query the table with full-speed:
33ms: SELECT otype,owner,number,firstname,account,lastname,email,description,password
FROM vpro4_users users WHERE lowerNotVariant(email)='' AND lowerNotVariant(password)=''
README.txt (END)
author:
Marcel Maatkamp
version:
$Id: FunctionValueConstraint.java,v 1.5 2007/12/06 08:13:36 michiel Exp $
since:
MMBase-1.8.5 |
| getValue | | Object getValue()(Code) | | Gets the value to compare with.
Depending on the field type, the value is of type
String or Number.
If the associated field type is of string type, when used in
combination with the operator LIKE, this may contain the
following wildcard characters as well:
- % for any string
- _ for a single character
|
| toString | | public String toString()(Code) | | Returns a string representation of this FunctionValueConstraint.
The string representation has the form
"FunctionValueConstraint(inverse:<:inverse>, field:<field>,
casesensitive:<casesensitive>, operator:<operator>,
value:<value>)"
where
- <inverse>is the value returned by
FunctionValueConstraint.isInverse isInverse()
- <field> is the field alias returned by
FieldConstraint#getField().getAlias(), or
FieldConstraint#getField().getFieldName()
when the former is null.
- <casesensitive> is the value returned by
FieldConstraint.isCaseSensitive isCaseSensitive()
- <operator> is the value returned by
(@link FieldCompareConstraint#getOperator getOperator()}
- <value> is the value returned by
FunctionValueConstraint.getValue getValue()
A string representation of this FunctionValueConstraint. |
|
|