Can anyone speed up my MySQL stored procedure?

I am running a simulation on financial data which fires off this stored procedure over and over as fast as it can with different parameters each time.

Speed is very much of the essence here.

What the procedure does is this:

1. Find the value of variable STD that is X values away from the value of input STD on both sides of STD. (This gives the boundaries of a range around the input STD, X values in length).

2. Compiles a list from variables in this range that match a set of conditions.

3. Processes this list with another set of conditions to create a final list that represents Open Prices, Type of Order, and Close Prices.

Here is a compressed (rar) table dump for the only non-transitional table.

Here is that table's SHOW CREATE TABLE info:

| b50d1 | CREATE TABLE `b50d1` (
  `pTime` int(10) unsigned NOT NULL,
  `Slope` float(8,4) unsigned NOT NULL DEFAULT '0.0000',
  `STD` float(8,4) unsigned NOT NULL DEFAULT '0.0000',
  `Slope_Pos` int(1) unsigned NOT NULL DEFAULT '2',
  `STD_Pos` int(1) unsigned NOT NULL DEFAULT '2',
  PRIMARY KEY (`pTime`),
  UNIQUE KEY `ID1` (`pTime`,`STD`,`STD_Pos`) USING BTREE,
  UNIQUE KEY `ID2` (`pTime`,`Slope`,`Slope_Pos`),
  KEY `STD` (`STD`) USING BTREE,
  KEY `Slope` (`Slope`) USING BTREE
) ENGINE=MEMORY DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=439340 PACK_KEYS=1
/*!50100 PARTITION BY KEY (pTime)
PARTITIONS 10 */ |

Here is a little sample of the table if you don't want to download the dump:

mysql> select * from b50d1 limit 10;
+------------+---------+--------+-----------+---------+
| pTime      | Slope   | STD    | Slope_Pos | STD_Pos |
+------------+---------+--------+-----------+---------+
| 1107309300 |  1.6326 | 1.3924 |         0 |       1 |
| 1107314400 |  6.8959 | 0.2243 |         1 |       1 |
| 1107323100 | 18.1991 | 1.4685 |         1 |       0 |
| 1107335400 |  2.5014 | 0.4736 |         0 |       0 |
| 1107362100 |  4.2878 | 0.8558 |         0 |       1 |
| 1107363300 |  6.9629 | 1.4130 |         0 |       0 |
| 1107363900 |  8.1032 | 0.2860 |         0 |       0 |
| 1107367500 | 16.6244 | 0.6159 |         0 |       0 |
| 1107368400 | 19.3792 | 1.1875 |         0 |       0 |
| 1107369300 | 21.9457 | 0.9426 |         0 |       0 |
+------------+---------+--------+-----------+---------+

And here is my code:

Parameters:

t1 varchar(15),inp1 float,inp2 int,inp3 float,inp4 int,inp9 int,inp10 int

Procedure:

BEGIN
DROP TABLE IF EXISTS MainList;
DROP TABLE IF EXISTS SearchListA;
DROP TABLE IF EXISTS List1;
DROP TABLE IF EXISTS List2;


CREATE TABLE MainList(
  `pTime` int unsigned NOT NULL,
  `STD` double unsigned NOT NULL,
    `STD_Pos`   int unsigned NOT NULL,
  PRIMARY KEY (`pTime` ASC),
    INDEX (`STD` ASC) USING BTREE,
    INDEX `ID1` (`pTime` ASC, `STD` ASC) USING BTREE,
    INDEX `ID2` (`pTime` ASC, `STD` ASC, `STD_Pos` ASC) USING BTREE
    ) ENGINE = MEMORY;  


CREATE TABLE SearchListA(
  `pTime`  int unsigned  NOT NULL ,
  `STD` double unsigned NOT NULL,
    `STD_Pos`   int unsigned NOT NULL,
  `SearchEnd`  int unsigned NOT NULL,
    PRIMARY KEY (`pTime` ASC),
    INDEX (`STD` ASC),
    INDEX `ID1` (`pTime`,`STD` ASC) USING BTREE,
    INDEX `ID2` (`pTime` ASC, `STD` ASC, `STD_Pos` ASC) USING BTREE
    ) ENGINE = MEMORY;

