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