what are the alternative approaches to optimize this sql query?

I am just a beginner in sql and i am trying to optimize sql query but couldn’t get any idea yet, so i am sharing the query with you guys if anybody help me out through this will be very appreciated.

Tables are already indexed but if you have any composite indexing approach or anything you can share it.
Here is the code :

var viewDealsSQLV2 = function (apiReference, userId, workflowId, dealStatus, startFrom, pageSize, rangeFilter,
dealStage, role, businessOwnerId, dealScore, getFollowersData, userFilters, tags, country, city,getMemberAssignToDeals) {
return new Promise((resolve, reject) => {
    var tableName = constants.tableName.TB_DEAL_SUMMARY
    var limitFilter = "";
    var tagsFilter = "";
    var followFilter = ``,
        followJoin = ``;
    if ((role == constants.userRoles.TEAM_MEMBER) || ((role == constants.userRoles.ADMIN || role == constants.userRoles.BUSINESS_OWNER) && getFollowersData && Array.isArray(userFilters) && userFilters.length)) {
        followJoin = ` LEFT JOIN tb_deal_follower ON tds.deal_id = tb_deal_follower.deal_id   `;

    }
    var filters = ''
    var organisationJoin = ''
    if (tags) {
        tagsFilter = ` LEFT JOIN   tb_deal_tag_mapping ON tds.deal_id = tb_deal_tag_mapping.deal_id   `

    }
    if (city || country) {
        organisationJoin = ` LEFT JOIN tb_organizations ON tb_organizations.organization_id = tds.organization_id `
    }
    // console.log("+++++++",followJoin)
    var sql = ` SELECT 
    deal_id, 
    email,
    phone,
    activity_date,
    activity_status,
    contact_name,
    deal_tag_names,
    name,
    organization_id,
    contact_person_id,
    deal_score,
    marketing_campaign,
    business_owner_id,
    deal_stage,
    deal_stage_id,
    estimated_worth,
    deal_status,
    deal_color,
    deal_order,
    currency_id,
    owner_id ,
    created_at,
    latest_activity_date
FROM
    (
    SELECT
        deal_id,
        email,
        phone,
        activity_date,
        activity_status,
        contact_name,
        deal_tag_names,
        name,
        organization_id,
        contact_person_id,
        deal_score,
        marketing_campaign,
        business_owner_id,
        deal_stage,
        deal_stage_id,
        estimated_worth,
        deal_status,
        deal_color,
        deal_order,
        currency_id,
        owner_id,
        created_at,
        latest_activity_date,
        CASE WHEN @pre_stage = deal_stage THEN @previous_rank := @previous_rank + 1 ELSE @previous_rank := 1
END AS rank,
@pre_stage := deal_stage
FROM
    (
    SELECT 
        tds.deal_id,
        contact_emal AS email,
        contact_phone as phone,
        latest_activity_date AS activity_date,
        latest_activity_status AS activity_status,
        contact_name,
        deal_tag_names,
        tds.name ,
        tds.organization_id,
        contact_person_id,
        deal_score,
        marketing_campaign,
        tds.business_owner_id,
        deal_stage,
        deal_stage_id,
        estimated_worth,
        deal_status,
        deal_color,
        deal_order,
        currency_id,
        tds.owner_id,
        tds.created_at,
        tds.latest_activity_date
    
    FROM
        ${tableName}  tds   ${followJoin} ${organisationJoin} ${tagsFilter}
    WHERE
    tds.business_owner_id = ? AND workflow_id = ? 
      `
    var queryParams = [businessOwnerId, workflowId];

    if (dealStage && dealStage.length) {
        sql += ` AND tds.deal_stage_id IN   (?) `;
        queryParams.push(dealStage);
    }
    if (userFilters && userFilters.length) {
    
        if(followJoin && role == constants.userRoles.TEAM_MEMBER){
            if(getMemberAssignToDeals){
                sql += ` AND ( ( tds.assign_to IN (?)  AND tb_deal_follower.is_active = 1   AND  tb_deal_follower.follower_id = ?  )  OR tds.assign_to = ? ) `;
                queryParams.push(userFilters);
                queryParams.push(userId);
                queryParams.push(userId);
            }else{

                sql += ` AND ( ( tds.assign_to IN (?)  AND tb_deal_follower.is_active = 1   AND  tb_deal_follower.follower_id = ?  )  ) `;
                queryParams.push(userFilters);
                queryParams.push(userId);
            }
        }else{
            sql += ` AND  tds.assign_to IN (?)   `;
            queryParams.push(userFilters);
        }

    }
    if (dealStatus && Array.isArray(dealStatus) && dealStatus.length) {
        sql += ` AND tds.deal_status IN (?) `;
        queryParams.push(dealStatus);
    }

    if (rangeFilter.start && rangeFilter.end) {
        sql += ` AND tds.deal_created_at > ? AND  tds.deal_created_at < ? `;
        queryParams.push(rangeFilter.start, rangeFilter.end);
    }
    if (dealScore && Array.isArray(dealScore) && dealScore.length) {
        sql += `AND tds.deal_score = ?`;
        queryParams.push(dealScore)
    }

    if (followJoin  && role != constants.userRoles.TEAM_MEMBER)  {
        sql += `   AND tb_deal_follower.is_active = 1   `;

    }
    if (organisationJoin) {
        if (city) {
            var cityText = ''
            for (elements in city) {
                cityText += `'${city[elements]}', `
            }
            cityText = cityText.slice(0, -2)
            sql += ` AND tb_organizations.city IN (${cityText}) `
        }
        if (country) {
            var countryText = ''
            for (elements in country) {
                countryText += `'${country[elements]}', `
            }
            countryText = countryText.slice(0, -2)
            sql += ` AND tb_organizations.country IN (${countryText}) `
        }
    }

    if (tags) {
        sql += ` AND tb_deal_tag_mapping.tag_id IN (${tags}) AND tb_deal_tag_mapping.is_deleted = 0 `
    }


    sql += `   GROUP BY  tds.deal_id
    ORDER BY
    deal_stage ASC,
    tds.deal_id
    DESC
    `

    // 
    sql += `) a
    JOIN(
        SELECT
        @previous_rank := 1,
        @pre_stage := 0
    ) b
) AS t `

    if (pageSize) {
        pageSize = startFrom + pageSize;
        sql += ` WHERE
    rank >= ${startFrom} AND rank <=  ${pageSize} `
    }
    sql = sql.replace(/--.*(n|$)/g, "").replace(/s+/g, ' ');

For particular case the sql query is :

SELECT 
      deal_id,
      email, 
      phone, 
      activity_date, 
      activity_status, 
      contact_name, 
      deal_tag_names, 
      name, 
      organization_id, 
      contact_person_id, 
      deal_score, 
      marketing_campaign, 
      business_owner_id, 
      deal_stage, 
      deal_stage_id, 
      estimated_worth, 
      deal_status, 
      deal_color, 
      deal_order, 
      currency_id, 
      owner_id , 
      created_at, 
      latest_activity_date 
   FROM 
      ( SELECT 
              deal_id, 
              email, 
              phone, 
              activity_date, 
              activity_status, 
              contact_name, 
              deal_tag_names, 
              name, 
              organization_id, 
              contact_person_id, 
              deal_score, 
              marketing_campaign, 
              business_owner_id, 
              deal_stage, 
              deal_stage_id, 
              estimated_worth, 
              deal_status, 
              deal_color, 
              deal_order, 
              currency_id, 
              owner_id, 
              created_at, 
              latest_activity_date, 
              CASE WHEN @pre_stage = deal_stage 
                   THEN @previous_rank := @previous_rank + 1 
                   ELSE @previous_rank := 1 END AS rank, 
              @pre_stage := deal_stage 
           FROM 
              ( SELECT 
                      tds.deal_id, 
                      contact_emal AS email, 
                      contact_phone as phone, 
                      latest_activity_date AS activity_date,
                      latest_activity_status AS activity_status,
                      contact_name, 
                      deal_tag_names, 
                      tds.name , 
                      tds.organization_id, 
                      contact_person_id, 
                      deal_score, 
                      marketing_campaign, 
                      tds.business_owner_id, 
                      deal_stage, 
                      deal_stage_id, 
                      estimated_worth, 
                      deal_status, 
                      deal_color, 
                      deal_order, 
                      currency_id, 
                      tds.owner_id, 
                      tds.created_at, 
                      tds.latest_activity_date 
                   FROM 
                      tb_deal_summary_v2 tds 
                   WHERE 
                          tds.business_owner_id = 'n6lqj9nayewl6osh2b82' 
                      AND workflow_id = 'c110988e-6519-4d3f-a675-de083e59e9fd' 
                      AND tds.deal_stage_id IN ('c110988e-6519-4d3f-a675-de083e59e9fd1','c110988e-6519-4d3f-a675-de083e59e9fd2') 
                      AND tds.assign_to IN ('n6lqj9nayewl6osh2b82') 
                      AND tds.deal_status IN (0) 
                   GROUP BY 
                      tds.deal_id 
                   ORDER BY 
                      deal_stage ASC, 
                      tds.deal_id DESC ) a 
            JOIN
            ( SELECT 
                    @previous_rank := 1, 
                    @pre_stage := 0 ) b 
         ) AS t 
   WHERE 
          rank >= 0 
      AND rank <= 20

There might be more values in IN clause but in this case there is only one for each IN clause.

some of the observation and their results :

1. it takes more than 1.5s.
2. Output of explain statement

Any kind of suggestion will be very helpful, Thanks so much in advance.

EDIT : The purpose of this sql is to pagination of deals stage wise, For example if the rank >= 0 and rank <= 20 then each of the stage has maximum 20 deals and rest of deals will be fetched in the next page.
Here is the image : Rank wise pagination
if the deal stage change the rank start with 1 and if it is from the same stage then rank will increment.

Answer

Your Javascript generates a so-called frankenquery — a query with many possible permutations of filter (WHERE) clauses. Each variant of your query will have its own ideal compound index.

Your EXPLAIN shows you already have an index on business_owner_id. And I guess your Javascript always generates a query saying WHERE business_owner_id = someConstant. So that’s a good starting point for optimization. If things still aren’t fast enough you may want to figure out which variants of your frankenquery are either slowest or most common or both.

For the variant you showed us, this compound index or some version of it might help.

CREATE INDEX index_name ON tb_deal_summary_v2 
 (business_owner_id, deal_status, workflow_id, deal_stage_id, assign_to);

Notice that lots of single-column indexes are generally harmful to database performance. Other than the indexes for primary keys and uniqueness constraints, your choice of indexes should be based on common query patterns.

By the way, I hope your deal_id column is the table’s primary key or has a unique index on it. If not, you’re misusing MySQL’s notorious nonstandard extension to GROUP BY, and the column values produced by your queries are unpredictable. Unpredictable results are no way to handle other peoples’ money.

A good reference is Marcus Winand’s https://use-the-index-luke.com/ online book.