AnsweredAssumed Answered

Global Search, delay SQS execution?

Question asked by Francesca Shiekh on Oct 30, 2017
Latest reply on Nov 2, 2017 by Francesca Shiekh

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

Outcomes