Skip to main content
added 66 characters in body
Source Link
Doc Brown
  • 220.6k
  • 35
  • 410
  • 625

When you expect queries like firstname = X and lastname = Y, and the infrastructure you have at hand allows only single key lookups, probably the most simple and efficient solution is to build a compound index by yourself. This can be achieved easily by simply concatenating the strings firstname and lastname by some separator character in the middle which is ideally not part of the allowed character set for these two attributes, or at least very unlikely to appear there.

For example, let' s assume ";" is a forbidden character, then you can index by the keys firstname + ";" + lastname, and a lookup for firstname='X' AND lastname='Y' will just be a lookup for the key 'X;Y'.

Of course, when no characters are forbidden for these attributes, you may have to encode the separator character in the data first before creating the lookup key, or you may have to deal with the fact that a lookup for 'X;;Y' can return results with firstname='X;' and lastname='Y' as well as firstname='X' and lastname=';Y' (and sort out the unwanted items afterwards).

Note that doing two lookups for items with firstname='X', then all items with lastname='Y', and finally build their intersection might be fast enough in certain real world cases. Still it is not nearly as efficient as creating a combined index (especially when you have to deal with a large number of records). You may read my answer here to understand what to expect when dealing with hundreds of millions of records and indexes which cover only one attribute out of two.

When you expect queries like firstname = X and lastname = Y, and the infrastructure you have at hand allows only single key lookups, probably the most simple and efficient solution is to build a compound index by yourself. This can be achieved easily by simply concatenating the strings firstname and lastname by some separator character in the middle which is ideally not part of the allowed character set for these two attributes, or at least very unlikely to appear there.

For example, let' s assume ";" is a forbidden character, then you can index by the keys firstname + ";" + lastname, and a lookup for firstname='X' AND lastname='Y' will just be a lookup for the key 'X;Y'.

Of course, when no characters are forbidden for these attributes, you may have to encode the separator character in the data first before creating the lookup key, or you may have to deal with the fact that a lookup for 'X;;Y' can return results with firstname='X;' and lastname='Y' as well as firstname='X' and lastname=';Y' (and sort out the unwanted items afterwards).

Note that doing two lookups for items with firstname='X', then all items with lastname='Y', and finally build their intersection might be fast enough in certain real world cases. Still it is not nearly as efficient as creating a combined index. You may read my answer here to understand what to expect when dealing with hundreds of millions of records and indexes which cover only one attribute out of two.

When you expect queries like firstname = X and lastname = Y, and the infrastructure you have at hand allows only single key lookups, probably the most simple and efficient solution is to build a compound index by yourself. This can be achieved easily by simply concatenating the strings firstname and lastname by some separator character in the middle which is ideally not part of the allowed character set for these two attributes, or at least very unlikely to appear there.

For example, let' s assume ";" is a forbidden character, then you can index by the keys firstname + ";" + lastname, and a lookup for firstname='X' AND lastname='Y' will just be a lookup for the key 'X;Y'.

Of course, when no characters are forbidden for these attributes, you may have to encode the separator character in the data first before creating the lookup key, or you may have to deal with the fact that a lookup for 'X;;Y' can return results with firstname='X;' and lastname='Y' as well as firstname='X' and lastname=';Y' (and sort out the unwanted items afterwards).

Note that doing two lookups for items with firstname='X', then all items with lastname='Y', and finally build their intersection might be fast enough in certain real world cases. Still it is not nearly as efficient as creating a combined index (especially when you have to deal with a large number of records). You may read my answer here to understand what to expect when dealing with hundreds of millions of records and indexes which cover only one attribute out of two.

added 461 characters in body
Source Link
Doc Brown
  • 220.6k
  • 35
  • 410
  • 625

