Stephen H. Anderson Stephen H. Anderson - 5 months ago 9
MySQL Question

Is it possible to optimize this MySql query

I'm looking for a way to optimize the following MySQL Stored Procedure:

DECLARE total BIGINT UNSIGNED;

DROP TEMPORARY TABLE IF EXISTS tempTable;
CREATE TEMPORARY TABLE tempTable(identityValue BIGINT(20) UNSIGNED );

INSERT INTO tempTable
SELECT
`getIdentity`(samples.`originalId`)
FROM
`rawData`
WHERE
`samples`.`groupId` = _group;


SET total = (

SELECT
IFNULL( SUM(getTotal(rawData.id, NULL, NULL)), 0)

FROM
rawData

WHERE
rawData.user= _user AND
getIdentity(rawData.id) IN (SELECT * FROM tempTable)

);


DROP TEMPORARY TABLE IF EXISTS tempTable;

RETURN total;


The getIdentity function looks like this:

RETURN (SELECT IFNULL(MIN(id1), _id)
FROM `equivalences` WHERE
id1 = _id OR id2 = _id);


The getTotal function looks like this:

BEGIN
DECLARE total INT;

IF (_startDate IS NULL OR _endDate IS NULL) THEN

SET total = (SELECT IFNULL(SUM(ops.downloads),0)
FROM objects, ops
WHERE objects.id = _objectId AND ops.objectId = _objectId);
ELSE
SET total = (SELECT IFNULL(SUM(ops.downloads),0)
FROM objects, ops
WHERE objects.id = _objectId AND ops.objectId = _objectId AND
ops.`date` BETWEEN _startDate AND _endDate);
END IF;

RETURN total;
END


Right now it's taking ~350ms to run. Most of the time seems to be related to the temporary tables I'm creating. The tables already have the indexes I considered necessary after using EXPLAIN.

Answer

I guess you don't use memory for temporary tables. Try

CREATE TEMPORARY TABLE tempTable(identityValue BIGINT(20) UNSIGNED ) engine=memory;

For mysql >= 5.6 you can set default_tmp_storage_engine=MEMORY in your config to make that a permanent option.

Since you already have good indexes, it depends on the amount of data in your temp table if it would make sense to analyze optimization of the rest of your query.