Mysql OneToMany JOIN most recent record only

Question

I have two tables customers and their contacts. A customer can have many contact details. In a case i need to get only the last added contact details for the customers.I can attain this by subquery. But when the data is huge, I'm facing performace lack on querying all the customers data.

Customer table (customers_customers)

+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id                | int(11)      | NO   | PRI | NULL    | auto_increment |
| company_name      | varchar(150) | NO   |     | NULL    |                |
| logo              | varchar(100) | NO   |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+

Contacts table (customers_customercontacts)

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| email       | varchar(100) | YES  |     | NULL    |                |
| mobile      | varchar(50)  | YES  |     | NULL    |                |
| customer_id | int(11)      | NO   | MUL | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

I've tried with the folowing queries, I'm getting results, but query is slow.

SELECT
    c.id,
    c.company_name,
    d.mobile
FROM customers_customers AS c
LEFT JOIN customers_customercontacts AS d
    ON d.id = (SELECT MAX(id) FROM customers_customercontacts WHERE d.customer_id = d.id);

and

SELECT
    c.id,
    c.company_name,
    d.mobile
FROM customers_customers AS c
LEFT JOIN customers_customercontacts AS d
    ON d.id = (SELECT id FROM customers_customercontacts WHERE d.customer_id = d.id
               ORDER BY id DESC LIMIT 1);

I need get get the customer's company_names and the last added phone_number of the each company_names. Is there any optimized way or a way without using subquery, to attain this?

Solved

NonCorrelated Subqueries always given better performance over than Correlated Subqueries.

can you [explain](https://dev.mysql.com/doc/refman/5.7/en/explain.html) your query? — rai, Nov 10 '17 at 05:05
I need to get customer company names and their phone numbers. Only one phone number for the each customers and that should be the last added one. — Rajez, Nov 10 '17 at 05:08
Answer by **Bill Karwin** might help you [`Retrieving the last record in each group`](https://stackoverflow.com/a/1313293/853360) — M Khalid Junaid, Nov 10 '17 at 05:13
@Rajez no i mean use the explain syntax, the table might not be optimized and there should be an index somewhere — rai, Nov 10 '17 at 05:16
Optimal [_groupwise max_](http://mysql.rjweb.org/doc.php/groupwise_max) — Rick James, Nov 11 '17 at 01:09

Tim Biegeleisen · Accepted Answer · 2017-11-11T00:53:01.697

Use a second join to a subquery which identifies the most recent contact record for each customer:

SELECT
    c.id,
    c.company_name,
    d1.mobile
FROM customers_customers AS c
LEFT JOIN customers_customercontacts AS d1
    ON c.id = d1.customer_id
INNER JOIN
(
    SELECT customer_id, MAX(id) AS max_id
    FROM customers_customercontacts
    GROUP BY customer_id
) AS d2
    ON d1.customer_id = d2.customer_id AND
       d1.id = d2.max_id;

This is a modification of your first query attempt. Note that your join condition was broken, because it does not really relate the customer and contacts table properly. The basic join should be customers_customers.id matches to customers_customercontacts.customer_id. On top of this correction, I do an additional join to restrict to the most recent contact record for each customer.

One reason this approach may improve performance over what you had originally is that this query uses a non correlated subquery to find the most recent contact for each customer. Your original attempts used correlated subqueries which tend to not perform well.

Thank you. I'm seeing good improvement in query duration. Can you explain how this change improves the performance ? — Rajez, Nov 10 '17 at 16:45

Mysql OneToMany JOIN most recent record only

1 Answers1