Make multitasking more efficient?
I have a select query that returns the following entry in alphabetical order with respect to a given ID:
SELECT *
FROM contacts
WHERE client_id = 22844
AND deleted_at IS NULL
AND
(
(
last_name = (
SELECT last_name
FROM contacts
WHERE client_id = 22844 AND id = 717604
)
AND first_name > (
SELECT first_name
FROM contacts
WHERE client_id = 22844 AND id = 717604
)
)
OR (
last_name > (
SELECT last_name
FROM contacts
WHERE client_id = 22844 AND id = 717604
)
)
)
ORDER BY last_name, first_name
LIMIT 1
There are separate indexes for first_name, last_name, deleted_at and client_id.
Is there a way to rewrite this to be more efficient? It currently takes about 250ms - 300ms when a particular client has about 3000 contacts.
Mysql 5.5 is currently in use
EDIT:
It turns out to be an order of magnitude faster if the condition is not taken into account deleted_at IS NULL
. I can just stop using soft delete and move the deleted records to the archive.
source to share
Here's an alternative method. It lists the lines after looking at the desired line:
SELECT c.*
FROM (SELECT c.*,
(@rn := if(c.id = 717604 or @rn > 0, @rn + 1, 0) as rn
FROM contacts c CROSS JOIN
(SELECT @rn := 0) params
WHERE c.client_id = 22844 AND c.deleted_at IS NULL
ORDER BY c.last_name, c.first_name
) c
WHERE rn = 2;
For this query, you want indexes on contacts(client_id, deleted_at, last_name, first_name)
.
EDIT:
Performance seems reasonable as per your request. However, the best indices contacts(client_id, id, last_name)
andcontacts(client_id, id, first_name)
source to share
SELECT b.*
FROM
( SELECT last_name, first_name
FROM contacts
WHERE client_id = 22844
AND id = 717604
) AS a
JOIN contacts AS b
WHERE deleted_at IS NULL
AND b.last_name >= a.last_name
AND ( b.first_name > a.first_name
OR b.last_name > a.last_name
)
ORDER BY b.last_name, b.first_name
LIMIT 1;
-- and have these indexes on contacts:
INDEX(client_id, id),
INDEX(last_name, first_name)
source to share