CREATE TABLE List1(
  `pTime` int unsigned NOT NULL,
  `STD` double unsigned NOT NULL DEFAULT 0,
    `STD_Pos`   int unsigned NOT NULL DEFAULT 2,
  PRIMARY KEY (`pTime` ASC),
    INDEX (`STD`,`STD_Pos` ASC) USING BTREE
    ) ENGINE = MEMORY;  

CREATE TABLE List2(
  `pTime` int unsigned NOT NULL,
  `Slope` double unsigned NOT NULL DEFAULT 0,
    `Slope_Pos`     int unsigned NOT NULL DEFAULT 2,
  PRIMARY KEY (`pTime` ASC),
    INDEX `ID1` (`Slope`,`Slope_Pos` ASC) USING BTREE
    ) ENGINE = MEMORY;  



SET @s1 = CONCAT('INSERT INTO List1(pTime,STD,STD_Pos)  SELECT t1.pTime, t1.STD, t1.STD_Pos FROM ',t1,' AS t1 USE INDEX (STD)   WHERE   t1.STD < ',   ABS(inp1),' AND t1.pTime >= ', inp9,
' AND t1.pTime <= ', inp10,' order by STD DESC limit ', inp2);
PREPARE stmt FROM @s1;
EXECUTE stmt;

SET @lim = inp2+(inp2-(SELECT count(*) FROM List1));
SET @s2 = CONCAT('INSERT INTO List1(pTime,STD,STD_Pos)  SELECT t1.pTime, t1.STD, t1.STD_Pos FROM ',t1,' AS t1 USE INDEX (STD)   WHERE  t1.STD >=',   ABS(inp1),' AND t1.pTime >= ', inp9,
' AND t1.pTime <= ', inp10,' order by STD ASC limit ?');
PREPARE stmt FROM @s2;
EXECUTE stmt USING @lim;

##########################################
SET @s3 = CONCAT('INSERT INTO List2(pTime,Slope,Slope_Pos)  SELECT t1.pTime, t1.Slope, t1.Slope_Pos FROM ',t1,' AS t1 USE INDEX (Slope) WHERE t1.Slope < ',ABS(inp3),' AND t1.pTime >= ', inp9,
' AND t1.pTime <= ', inp10,' order by Slope DESC limit ', inp4);
PREPARE stmt FRO开发者_如何学CM @s3;
EXECUTE stmt;

SET @lim = inp4+(inp4-(SELECT count(*) FROM List2));
SET @s4 = CONCAT('INSERT INTO List2(pTime,Slope,Slope_Pos)  SELECT t1.pTime, t1.Slope, t1.Slope_Pos FROM ',t1,' AS t1 USE INDEX (Slope) WHERE t1.Slope >=',ABS(inp3),' AND t1.pTime >= ', inp9,
' AND t1.pTime <= ', inp10,' order by Slope ASC limit ?');
PREPARE stmt FROM @s4;
EXECUTE stmt USING @lim;

#########################################


#########################################
SET @minSL1 = (SELECT MIN(Slope) FROM List2);
SET @maxSL1 = (SELECT MAX(Slope) FROM List2);

SET @minSD1 = (SELECT MIN(STD) FROM List1);
SET @maxSD1 = (SELECT MAX(STD) FROM List1);


SET @s = CONCAT('INSERT INTO MainList(pTime,STD,STD_Pos) SELECT t1.pTime, t1.STD, t1.STD_Pos FROM ',t1,' AS t1 ',
' WHERE t1.Slope >= ', @minSL1 ,
' AND t1.Slope <= ', @maxSL1 ,
' AND t1.STD  >= ', @minSD1 ,
' AND t1.STD  <= ', @maxSD1,
' AND ((t1.Slope_Pos <> t1.STD_Pos) AND t1.pTime >= ', inp9,
' AND t1.pTime <= ', inp10,' ) ORDER BY t1.pTime'
);

PREPARE stmt FROM @s;
EXECUTE stmt;

INSERT INTO SearchListA (pTime,STD,STD_Pos,SearchEnd)
SELECT sql_no_cache M1.pTime,M1.STD,M1.STD_Pos,M2.pTime 
FROM MainList as M1
JOIN MainList as M2
ON(M2.pTime = (
SELECT M3.pTime FROM MainList as M3 WHERE M3.pTime>M1.pTime ORDER BY M3.pTime ASC  limit 1)
);



