killbill-memoizeit

diff --git a/account/src/main/java/org/killbill/billing/account/dao/AccountSqlDao.java b/account/src/main/java/org/killbill/billing/account/dao/AccountSqlDao.java
index d474747..2eccd3c 100644
--- a/account/src/main/java/org/killbill/billing/account/dao/AccountSqlDao.java
+++ b/account/src/main/java/org/killbill/billing/account/dao/AccountSqlDao.java
@@ -60,4 +60,9 @@ public interface AccountSqlDao extends EntitySqlDao<AccountModelDao, Account> {
     @SqlQuery
     List<AccountModelDao> getAccountsByParentId(@Bind("parentAccountId") UUID parentAccountId,
                                                 @BindBean final InternalTenantContext context);
+
+    @SqlQuery
+    public AccountModelDao luckySearch(@Bind("searchKey") final String searchKey,
+                                       @BindBean final InternalTenantContext context);
+
 }

diff --git a/account/src/main/java/org/killbill/billing/account/dao/DefaultAccountDao.java b/account/src/main/java/org/killbill/billing/account/dao/DefaultAccountDao.java
index f1186a2..b959cb7 100644
--- a/account/src/main/java/org/killbill/billing/account/dao/DefaultAccountDao.java
+++ b/account/src/main/java/org/killbill/billing/account/dao/DefaultAccountDao.java
@@ -38,6 +38,7 @@ import org.killbill.billing.util.audit.ChangeType;
 import org.killbill.billing.util.cache.CacheControllerDispatcher;
 import org.killbill.billing.util.callcontext.InternalCallContextFactory;
 import org.killbill.billing.util.dao.NonEntityDao;
+import org.killbill.billing.util.entity.DefaultPagination;
 import org.killbill.billing.util.entity.Pagination;
 import org.killbill.billing.util.entity.dao.DefaultPaginationSqlDaoHelper.PaginationIteratorBuilder;
 import org.killbill.billing.util.entity.dao.EntityDaoBase;
