Is it possible to optimize this MySql query

Question

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 1

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.