SET @q = CONCAT('
SELECT 
m.pTime as OpenTime,
CASE WHEN m.STD_Pos = 0 THEN 1 ELSE -1 END As Type,
min( big.pTime ) as CloseTime
FROM   
    SearchListA m
    JOIN ',t1,' as big ON (
        big.pTime >  m.pTime
        AND big.pTime <= LEAST(m.SearchEnd,m.pTime+172800)
        AND m.STD < big.STD AND m.STD_Pos <> big.STD_Pos
    )
GROUP BY m.pTime
');


PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


END

Sample function call:

CALL `data`.`JustMain`( "b50d1",1.5,5000,6.43,5000,1121126400,1278892800)

I can currently run this procedure in under a second, but faster is better!

If anyone needs more info, I will include it.

---

Here is just a start:

SUGGESTION # 1 : Removing Duplicate Indexes from your tables. Having less indexes to maintain speeds up loading tables regardless of storage engine.

The MainList table has two indexes that begin with the same two columns: ID1 and ID2.

The MainList table has three indexes that begin with the same column: Primary Key, ID1 and ID2.

IF the PRIMARY KEY is for MainList is pTime, then no other row in the table will share the same pTime. Make sure that is your intent.

If you know that more than one row in the MainList is supposed to have the same pTime (i.e., a pTime with multiple STDs) then change PRIMARY to (pTime ASC, STD ASC).

Since you have queries involving the pTime, STD, STD_Pos, and if this tuple is Unique, then the three columns can be the PRIMARY KEY (pTime ASC,STD ASC,STD_Pos ASC)

To really play it safe, the MainList should look like this:

CREATE TABLE MainList(
  `pTime` int unsigned NOT NULL,
  `STD` double unsigned NOT NULL,
  `STD_Pos` int unsigned NOT NULL,
  INDEX `NDX1` (`STD` ASC, `STD_Pos` ASC) USING BTREE,
  INDEX `NDX2` (`pTime` ASC, `STD` ASC, `STD_Pos` ASC) USING BTREE
) ENGINE = MEMORY;  

That's right, there is no PRIMARY KEY. Let the MySQL Query Optimizer decide from here. If you are absolutely certain that pTime is Unique on every row, the table can look like this:

CREATE TABLE MainList(
  `pTime` int unsigned NOT NULL,
  `STD` double unsigned NOT NULL,
  `STD_Pos` int unsigned NOT NULL,
  INDEX `NDX1` (`STD` ASC, `STD_Pos` ASC) USING BTREE,
  INDEX `NDX2` (`pTime` ASC, `STD` ASC, `STD_Pos` ASC) USING BTREE,
  UNIQUE INDEX pTime (pTime)
) ENGINE = MEMORY;  

SUGGESTION # 2 : Disabling NonUnique Indexes During Load

This is how mysqldump creates dumps, specifically for loading data back into mysql as fast as possible.

Before loading any table that has additional NonUnique indexes to the following (as an example):

SET @s1 = 'ALTER TABLE List1 DISABLE KEYS';
    PREPARE stmt FROM @s1;
    EXECUTE stmt;
SET @s1 = CONCAT('INSERT INTO List1(pTime,STD,STD_Pos)  SELECT t1.pTime, t1.STD, t1.STD_Pos FROM ',t1,' AS t1 USE INDEX (STD)   WHERE   t1.STD < ',   ABS(inp1),' AND t1.pTime >= ', inp9,
    ' AND t1.pTime <= ', inp10,' order by STD DESC limit ', inp2);
    PREPARE stmt FROM @s1;
    EXECUTE stmt;
SET @s1 = 'ALTER TABLE List1 ENABLE KEYS';
    PREPARE stmt FROM @s1;
    EXECUTE stmt;

Doing this builds the NonUnique Index for List1. The PRIMARY is loaded immediately into List1. The NonUnique index for List1 will load afterwards and a linear fashion. You can do this also for List2.

The second suggestion may not help that much if the data load is small, but can accommodate fast loading as the data load increases.