When you expect queries like firstname = X and lastname = Y, and the infrastructure you have at hand allows only single key lookups, probably the most simple and efficient solution is to build a compound index by yourself. This can be achieved easily by simply concatenating the strings firstname and lastname by some separator character in the middle which is ideally not part of the allowed character set for these two attributes, or at least very unlikely to appear there.

For example, let' s assume ";" is a forbidden character, then you can index by the keys firstname + ";" + lastname, and a lookup for firstname='X' AND lastname='Y' will just be a lookup for the key 'X;Y'.

Of course, when no characters are forbidden for these attributes, you may have to encode the separator character in the data first before creating the lookup key, or you may have to deal with the fact that a lookup for 'X;;Y' can return results with firstname='X;' and lastname='Y' as well as firstname='X' and lastname=';Y' (and sort out the unwanted items afterwards).

Note that doing two lookups for items with firstname='X', then all items with lastname='Y', and finally build their intersection might be fast enough in certain real world cases. Still it is not nearly as efficient as creating a combined index. You may read my answer here to understand what to expect when dealing with hundreds of millions of records and indexes which cover only one attribute out of two.

When you expect queries like firstname = X and lastname = Y, and the infrastructure you have at hand allows only single key lookups, probably the most simple and efficient solution is to build a compound index by yourself. This can be achieved easily by simply concatenating the strings firstname and lastname by some separator character in the middle which is ideally not part of the allowed character set for these two attributes, or at least very unlikely to appear there.

For example, let' s assume ";" is a forbidden character, then you can index by the keys firstname + ";" + lastname, and a lookup for firstname='X' AND lastname='Y' will just be a lookup for the key 'X;Y'.

Of course, when no characters are forbidden for these attributes, you may have to encode the separator character in the data first before creating the lookup key, or you may have to deal with the fact that a lookup for 'X;;Y' can return results with firstname='X;' and lastname='Y' as well as firstname='X' and lastname=';Y' (and sort out the unwanted items afterwards).

When you expect queries like firstname = X and lastname = Y, and the infrastructure you have at hand allows only single key lookups, probably the most simple and efficient solution is to build a compound index by yourself. This can be achieved easily by simply concatenating the strings firstname and lastname by some separator character in the middle which is ideally not part of the allowed character set for these two attributes, or at least very unlikely to appear there.

For example, let' s assume ";" is a forbidden character, then you can index by the keys firstname + ";" + lastname, and a lookup for firstname='X' AND lastname='Y' will just be a lookup for the key 'X;Y'.

Of course, when no characters are forbidden for these attributes, you may have to encode the separator character in the data first before creating the lookup key, or you may have to deal with the fact that a lookup for 'X;;Y' can return results with firstname='X;' and lastname='Y' as well as firstname='X' and lastname=';Y' (and sort out the unwanted items afterwards).

Note that doing two lookups for items with firstname='X', then all items with lastname='Y', and finally build their intersection might be fast enough in certain real world cases. Still it is not nearly as efficient as creating a combined index. You may read my answer here to understand what to expect when dealing with hundreds of millions of records and indexes which cover only one attribute out of two.

Source Link
Doc Brown
  • 220.6k
  • 35
  • 410
  • 625

When you expect queries like firstname = X and lastname = Y, and the infrastructure you have at hand allows only single key lookups, probably the most simple and efficient solution is to build a compound index by yourself. This can be achieved easily by simply concatenating the strings firstname and lastname by some separator character in the middle which is ideally not part of the allowed character set for these two attributes, or at least very unlikely to appear there.

For example, let' s assume ";" is a forbidden character, then you can index by the keys firstname + ";" + lastname, and a lookup for firstname='X' AND lastname='Y' will just be a lookup for the key 'X;Y'.

Of course, when no characters are forbidden for these attributes, you may have to encode the separator character in the data first before creating the lookup key, or you may have to deal with the fact that a lookup for 'X;;Y' can return results with firstname='X;' and lastname='Y' as well as firstname='X' and lastname=';Y' (and sort out the unwanted items afterwards).