How to increase the porformance of this View/Query?

I have a Mysql View it runs about 9 seconds. I really need to increase the performance of this view. Here is my Code:

SELECT
        `tad`.`TRIP_ASSIGNMENT_DETAILS_ID` AS `TRIP_ASSIGNMENT_DETAILS_ID`,
        `lvdu`.`USERNAME` AS `USERNAME`,
        `cdm`.`DOMICILE_LOOK_UP_ID` AS `DOMICILE_LOOK_UP_ID`,
        CAST(
            GROUP_CONCAT(
                DISTINCT `ptlm`.`LOAD_ID` SEPARATOR ','
            ) AS CHAR(255) CHARSET utf8mb4
        ) AS `LOAD_RELATED`,
        MIN(`ptlm`.`PLANNED_START_DATETIME`) AS `PLANNED_START_DATETIME`
    FROM
           `Appian`.`LDA_TRIP_ASSIGNMENT_DETAILS` `tad`
                LEFT JOIN `Appian`.`LDA_VIEW_DRIVER_AND_USER_DETAILS` `lvdu`
                ON
                    (
                        `tad`.`DRIVER_DETAILS_ID` = `lvdu`.`DRIVER_DETAILS_ID`
                    )                    
            LEFT JOIN `Appian`.`LDA_VIEW_CARRIER_DOMICILE_MAPPING` `cdm`
            ON
                (
                    `lvdu`.`CARRIER_LOOK_UP_ID` = `cdm`.`CARRIER_LOOK_UP_ID`
                )
        LEFT JOIN `Appian`.`LDA_POD_TRIP_LOAD_MAPPING` `ptlm`
        ON
            (
                `tad`.`TRIP_ASSIGNMENT_DETAILS_ID` = `ptlm`.`TRIP_ASSIGNMENT_ID`
            )
    WHERE
        `tad`.`TRIP_STATUS` IN('Force De-Assigned', 'Completed') AND `tad`.`TRIP_ASSIGNED_TIME` > CURRENT_TIMESTAMP() - INTERVAL 10 DAY
    GROUP BY
        `tad`.`TRIP_ASSIGNMENT_DETAILS_ID`
    ORDER BY
        `tad`.`TRIP_ASSIGNMENT_DETAILS_ID`
    DESC

View LDA_VIEW_DRIVER_AND_USER_DETAILS (1329 rows) and LDA_VIEW_CARRIER_DOMICILE_MAPPING (216 rows) have pretty good performance and it only takes 0.01 sec. But once I run this query it takes about 9 seconds.

LDA_TRIP_ASSIGNMENT_DETAILS: only 19401 rows

LDA_POD_TRIP_LOAD_MAPPING: only 11056 rows

TRIP_ASSIGNMENT_DETAILS_ID is the primary Key of LDA_TRIP_ASSIGNMENT_DETAILS

There are no index for LDA_POD_TRIP_LOAD_MAPPING table.

Please give me some hint for solving this,

I will be much appreciate!!!!

Solution

It takes guesswork to offer definitive advice about optimizing this query. Why?

You haven't shown us the table definitions.
You haven't shown us the view definitions, or the definitions of the underlying tables.

The MySQL query planner compiles view definitions into each query and then figures out the best way to satisfy it. Please read this for more information about how to put together a good query-optimizattion question.

All that being said, these parts of your query jump out at me.

SELECT tad.TRIP_ASSIGNMENT_DETAILS_ID AS TRIP_ASSIGNMENT_DETAILS_ID,
....
    ON tad.DRIVER_DETAILS_ID = lvdu.DRIVER_DETAILS_ID
...
 WHERE tad.TRIP_STATUS IN('Force De-Assigned', 'Completed')
   AND tad.TRIP_ASSIGNED_TIME > CURRENT_TIMESTAMP() - INTERVAL 10 DAY
...
  ORDER BY tad.TRIP_ASSIGNMENT_DETAILS_ID DESC

If you create a covering index it can optimize your WHERE clause and provide the column data for your SELECT and ON clauses. That may help your query run fast.

This is the index I suggest

CREATE INDEX TAD_LOOKUP 
    ON Appian.LDA_TRIP_ASSIGNMENT_DETAILS 
       (TRIP_STATUS, 
        TRIP_ASSIGNED_TIME, 
        TRIP_ASSIGNMENT_DETAILS_ID DESC,
        DRIVER_DETAILS_ID);

This is called a covering index because it contains all columns needed to satisfy your query. TRIP_STATUS is first because it shows up as an equality filer in your WHERE clause. TRIP_ASSIGNED_TIME is second because it shows up as a range match. That means MySQL can random-access the index to the first relevant row, then scan the index sequentially to get the data you need.

The other two columns are in the index just because your query needs their values, not because they're part of the search.

Another index will probably also help, for simiar reasons.

CREATE INDEX PTLM_LOOKUP 
    ON Appian.LDA_POD_TRIP_LOAD_MAPPING
     (PTLM_TRIP_ASSIGNMENT_ID, LOAD_ID);

Pro tip: It's vitally important that you, and the next person to work on your code, are able to reason about your queries. That means formatting them to be readable is worth every second you spend doing it. Take a look above at how I formatted your WHERE clause, so all filtering predicates appear near the left margin. (I also left out the backticks around names, because they irritate me. But that's just me.)