@@ -51,6 +52,7 @@ import org.skife.jdbi.v2.IDBI;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 
+import com.google.common.collect.ImmutableList;
 import com.google.inject.Inject;
 
 public class DefaultAccountDao extends EntityDaoBase<AccountModelDao, Account, AccountApiException> implements AccountDao {
@@ -110,6 +112,25 @@ public class DefaultAccountDao extends EntityDaoBase<AccountModelDao, Account, A
 
     @Override
     public Pagination<AccountModelDao> searchAccounts(final String searchKey, final Long offset, final Long limit, final InternalTenantContext context) {
+        final boolean userIsFeelingLucky = limit == 1 && offset == -1;
+        if (userIsFeelingLucky) {
+            // The use-case we can optimize is when the user is looking for an exact match (e.g. he knows the full email). In that case, we can speed up the queries
+            // by doing exact searches only.
+            final AccountModelDao accountModelDao = transactionalSqlDao.execute(new EntitySqlDaoTransactionWrapper<AccountModelDao>() {
+                @Override
+                public AccountModelDao inTransaction(final EntitySqlDaoWrapperFactory entitySqlDaoWrapperFactory) throws Exception {
+                    return entitySqlDaoWrapperFactory.become(AccountSqlDao.class).luckySearch(searchKey, context);
+                }
+            });
+            return new DefaultPagination<AccountModelDao>(0L,
+                                                          1L,
+                                                          accountModelDao == null ? 0L : 1L,
+                                                          null, // We don't compute stats for speed in that case
+                                                          accountModelDao == null ? ImmutableList.<AccountModelDao>of().iterator() : ImmutableList.<AccountModelDao>of(accountModelDao).iterator());
+        }
+
+        // Otherwise, we pretty much need to do a full table scan (leading % in the like clause).
+        // Note: forcing MySQL to search indexes (like luckySearch above) doesn't always seem to help on large tables, especially with large offsets
         return paginationHelper.getPagination(AccountSqlDao.class,
                                               new PaginationIteratorBuilder<AccountModelDao, Account, AccountSqlDao>() {
                                                   @Override

diff --git a/account/src/main/resources/org/killbill/billing/account/dao/AccountSqlDao.sql.stg b/account/src/main/resources/org/killbill/billing/account/dao/AccountSqlDao.sql.stg
index d48a16b..f9ddfa5 100644
--- a/account/src/main/resources/org/killbill/billing/account/dao/AccountSqlDao.sql.stg
+++ b/account/src/main/resources/org/killbill/billing/account/dao/AccountSqlDao.sql.stg
@@ -106,6 +106,64 @@ searchQuery(prefix) ::= <<
   or <prefix>company_name like :likeSearchKey
 >>
 
+/** Unfortunately, we need to force MySQL to use the individual indexes */
+luckySearch() ::= <<
+select
+<allTableFields("t.")>
+from <tableName()> t
+join (
+  select distinct <recordIdField()>
+  from (
+    (
+      select <recordIdField()>
+      from <tableName()>
+      where <idField()> = :searchKey
+      <andCheckSoftDeletionWithComma()>
+      <AND_CHECK_TENANT()>
+      limit 1
+    )
+    union all
+    (
+      select <recordIdField()>
+      from <tableName()>
+      where name = :searchKey
+      <andCheckSoftDeletionWithComma()>
+      <AND_CHECK_TENANT()>
+      limit 1
+    )
+    union all
+    (
+      select <recordIdField()>
+      from <tableName()>
+      where email = :searchKey
+      <andCheckSoftDeletionWithComma()>
+      <AND_CHECK_TENANT()>
+      limit 1
+    )
+    union all
+    (
+      select <recordIdField()>
+      from <tableName()>
+      where external_key = :searchKey
+      <andCheckSoftDeletionWithComma()>
+      <AND_CHECK_TENANT()>
+      limit 1
+    )
+    union all
+    (
+      select <recordIdField()>
+      from <tableName()>
+      where company_name = :searchKey
+      <andCheckSoftDeletionWithComma()>
+      <AND_CHECK_TENANT()>
+      limit 1
+    )
+  ) search_with_idx
+) optimization on <recordIdField("optimization.")> = <recordIdField("t.")>
+limit 1
+;
+>>
+
 getIdFromKey() ::= <<
     SELECT id
     FROM accounts
@@ -119,4 +177,4 @@ getAccountsByParentId() ::= <<
     <AND_CHECK_TENANT()>
     <defaultOrderBy()>
     ;
->>
\ No newline at end of file
+>>

diff --git a/account/src/main/resources/org/killbill/billing/account/ddl.sql b/account/src/main/resources/org/killbill/billing/account/ddl.sql
index a24f272..195eb5b 100644
--- a/account/src/main/resources/org/killbill/billing/account/ddl.sql
+++ b/account/src/main/resources/org/killbill/billing/account/ddl.sql
@@ -37,6 +37,9 @@ CREATE UNIQUE INDEX accounts_id ON accounts(id);
 CREATE UNIQUE INDEX accounts_external_key ON accounts(external_key, tenant_record_id);
 CREATE INDEX accounts_parents ON accounts(parent_account_id);
 CREATE INDEX accounts_tenant_record_id ON accounts(tenant_record_id);
+CREATE INDEX accounts_email_tenant_record_id ON accounts(email, tenant_record_id);
+CREATE INDEX accounts_company_name_tenant_record_id ON accounts(company_name, tenant_record_id);
+CREATE INDEX accounts_name_tenant_record_id ON accounts(name, tenant_record_id);
 
 DROP TABLE IF EXISTS account_history;
 CREATE TABLE account_history (

diff --git a/account/src/main/resources/org/killbill/billing/account/migration/V20170123221645__add_lucky_search_indexes.sql b/account/src/main/resources/org/killbill/billing/account/migration/V20170123221645__add_lucky_search_indexes.sql
new file mode 100644
index 0000000..54cd629
--- /dev/null
+++ b/account/src/main/resources/org/killbill/billing/account/migration/V20170123221645__add_lucky_search_indexes.sql
@@ -0,0 +1,3 @@
+alter table accounts add index accounts_email_tenant_record_id(email, tenant_record_id);
+alter table accounts add index accounts_company_name_tenant_record_id(company_name, tenant_record_id);
+alter table accounts add index accounts_name_tenant_record_id(name, tenant_record_id);

diff --git a/account/src/test/java/org/killbill/billing/account/AccountTestUtils.java b/account/src/test/java/org/killbill/billing/account/AccountTestUtils.java
index 7934e12..8b53264 100644
--- a/account/src/test/java/org/killbill/billing/account/AccountTestUtils.java
+++ b/account/src/test/java/org/killbill/billing/account/AccountTestUtils.java
@@ -20,12 +20,12 @@ import java.util.Locale;
 import java.util.UUID;
 
 import org.joda.time.DateTimeZone;
-import org.testng.Assert;
-
 import org.killbill.billing.account.api.AccountData;
 import org.killbill.billing.account.api.DefaultMutableAccountData;
+import org.killbill.billing.account.api.MutableAccountData;
 import org.killbill.billing.account.dao.AccountModelDao;
 import org.killbill.billing.catalog.api.Currency;
+import org.testng.Assert;
 
 public abstract class AccountTestUtils {
 
@@ -81,11 +81,11 @@ public abstract class AccountTestUtils {
         return new AccountModelDao(UUID.randomUUID(), accountData);
     }
 
-    public static AccountData createAccountData() {
+    public static MutableAccountData createAccountData() {
         return createAccountData(30, 31, UUID.randomUUID().toString().substring(0, 4));
     }
 
-    private static AccountData createAccountData(final int billCycleDayUTC, final int billCycleDayLocal, final String phone) {
+    private static MutableAccountData createAccountData(final int billCycleDayUTC, final int billCycleDayLocal, final String phone) {
         final String externalKey = UUID.randomUUID().toString();
         final String email = UUID.randomUUID().toString().substring(0, 4) + '@' + UUID.randomUUID().toString().substring(0, 4);
         final String name = UUID.randomUUID().toString();

diff --git a/account/src/test/java/org/killbill/billing/account/api/user/TestDefaultAccountUserApi.java b/account/src/test/java/org/killbill/billing/account/api/user/TestDefaultAccountUserApi.java
index 338c58c..6149547 100644
--- a/account/src/test/java/org/killbill/billing/account/api/user/TestDefaultAccountUserApi.java
+++ b/account/src/test/java/org/killbill/billing/account/api/user/TestDefaultAccountUserApi.java
@@ -34,18 +34,76 @@ import org.killbill.billing.account.api.MutableAccountData;
 import org.killbill.billing.account.dao.AccountModelDao;
 import org.killbill.billing.catalog.api.Currency;
 import org.killbill.billing.events.AccountCreationInternalEvent;
+import org.killbill.billing.util.entity.Pagination;
 import org.testng.Assert;
 import org.testng.annotations.Test;
 
+import com.google.common.collect.ImmutableList;
 import com.google.common.eventbus.Subscribe;
 
 import static com.jayway.awaitility.Awaitility.await;
 import static java.util.concurrent.TimeUnit.SECONDS;
+import static org.killbill.billing.account.AccountTestUtils.createAccountData;
 import static org.killbill.billing.account.AccountTestUtils.createTestAccount;
 import static org.testng.Assert.assertEquals;
 
 public class TestDefaultAccountUserApi extends AccountTestSuiteWithEmbeddedDB {
 
+    @Test(groups = "slow", description = "Test Account search")
+    public void testSearch() throws Exception {
+        final MutableAccountData mutableAccountData1 = createAccountData();
+        mutableAccountData1.setEmail("john@acme.com");
+        mutableAccountData1.setCompanyName("Acme, Inc.");
+        final AccountModelDao account1ModelDao = new AccountModelDao(UUID.randomUUID(), mutableAccountData1);
+        final AccountData accountData1 = new DefaultAccount(account1ModelDao);
+        accountUserApi.createAccount(accountData1, callContext);
+
+        final MutableAccountData mutableAccountData2 = createAccountData();
+        mutableAccountData2.setEmail("bob@gmail.com");
+        mutableAccountData2.setCompanyName("Acme, Inc.");
+        final AccountModelDao account2ModelDao = new AccountModelDao(UUID.randomUUID(), mutableAccountData2);
+        final AccountData accountData2 = new DefaultAccount(account2ModelDao);
+        accountUserApi.createAccount(accountData2, callContext);
+
+        final Pagination<Account> search1 = accountUserApi.searchAccounts("Inc.", 0L, 5L, callContext);
+        Assert.assertEquals(search1.getCurrentOffset(), (Long) 0L);
+        Assert.assertNull(search1.getNextOffset());
+        Assert.assertEquals(search1.getMaxNbRecords(), (Long) 2L);
+        Assert.assertEquals(search1.getTotalNbRecords(), (Long) 2L);
+        Assert.assertEquals(ImmutableList.<Account>copyOf(search1.iterator()).size(), 2);
+
+        final Pagination<Account> search2 = accountUserApi.searchAccounts("Inc.", 0L, 1L, callContext);
+        Assert.assertEquals(search2.getCurrentOffset(), (Long) 0L);
+        Assert.assertEquals(search2.getNextOffset(), (Long) 1L);
+        Assert.assertEquals(search2.getMaxNbRecords(), (Long) 2L);
+        Assert.assertEquals(search2.getTotalNbRecords(), (Long) 2L);
+        Assert.assertEquals(ImmutableList.<Account>copyOf(search2.iterator()).size(), 1);
+
+        final Pagination<Account> search3 = accountUserApi.searchAccounts("acme.com", 0L, 5L, callContext);
+        Assert.assertEquals(search3.getCurrentOffset(), (Long) 0L);
+        Assert.assertNull(search3.getNextOffset());
+        Assert.assertEquals(search3.getMaxNbRecords(), (Long) 2L);
+        Assert.assertEquals(search3.getTotalNbRecords(), (Long) 1L);
+        Assert.assertEquals(ImmutableList.<Account>copyOf(search3.iterator()).size(), 1);
+
+        // Exact search will fail
+        final Pagination<Account> search4 = accountUserApi.searchAccounts("acme.com", -1L, 1L, callContext);
+        Assert.assertEquals(search4.getCurrentOffset(), (Long) 0L);
+        Assert.assertNull(search4.getNextOffset());
+        // Not computed
+        Assert.assertNull(search4.getMaxNbRecords());
+        Assert.assertEquals(search4.getTotalNbRecords(), (Long) 0L);
+        Assert.assertEquals(ImmutableList.<Account>copyOf(search4.iterator()).size(), 0);
+
+        final Pagination<Account> search5 = accountUserApi.searchAccounts("john@acme.com", -1L, 1L, callContext);
+        Assert.assertEquals(search5.getCurrentOffset(), (Long) 0L);
+        Assert.assertNull(search5.getNextOffset());
+        // Not computed
+        Assert.assertNull(search5.getMaxNbRecords());
+        Assert.assertEquals(search5.getTotalNbRecords(), (Long) 1L);
+        Assert.assertEquals(ImmutableList.<Account>copyOf(search5.iterator()).size(), 1);
+    }
+
     @Test(groups = "slow", description = "Test Account creation generates an event")
     public void testBusEvents() throws Exception {
         final AccountEventHandler eventHandler = new AccountEventHandler();