LEFT JOIN two MySql tables while second table order by desc and limit to 1

Question

I have two tables like below

NA_table
+----+-------+-------+---------------+---------------------+-----+
| id | nname  | phone |      nip       |        ntime      | iid |
+----+-------+-------+---------------+---------------------+-----+
|  1 | john  | +xxxx |  192.168.1.10 | 2020-04-21 11:10:10 |  23 |
|  2 | bill  | +xxxx |  192.168.1.11 | 2020-04-21 12:10:10 |  44 |
|  3 | husky | +xxxx |  192.168.1.12 | 2020-04-21 13:10:10 |  44 |
|  4 | lab   | +xxxx |  192.168.1.13 | 2020-04-21 14:10:10 |  33 |
|  5 | bill  | +xxxx |  192.168.1.12 | 2020-04-21 11:10:15 |  44 |
|  6 | cal   | +xxxx |  192.168.1.13 | 2020-04-21 16:10:10 |  12 |
|  7 | jess  | +xxxx |  192.168.1.11 | 2020-04-21 17:10:10 |  90 |
|  8 | minn  | +xxxx |  192.168.1.12 | 2020-04-21 18:10:10 |  44 |
|  9 | jess  | +xxxx |  192.168.1.11 | 2020-04-21 17:10:10 |  21 |
+----+-------+-------+---------------+---------------------+-----+


CD_table
+----+--------------+---------------------+-------+
| cid |     cip      |       ctime        | other |
+----+--------------+---------------------+-------+
|  1 | 192.168.1.11 | 2020-04-21 03:22:19 | text  |
|  2 | 192.168.1.12 | 2020-04-21 03:10:10 | text  |
|  3 | 192.168.1.11 | 2020-04-21 06:11:12 | text  |
|  4 | 192.168.1.19 | 2020-04-21 06:10:03 | text  |
|  5 | 192.168.1.22 | 2020-04-21 13:10:10 | text  |
|  6 | 192.168.1.11 | 2020-04-21 14:14:17 | text  |
|  7 | 192.168.1.12 | 2020-04-21 16:09:10 | text  |
|  8 | 192.168.1.11 | 2020-04-22 09:07:11 | text  |
+----+--------------+---------------------+-------+

Using this two tables I want to run this query

SELECT
CD_table.ctime AS CTIME,
CD_table.cip AS CIP,
CD_table.other AS OTHER,
NA_table.phone AS PHONE,
FROM NA_table
LEFT JOIN CD_table 
ON NA_table.nip = CD_table.cip 
WHERE
NA_table.NAIID = '44'
AND
NA_table.ntime between '2020-04-21 11:10:00' AND '2020-04-21 11:10:59'
AND
CD_table.ctime between '2020-04-21 00:10:00' AND '2020-04-21 23:59:59'

which gives me this result.

+----------------------+---------------+-------+-------+
|        CTIME         |      CIP      | OTHER | PHONE |
+----------------------+---------------+-------+-------+
| 2020-04-21 03:22:19  |  192.168.1.11 | text  | +xxxx |
| 2020-04-21 03:10:10  |  192.168.1.12 | text  | +xxxx |
| 2020-04-21 06:11:12  |  192.168.1.11 | text  | +xxxx |
| 2020-04-21 14:14:17  | 192.168.1.11  | text  | +xxxx |
| 2020-04-21 16:09:10  |  192.168.1.12 | text  | +xxxx |
+----------------------+---------------+-------+-------+

But I want my output sorted by CTIME and print only last matching record of the each CD_table record like this

+---------------------+---------------+-------+-------+
|        CTIME        |      CIP      | OTHER | phone |
+---------------------+---------------+-------+-------+
| 2020-04-21 14:14:17 | 192.168.1.11  | text  | +xxxx |
| 2020-04-21 16:09:10 |  192.168.1.12 | text  | +xxxx |
+---------------------+---------------+-------+-------+

Where I can do DESC and LIMIT part withn my query. Or is there other way that I can split my query.

Answer 1

You can filter the cd_table with a subquery for the latest record within the given interval.

Also, since your query does not return anything from na_table, I turned the join to an exists condition - this is usually more efficient in this situation.

select c.ctime, c.cip, c.other
from cd_table c
where 
    c.ctime = (
        select max(c1.ctime) 
        from cd_table c1 
        where 
            c1.cip = c.cip
            and c1.ctime >= '2020-04-21 00:10:00'
            and c1.ctime <  '2020-04-22'
    )
    and exists (
        select 1 
        from na_table n
        where 
            n.nip = c.cip
            and n.naid = 44 
            and n.ntime >= '2020-04-21 11:10:00'
            and n.ntime <  '2020-04-21 11:11:00'
    )

Note that I rewrote the conditions on the dates to use half-open intervals (this avoids dealing with trailing 59 seconds everytime).

For performance, consider the following indexes:

cd_table(cip, ctime)
na_table(nip, naid, time)

Adding other to the index on cd_table might give an additional boost.