I have the following code in my stored procedure, which basically joins the SQL text into one big query in while loop, then executes it. I have 7 UNION ALL statements from 7 different tables with some input parameters (table_name1...7, start_date, end_date):
BEGIN
DECLARE v_max INT UNSIGNED DEFAULT 58;
DECLARE v_counter INT UNSIGNED DEFAULT 1;
SET @sql_text = '';
WHILE v_counter < v_max DO
-- Build SQL string
SET @sql_text = CONCAT(@sql_text,
CONCAT(' SELECT
(SELECT i.name FROM instrument i WHERE i.id =',v_counter,' LIMIT 1) AS Instrument,
(SELECT AVG(p.ask-p.bid) AS avg_spread
FROM ',table_name1,' p
WHERE p.instrument_id=',v_counter,' AND FROM_UNIXTIME(p.system_datetime/1000000) BETWEEN '',start_date,'' AND '',end_date,'') AS One
,
(SELECT AVG(p.ask-p.bid) AS avg_spread
FROM ', table_name2,' p
WHERE p.instrument_id=',v_counter,' AND FROM_UNIXTIME(p.system_datetime/1000000) BETWEEN '',start_date,'' AND '', end_date,'') AS Two
,
(SELECT AVG(p.ask-p.bid) AS avg_spread
FROM ',table_name3,' p
WHERE p.instrument_id=',v_counter,' AND FROM_UNIXTIME(p.system_datetime/1000000) BETWEEN '', start_date,'' AND '',end_date,'') AS Three
/* ... more unions here ...*/
,
(SELECT AVG(p.ask-p.bid) AS avg_spread
FROM ',table_name7,' p
WHERE p.instrument_id=',v_counter,' AND FROM_UNIXTIME(p.system_datetime/1000000) BETWEEN '', start_date,'' AND '',end_date,'') AS Seven UNION ALL ')
);
SET v_counter=v_counter+1;
END WHILE;
-- Remove extra UNION ALL from SQL string
SET @sql_text = LEFT(@sql_text,LENGTH(@sql_text) - LENGTH(' UNION ALL'));
-- SELECT LEFT(@sql_text,LENGTH(@sql_text) - LENGTH(' UNION ALL'));
-- Prepare and execute statement
PREPARE stmt3 FROM @sql_text;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;
END
I wonder is there any way to optimise this query (the seven tables contain on average 3.5 million records each), the execution time is between 6 and 25 minutes? Tables are MyISAM, identical, here is their structure:
CREATE TABLE `prices_all_2016_06_13_5` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`instrument_id` TINYINT(4) NOT NULL,
`ask` DECIMAL(12,6) NOT NULL,
`bid` DECIMAL(12,6) NOT NULL,
`system_datetime` DECIMAL(20,0) NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `Index 3` (`instrument_id`) USING HASH,
INDEX `Index 4` (`system_datetime`)
)
COLLATE='utf8_unicode_ci'
ENGINE=MyISAM
ROW_FORMAT=COMPACT
AUTO_INCREMENT=3882184
;
EDIT: This is the EXPLAIN:
"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "PRIMARY" N N N N N N N "No tables used"
"9" "SUBQUERY" N N N N N N N "Impossible WHERE noticed after reading const tables"
"8" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "64566" "Using where"
"7" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "71061" "Using where"
"6" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "43979" "Using where"
"5" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "69852" "Using where"
"4" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "117355" "Using where"
"3" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "70672" "Using where"
"2" "SUBQUERY" "i" "const" "PRIMARY" "PRIMARY" "4" "const" "1" N
"10" "UNION" N N N N N N N "No tables used"
"18" "SUBQUERY" N N N N N N N "Impossible WHERE noticed after reading const tables"
"17" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "52505" "Using where"
"16" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "68781" "Using where"
"15" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "40055" "Using where"
"14" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "32054" "Using where"
"13" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "57465" "Using where"
"12" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "33265" "Using where"
"11" "SUBQUERY" "i" "const" "PRIMARY" "PRIMARY" "4" "const" "1" N
"19" "UNION" N N N N N N N "No tables used"
"27" "SUBQUERY" N N N N N N N "Impossible WHERE noticed after reading const tables"
"26" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "75411" "Using where"
"25" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "91574" "Using where"
"24" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "47560" "Using where"
"23" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "80731" "Using where"
"22" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "139225" "Using where"
"21" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "109131" "Using where"
"20" "SUBQUERY" "i" "const" "PRIMARY" "PRIMARY" "4" "const" "1" N
"28" "UNION" N N N N N N N "No tables used"
"36" "SUBQUERY" N N N N N N N "Impossible WHERE noticed after reading const tables"
"35" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "67288" "Using where"
"34" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "83522" "Using where"
"33" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "46650" "Using where"
"32" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "50401" "Using where"
"31" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "73280" "Using where"
"30" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "50836" "Using where"
"29" "SUBQUERY" "i" "const" "PRIMARY" "PRIMARY" "4" "const" "1" N
"37" "UNION" N N N N N N N "No tables used"
"45" "SUBQUERY" N N N N N N N "Impossible WHERE noticed after reading const tables"
"44" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "59256" "Using where"
"43" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "54092" "Using where"
"42" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "41561" "Using where"
"41" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "51928" "Using where"
"40" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "64368" "Using where"
"39" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "60471" "Using where"
"38" "SUBQUERY" "i" "const" "PRIMARY" "PRIMARY" "4" "const" "1" N
"46" "UNION" N N N N N N N "No tables used"
"54" "SUBQUERY" N N N N N N N "Impossible WHERE noticed after reading const tables"
"53" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "59213" "Using where"
"52" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "56071" "Using where"
"51" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "47653" "Using where"
"50" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "46033" "Using where"
"49" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "56180" "Using where"
"48" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "47262" "Using where"
"47" "SUBQUERY" "i" "const" "PRIMARY" "PRIMARY" "4" "const" "1" N
"55" "UNION" N N N N N N N "No tables used"
"63" "SUBQUERY" N N N N N N N "Impossible WHERE noticed after reading const tables"
"62" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "69552" "Using where"
"61" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "100487" "Using where"
"60" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "78156" "Using where"
"59" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "105721" "Using where"
"58" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "128156" "Using where"
"57" "SUBQUERY" "p" "ref" "Index 3" "Index 3" "1" "const" "114533" "Using where"
"56" "SUBQUERY" "i" "const" "PRIMARY" "PRIMARY" "4" "const" "1" ...
Aucun commentaire:
Enregistrer un commentaire