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 :
- it takes more than 1.5s.
- 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.
Advertisement
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.