Global Search, delay SQS execution?

When starting a global search the queries start immediately, causing some pretty large UNIONs to be generated.

For example, as soon as the user typed the letter c in the search window the following executed:

- (SELECT users.id,
        users.first_name,
        users.last_name,
        eabr.primary_address,
        ea.email_address,
        'Users' module
   FROM users
   JOIN email_addr_bean_rel eabr
       ON (users.id = eabr.bean_id
           AND eabr.deleted=0)
   JOIN email_addresses ea
       ON (eabr.email_address_id = ea.id)
   WHERE (users.deleted = 0
           AND eabr.primary_address = 1)
           AND (first_name LIKE 'c%')
           AND ea.invalid_email = 0
           AND ea.opt_out = 0)
UNION
   ::~(SELECT users.id,
        users.first_name,
        users.last_name,
        eabr.primary_address,
        ea.email_address,
        'Users' module
   FROM users
   JOIN email_addr_bean_rel eabr
       ON (users.id = eabr.bean_id
           AND eabr.deleted=0)
   JOIN email_addresses ea
       ON (eabr.email_address_id = ea.id)
   WHERE (users.deleted = 0
           AND eabr.primary_address = 1)
           AND (last_name LIKE 'c%')
           AND ea.invalid_email = 0
           AND ea.opt_out = 0)
UNION
   ::~(SELECT users.id,
        users.first_name,
        users.last_name,
        eabr.primary_address,
        ea.email_address,
        'Users' module
   FROM users
   JOIN email_addr_bean_rel eabr
       ON (users.id = eabr.bean_id
           AND eabr.deleted=0)
   JOIN email_addresses ea
       ON (eabr.email_address_id = ea.id)
   WHERE (users.deleted = 0
           AND eabr.primary_address = 1)
           AND (email_address LIKE 'c%')
           AND ea.invalid_email = 0
           AND ea.opt_out = 0)
UNION
   ::~(SELECT contacts.id,
        contacts.first_name,
        contacts.last_name,
        eabr.primary_address,
        ea.email_address,
        'Contacts' module
   FROM contacts
   JOIN email_addr_bean_rel eabr
       ON (contacts.id = eabr.bean_id
           AND eabr.deleted=0)
   JOIN email_addresses ea
       ON (eabr.email_address_id = ea.id)
   WHERE (contacts.deleted = 0
           AND eabr.primary_address = 1)
           AND (first_name LIKE 'c%')
           AND ea.invalid_email = 0
           AND ea.opt_out = 0)
UNION
   ::~(SELECT contacts.id,
        contacts.first_name,
        contacts.last_name,
        eabr.primary_address,
        ea.email_address,
        'Contacts' module
   FROM contacts
   JOIN email_addr_bean_rel eabr
       ON (contacts.id = eabr.bean_id
           AND eabr.deleted=0)
   JOIN email_addresses ea
       ON (eabr.email_address_id = ea.id)
   WHERE (contacts.deleted = 0
           AND eabr.primary_address = 1)
           AND (last_name LIKE 'c%')
           AND ea.invalid_email = 0
           AND ea.opt_out = 0)
UNION
   ::~(SELECT contacts.id,
        contacts.first_name,
        contacts.last_name,
        eabr.primary_address,
        ea.email_address,
        'Contacts' module
   FROM contacts
   JOIN email_addr_bean_rel eabr
       ON (contacts.id = eabr.bean_id
           AND eabr.deleted=0)
   JOIN email_addresses ea
       ON (eabr.email_address_id = ea.id)
   WHERE (contacts.deleted = 0
           AND eabr.primary_address = 1)
           AND (email_address LIKE 'c%')
           AND ea.invalid_email = 0
           AND ea.opt_out = 0)
UNION
   ::~(SELECT leads.id,
        leads.first_name,
        leads.last_name,
        eabr.primary_address,
        ea.email_address,
        'Leads' module
   FROM leads
   JOIN email_addr_bean_rel eabr
       ON (leads.id = eabr.bean_id
           AND eabr.deleted=0)
   JOIN email_addresses ea
       ON (eabr.email_address_id = ea.id)
   WHERE (leads.deleted = 0
           AND eabr.primary_address = 1)
           AND (first_name LIKE 'c%')
           AND ea.invalid_email = 0
           AND ea.opt_out = 0)
UNION
   ::~(SELECT leads.id,
        leads.first_name,
        leads.last_name,
        eabr.primary_address,
        ea.email_address,
        'Leads' module
   FROM leads
   JOIN email_addr_bean_rel eabr
       ON (leads.id = eabr.bean_id
           AND eabr.deleted=0)
   JOIN email_addresses ea
       ON (eabr.email_address_id = ea.id)
   WHERE (leads.deleted = 0
           AND eabr.primary_address = 1)
           AND (last_name LIKE 'c%')
           AND ea.invalid_email = 0
           AND ea.opt_out = 0)
