Geek asks and answers

Please help me with optimizing a table / nested JOIN: Too slow!

I have about 250 tables in my database, each of which has exactly 439340 rows.

mysql> SHOW CREATE TABLE data.b50d1 ;
+-------+--------------------------------------------------------------------------------------------
CREATE TABLE `b50d1` (
      `pTime` int(10) unsigned NOT NULL,
      `Slope` double NOT NULL,
      `STD` double NOT NULL,
      PRIMARY KEY (`pTime`),
      KEY `Slope` (`Slope`) USING BTREE
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MIN_ROWS=43940 MAX_ROWS=43940 PACK_KEYS=1 ROW_FORMAT=FIXED |
+-------+--------------------------------------------------------------------------------------------

As you can see, each table has three columns:

  • pTime : POSIX timestamp. This column (and all its values) is exactly the same in every table. This is myPRIMARY KEY
  • Slope
  • STD

The Slope and STD columns have "signed double" values ​​that differ from row to row and from table to table.

Here is a small example from one of the tables:

mysql> SELECT * FROM data.b50d1 limit 10;
+------------+------------+-------------+
| pTime      | Slope      | STD         |
+------------+------------+-------------+
| 1104537600 | 6.38733032 | -1.13387667 |
| 1104537900 | 5.58733032 | -0.93810617 |
| 1104538200 | 5.30135747 | -0.51912757 |
| 1104538500 |  5.4678733 | -0.54460575 |
| 1104538800 | 5.58190045 | -0.46369055 |
| 1104539100 | 5.50226244 | -0.46712018 |
| 1104714000 | 5.31221719 | -0.25210485 |
| 1104714300 | 4.72941176 |  0.00321249 |
| 1104714600 | 5.19638009 |  0.64116376 |
| 1104714900 | 5.12941176 |  0.39599099 |
+------------+------------+-------------+

Using these tables, I run the stored procedure. This procedure consists of the following steps:

STEP 1) CREATE TEMPORARY TABLEMain List ...

2) INSERT SELECT . .

3) SELECT JOIN MainList.STD TEMPORARY (MainList) , ( ).

4) JOIN MainList .

:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetTimeList`(t1 varchar(7),t2 varchar(7),t3 varchar(7),inp1 float,inp2 float,inp3 float,inp4 float,inp5 float,inp6 float,inp7 float,inp8 float,inp9 float,inp10 float)
    READS SQL DATA
BEGIN
DROP TABLE IF EXISTS MainList;

CREATE TEMPORARY TABLE MainList(
  `pTime` int unsigned NOT NULL,
  `STD` double NOT NULL,
  PRIMARY KEY (`pTime`),
    KEY (`STD`) USING BTREE
    ) ENGINE = MEMORY;  

SET @s = CONCAT('INSERT INTO MainList(pTime,STD)  SELECT DISTINCT t1.pTime, t1.STD FROM ',t1,' AS t1 JOIN (',t2,' as t2 ,',t3,' as t3 )',
' ON (( t1.Slope >= ', inp1,
' AND t1.Slope <= ', inp2,
' AND t1.STD  >= ', inp3,
' AND t1.STD  <= ', inp4,
' AND t2.Slope  >= ', inp5,
' AND t2.Slope  <= ', inp6,
' AND t3.Slope  >= ', inp7,
' AND t3.Slope  <= ', inp8,
' ) OR ( t1.Slope <= ', 0-inp1,
' AND t1.Slope >= ', 0-inp2,
' AND t1.STD  <= ', 0-inp3,
' AND t1.STD  >= ', 0-inp4,
' AND t2.Slope  <= ', 0-inp5,
' AND t2.Slope  >= ', 0-inp6,
' AND t3.Slope  <= ', 0-inp7,
' AND t3.Slope  >= ', 0-inp8,
' ) ) AND ((t1.Slope < 0 XOR t1.STD < 0) AND t1.pTime = t2.pTime AND t2.pTime = t3.pTime AND t1.pTime >= ', inp9,
' AND t1.pTime <= ', inp10,' ) ORDER BY t1.pTime'
);

PREPARE stmt FROM @s;
EXECUTE stmt;

SET @q= CONCAT('SELECT m.pTime as OpenTime, CASE WHEN m.STD < 0 THEN 1 ELSE -1 END As Type, mu.pTime As CloseTime from MainList m LEFT JOIN ',t1,' mu ON mu.pTime = ( SELECT DISTINCT md.pTime FROM ',t1,' md WHERE md.pTime>m.pTime',' AND md.pTime <= ', inp10,
                            ' AND SIGN (md.STD)!= SIGN (m.STD) AND ABS(md.STD) >= ABS(m.STD) ORDER BY md.pTime LIMIT 1 )');


PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE MainList;
END

. , EXPLAIN EXTENDED '( temp ):

INSERT INTO MainList(pTime,STD)
SELECT
  t1.pTime,
  t1.STD
 FROM
  b50d1 AS t1
 JOIN(b75d1 AS t2, b100d1 AS t3)ON(
  (
          t1.Slope >= 2.3169
          AND t1.Slope <= 7.0031
          AND t1.STD >= - 2.068
          AND t1.STD <= - 0.972
          AND t2.Slope >= 0.3179
          AND t2.Slope <= 5.7221
          AND t3.Slope >= 2.6466
          AND t3.Slope <= 35.7534
  )
  OR(
          t1.Slope <= - 2.3169
          AND t1.Slope >= - 7.0031
          AND t1.STD <= 2.068
          AND t1.STD >= 0.972
          AND t2.Slope <= - 0.3179
          AND t2.Slope >= - 5.7221
          AND t3.Slope <= - 2.6466
          AND t3.Slope >= - 35.7534
  )
 )
 AND(
  (t1.Slope < 0 XOR t1.STD < 0)
  AND t1.pTime = t2.pTime
  AND t2.pTime = t3.pTime
  AND t1.pTime >= 1104710000
  AND t1.pTime <= 1367700000
 )
 ORDER BY
  t1.pTime;

EXPLAIN EXTENDED:

+----+-------------+-------+--------+---------------+---------+---------+---------------+--------+----------+-----------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows   | filtered | Extra                       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+--------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | ALL    | PRIMARY,Slope | NULL    | NULL    | NULL          | 439340 |    25.79 | Using where; Using filesort |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY,Slope | PRIMARY | 4       | data.t1.pTime |      1 |   100.00 | Using where                 |
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY       | PRIMARY | 4       | data.t1.pTime |      1 |   100.00 | Using where                 |
+----+-------------+-------+--------+---------------+---------+---------+---------------+--------+----------+-----------------------------+


SELECT
    m.pTime AS OpenTime,
    CASE WHEN m.STD < 0 THEN 1 ELSE - 1 END AS Type,
  mu.pTime AS CloseTime;
FROM
    MainList m
LEFT JOIN b50d1 mu ON mu.pTime =(
    SELECT DISTINCT
        md.pTime
    FROM
        b50d1 md
    WHERE
        md.pTime > m.pTime
    AND md.pTime <= 1367700000
    AND SIGN(md.STD)!= SIGN(m.STD)
    AND ABS(md.STD)>= ABS(m.STD)
    ORDER BY
        md.pTime
    LIMIT 1
);

EXPLAIN EXTENDED:

+----+--------------------+-------+--------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+--------------------+-------+--------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY            | m     | ALL    | NULL          | NULL    | NULL    | NULL |     16 |   100.00 |             |
|  1 | PRIMARY            | mu    | eq_ref | PRIMARY       | PRIMARY | 4       | func |      1 |   100.00 | Using index |
|  2 | DEPENDENT SUBQUERY | md    | range  | PRIMARY       | PRIMARY | 4       | NULL | 439338 |   100.00 | Using where |
+----+--------------------+-------+--------+---------------+---------+---------+------+--------+----------+-------------+

, , . , type: ALL, EXPLAIN, , , , .

MYSQL , , . .

SQL CREATE TABLE INSERT, , , "": slowtables.SQL

my.ini - , ?

[client]
pipe
socket=mysql
[mysql]
default-character-set=latin1
[mysqld]
skip-networking
enable-named-pipe
socket=mysql
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
character-set-server=latin1
default-storage-engine=MYISAM
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=189M
table_cache=256
tmp_table_size=192M
key_buffer_size=594M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
+3
2

, ... ... "pTime", ... , XOR , * STD. , = ( , )...

, WHERE , , 2 3...

XOR, , . , ABS() . , , , ABS(). UNION ALL, , - , SQL . , XOR, AND std.

THEN, std

INSERT INTO MainList(pTime,STD)
SELECT STRAIGHT_JOIN
      t1.pTime,
      t1.STD
   FROM
      b50d1 AS t1
         JOIN b75d1 AS t2
            ON t1.pTime = t2.pTime
         JOIN b100d1 AS t3
            ON t1.pTime = t3.pTime
   where 
          t1.pTime >= 1104710000
      AND t1.pTime <= 1367700000
      AND t1.Slope >= 2.3169
      AND t1.Slope <= 7.0031
      AND t1.STD >= - 2.068
      AND t1.STD <= - 0.972
      AND t2.Slope >= 0.3179
      AND t2.Slope <= 5.7221
      AND t3.Slope >= 2.6466
      AND t3.Slope <= 35.7534
   ORDER BY
      t1.pTime
UNION ALL
SELECT STRAIGHT_JOIN
      t1.pTime,
      t1.STD
   FROM
      b50d1 AS t1
         JOIN b75d1 AS t2
            ON t1.pTime = t2.pTime
         JOIN b100d1 AS t3
            ON t1.pTime = t3.pTime
   where 
          t1.pTime >= 1104710000
      AND t1.pTime <= 1367700000
      AND t1.Slope >= - 7.0031
      AND t1.Slope <= - 2.3169
      AND t1.STD >=  0.972
      AND t1.STD <=  2.068
      AND t2.Slope >= - 5.7221
      AND t2.Slope <= - 0.3179
      AND t3.Slope >= - 35.7534
      AND t3.Slope <= - 2.6466;

, ... (, "PQ" PreQuery)

INSERT INTO MainList(pTime,STD)
SELECT STRAIGHT_JOIN
      pq.pTime,
      pq.STD
   FROM
      ( select
              t1.pTime,
              t1.slope,
              t1.std
           from 
              b50d1 t1
           where
                  t1.pTime >= 1104710000
              AND t1.pTime <= 1367700000
              AND (( t1.slope between 2.3169 and 7.0031
                    AND t1.std between -2.068 and -.972 )
                  OR 
                   ( t1.slope between -7.0031 and -2.3169  
                    AND t1.std between .972 and 2.068 )) ) PQ
         JOIN b75d1 AS t2
            ON p1.pTime = t2.pTime
         JOIN b100d1 AS t3
            ON p1.pTime = t3.pTime
   where 
          (     pq.slope > 0
            AND t2.Slope >= 0.3179
            AND t2.Slope <= 5.7221
            AND t3.Slope >= 2.6466
            AND t3.Slope <= 35.7534 
          )
       OR
          (     pq.slope > 0
            AND t2.Slope >= -5.7221
            AND t2.Slope <= -0.3179
            AND t3.Slope >= -35.7534 
            AND t3.Slope <= -2.6466
          )
   ORDER BY
      t1.pTime
+1

, MySQL ( ). DISTINCT LIMIT 1. ORDER BY LIMIT 1 , , . ( ?)

MySQL, -, OR UNION. , , UNION.

. .

+1

More articles:


All Articles