I've got a project I'm working on where due to NDA I can't reveal what I'm doing, but I can generalize in hopes of determining if my queries are the most efficient for a particular purpose. If you look at the following query, it's doing a couple of things:
1. By grouping, I am finding people that have more than one shipping address.
2. By ordering, I am finding datetime values in a "last_called" field that are the oldest possible, but they have to be at least 5 days old.
Code:
SELECT
id
FROM
(SELECT
p.id,
p.last_called,
COUNT(a.addr_id) AS cnt
FROM
people p
JOIN addresses a
ON p.id = a.person_id
WHERE p.last_called <= DATE_SUB(NOW(), INTERVAL 5 DAY)
AND a.type = "shipping"
GROUP BY p.id
HAVING COUNT(a.addr_id) > 1) foo
ORDER BY last_called ASC
LIMIT 10
This works as expected. I'm getting exactly the results I expect, and that is a bunch of people IDs. I take those people IDs and plug them into this query:
Code:
SELECT
*
FROM
people p
LEFT JOIN addresses a
ON p.id = a.person_id
WHERE p.id IN (...)
I need this second query, because along with the people's shipping addresses, I also need their billing address.
I think one mistake I am making is that I am fetching the result of the first query, and using PHP to implode the people IDs. I should just let MySQL do all the work, but when I try to put the first query in the second query, I get an error:
Error Code: 1235
This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
This is that attempted query:
Code:
SELECT
*
FROM
people p
LEFT JOIN addresses a
ON p.id = a.person_id
WHERE p.id IN (SELECT
id
FROM
(SELECT
p.id,
p.last_called,
COUNT(a.addr_id) AS cnt
FROM
people p
JOIN addresses a
ON p.id = a.person_id
WHERE p.last_called <= DATE_SUB(NOW(), INTERVAL 5 DAY)
AND a.type = "shipping"
GROUP BY p.id
HAVING COUNT(a.addr_id) > 1) foo
ORDER BY last_called ASC
LIMIT 10
)
I understand the error / limitation, so I'm not asking about that, but would still like to do something to make this as efficient as possible.
The people table has about 50K records. The addresses table has about 250K rows.
When I need to change the limit in the first query to something like 300, these queries take about 10 seconds. I'm wondering if anyone has any ideas that will allow me to shave a few seconds off and still get the same results.