UNION
   ::~(SELECT leads.id,
        leads.first_name,
        leads.last_name,
        eabr.primary_address,
        ea.email_address,
        'Leads' module
   FROM leads
   JOIN email_addr_bean_rel eabr
       ON (leads.id = eabr.bean_id
           AND eabr.deleted=0)
   JOIN email_addresses ea
       ON (eabr.email_address_id = ea.id)
   WHERE (leads.deleted = 0
           AND eabr.primary_address = 1)
           AND (email_address LIKE 'c%')
           AND ea.invalid_email = 0
           AND ea.opt_out = 0)
UNION
   ::~(SELECT prospects.id,
        prospects.first_name,
        prospects.last_name,
        eabr.primary_address,
        ea.email_address,
        'Prospects' module
   FROM prospects
   JOIN email_addr_bean_rel eabr
       ON (prospects.id = eabr.bean_id
           AND eabr.deleted=0)
   JOIN email_addresses ea
       ON (eabr.email_address_id = ea.id)
   WHERE (prospects.deleted = 0
           AND eabr.primary_address = 1)
           AND (first_name LIKE 'c%')
           AND ea.invalid_email = 0
           AND ea.opt_out = 0)
UNION
   ::~(SELECT prospects.id,
        prospects.first_name,
        prospects.last_name,
        eabr.primary_address,
        ea.email_address,
        'Prospects' module
   FROM prospects
   JOIN email_addr_bean_rel eabr
       ON (prospects.id = eabr.bean_id
           AND eabr.deleted=0)
   JOIN email_addresses ea
       ON (eabr.email_address_id = ea.id)
   WHERE (prospects.deleted = 0
           AND eabr.primary_address = 1)
           AND (last_name LIKE 'c%')
           AND ea.invalid_email = 0
           AND ea.opt_out = 0)
UNION
   ::~(SELECT prospects.id,
        prospects.first_name,
        prospects.last_name,
        eabr.primary_address,
        ea.email_address,
        'Prospects' module
   FROM prospects
   JOIN email_addr_bean_rel eabr
       ON (prospects.id = eabr.bean_id
           AND eabr.deleted=0)
   JOIN email_addresses ea
       ON (eabr.email_address_id = ea.id)
   WHERE (prospects.deleted = 0
           AND eabr.primary_address = 1)
           AND (email_address LIKE 'c%')
           AND ea.invalid_email = 0
           AND ea.opt_out = 0)
UNION
   ::~(SELECT accounts.id,
        '' first_name, accounts.name last_name, eabr.primary_address, ea.email_address, 'Accounts' module
   FROM accounts
   JOIN email_addr_bean_rel eabr
       ON (accounts.id = eabr.bean_id
           AND eabr.deleted=0)
   JOIN email_addresses ea
       ON (eabr.email_address_id = ea.id)
   WHERE (accounts.deleted = 0
           AND eabr.primary_address = 1)
           AND (email_address LIKE 'c%')
           AND ea.invalid_email = 0
           AND ea.opt_out = 0)
UNION
   ::~(SELECT accounts.id,
        '' first_name, accounts.name last_name, eabr.primary_address, ea.email_address, 'Accounts' module
   FROM accounts
   JOIN email_addr_bean_rel eabr
       ON (accounts.id = eabr.bean_id
           AND eabr.deleted=0)
   JOIN email_addresses ea
       ON (eabr.email_address_id = ea.id)
   WHERE (accounts.deleted = 0
           AND eabr.primary_address = 1)
           AND (name LIKE 'c%')
           AND ea.invalid_email = 0
           AND ea.opt_out = 0)
ORDER BY  id DESC LIMIT 0,11

It executes somewhat quickly but with an eye toward optimization and reducing database load, is there a way I can put, maybe, a 1second delay on the execution of the query so as to also reduce the number of queries?

Or even prevent the SQS altogether and require the user to enter to execute the search?

 

thanks,
FrancescaS

  • I was able to increase the minimum number of characters that trigger the SQS search from 1 to 3, I could increase further and require the user submit via enter key for shorter terms.

    custom/clients/base/views/quicksearch-bar/quicksearch-bar.js

    ({
      extendsFrom: "QuicksearchBarView",
      initialize: function(options){
        this._super('initialize', [options]);
        this.minChars = 3;
      },
      _dispose: function() {
        this._super('_dispose');
      },
    })

    I also submitted an "Idea" to make this parameter configurable via the Admin, vote up if you like the idea.

    Make global search SQS minChars configurable 

    FrancescaS