CODEWITHSUNDEEP
mysqlsqloptimization
davidahines
davidahines

Reputation: 4094

Avoiding copying to temporary tables

I normally do most of my work in java, I cannot really find what I'm doing here that is causing it to need to copy to a table and die.

SELECT company.tblusers.userid, 
db.operations.id AS operation_id,
SUM(TIME_TO_SEC(db.batch_log.time_elapsed)) AS time_elapsed, 
SUM(db.tasks.estimated_nonrecurring + db.tasks.estimated_recurring) AS total_elapsed,
COUNT(db2.ncr_ncr.id) AS number_of_ncrs
FROM company.tblusers 
INNER JOIN db.operations 
INNER JOIN db.batch_log ON company.tblusers.userid = batch_log.userid 
INNER JOIN db.batches ON batch_log.batch_id = batches.id 
INNER JOIN db.tasks ON db.batches.id = db.tasks.batch_id
INNER JOIN db2.ncr_ncr ON company.tblusers.sugar_name = db2.ncr_ncr.employee
WHERE company.tblusers.departmentid = 8 
AND db.operations.id = db.batches.operation_id 
AND db.batches.id = db.tasks.batch_id 
AND db.batch_log.userid = company.tblusers.userid
AND db2.ncr_ncr.employee = company.tblusers.sugar_name 
GROUP by company.tblusers.userid, db.batches.operation_id

EDIT: Explain output

"id";"select_type";"table";"type";"possible_keys";"key";"key_len";"ref";"rows";"Extra"
"1";"SIMPLE";"ncr_ncr";"ALL";NULL;NULL;NULL;NULL;"2700";"Using temporary; Using filesort"
"1";"SIMPLE";"batch_log";"ALL";NULL;NULL;NULL;NULL;"78026";""
"1";"SIMPLE";"tblusers";"eq_ref";"PRIMARY";"PRIMARY";"52";"ramses.batch_log.userid";"1";"Using where"
"1";"SIMPLE";"tasks";"ref";"Index 2";"Index 2";"38";"ramses.batch_log.batch_id";"2";""
"1";"SIMPLE";"batches";"eq_ref";"PRIMARY";"PRIMARY";"38";"ramses.tasks.batch_id";"1";"Using where"
"1";"SIMPLE";"operations";"eq_ref";"PRIMARY";"PRIMARY";"4";"ramses.batches.operation_id";"1";"Using where; Using index"

Upvotes: 1

Views: 2157

Answers (1)

Jeffrey L Whitledge
Jeffrey L Whitledge

Reputation: 59513

If you aggregate a value while grouping on a non-indexed combination of columns, then the SQL engine will create a temporary table as part of its normal functioning in order to hold the partially aggregated values. You have two SUMs and a COUNT that need to be calculated for each combination of company.tblusers.userid and db.batches.operation_id. These values must be stored, since, clearly, there is no index that contains both of those columns, since they are in different tables.

I don't know why it would die, though, unless it is actually building the full join as a termporary table, because of the COUNT aggregation.

Instead of joining to the db2.ncr_ncr table, you should probably just make it a correlated subquery. This may require less storage:

SELECT
        company.tblusers.userid,
        db.operations.id AS operation_id,
        SUM(TIME_TO_SEC(db.batch_log.time_elapsed)) AS time_elapsed,
        SUM(db.tasks.estimated_nonrecurring + db.tasks.estimated_recurring) AS total_elapsed,
        (SELECT COUNT(*) FROM db2.ncr_ncr WHERE db2.ncr_ncr.employee = company.tblusers.sugar_name) AS number_of_ncrs
FROM 
        company.tblusers
        INNER JOIN db.batch_log ON batch_log.userid = company.tblusers.userid
        INNER JOIN db.batches ON db.batches.id = db.batch_log.batch_id
        INNER JOIN db.operations ON db.operations.id = db.batches.operation_id
        INNER JOIN db.tasks ON db.tasks.batch_id = db.batch_log.batch_id
WHERE
        company.tblusers.departmentid = 8
GROUP BY 
        company.tblusers.userid,
        db.batches.operation_id

UPDATE

Based on the explain-plan results, you do not have any index that the query can use on ncr_ncr. This is bad. It is actually creating a new copy of the table that it can sort by employee so that the join can happen in a reasonable amount of time.

Even though that table is not very big (just 2700 rows), you still need to put an index (preferably correlated, if the rest of the system allows it) on db2.ncr_ncr.employee, otherwise your performance is going to be suffer badly. This table is small enough that making and sorting a temporary copy shouldn't be a problem, but it will still hurt your performance.

The same is true for batch_log.userid.

Upvotes: 8

Related Questions