CODEWITHSUNDEEP
mysqlsqlquery-optimization
Stephen H. Anderson
Stephen H. Anderson

Reputation: 1068

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.

Upvotes: 0

Views: 151

Answers (2)

Rick James
Rick James

Reputation: 142218

Don't use IN ( SELECT ... ), use a JOIN instead. Then get rid of TempTable by using it as a subquery: JOIN ( SELECT ... ) with what used to be the body of TempTable.

Don't use 'commajoin', use explicit JOIN ... ON.

Have INDEX(objectId, date) and INDEX(user, id).

Using a MEMORY table for temps is dangerous because you cannot predict the value of max_heap_table_size that you will need. And setting it too large could steal RAM, thereby slowing down everything.

Upvotes: 0

Solarflare
Solarflare

Reputation: 11106

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.

Upvotes: 1

Related Questions