Skip to content

Instantly share code, notes, and snippets.

@ms-tg

ms-tg/mysql_in_list_vs_temp_tbl.sql

Created February 14, 2013 20:55
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ms-tg/4956336 to your computer and use it in GitHub Desktop.
Save ms-tg/4956336 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
mysql_in_list_vs_temp_tbl.sql
CREATE TABLE filler (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
) ENGINE=Memory;
CREATE TABLE t_param (
param INT NOT NULL PRIMARY KEY
) ENGINE=Memory;
CREATE TABLE t_source (
id INT NOT NULL PRIMARY KEY,
data VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
DELIMITER $$
CREATE PROCEDURE prc_filler(cnt INT)
BEGIN
DECLARE _cnt INT;
SET _cnt = 1;
WHILE _cnt <= cnt DO
INSERT
INTO filler
SELECT _cnt;
SET _cnt = _cnt + 1;
END WHILE;
END
$$
CREATE PROCEDURE prc_temporary(cnt INT)
BEGIN
DECLARE _cnt INT;
DECLARE _out INT;
SET _cnt = 1;
WHILE _cnt <= cnt DO
TRUNCATE TABLE t_param;
PREPARE STMT FROM
'
INSERT
INTO t_param (param)
VALUES (50000), (49900), (49800), (49700), (49600), (49500), (49400), (49300), (49200), (49100), (49000), (48900), (48800), (48700), (48600), (48500), (48400), (48300), (48200), (48100), (48000), (47900), (47800), (47700), (47600), (47500), (47400), (47300), (47200), (47100), (47000), (46900), (46800), (46700), (46600), (46500), (46400), (46300), (46200), (46100), (46000), (45900), (45800), (45700), (45600), (45500), (45400), (45300), (45200), (45100), (45000), (44900), (44800), (44700), (44600), (44500), (44400), (44300), (44200), (44100), (44000), (43900), (43800), (43700), (43600), (43500), (43400), (43300), (43200), (43100), (43000), (42900), (42800), (42700), (42600), (42500), (42400), (42300), (42200), (42100), (42000), (41900), (41800), (41700), (41600), (41500), (41400), (41300), (41200), (41100), (41000), (40900), (40800), (40700), (40600), (40500), (40400), (40300), (40200), (40100), (40000), (39900), (39800), (39700), (39600), (39500), (39400), (39300), (39200), (39100), (39000), (38900), (38800), (38700), (38600), (38500), (38400), (38300), (38200), (38100), (38000), (37900), (37800), (37700), (37600), (37500), (37400), (37300), (37200), (37100), (37000), (36900), (36800), (36700), (36600), (36500), (36400), (36300), (36200), (36100), (36000), (35900), (35800), (35700), (35600), (35500), (35400), (35300), (35200), (35100), (35000), (34900), (34800), (34700), (34600), (34500), (34400), (34300), (34200), (34100), (34000), (33900), (33800), (33700), (33600), (33500), (33400), (33300), (33200), (33100), (33000), (32900), (32800), (32700), (32600), (32500), (32400), (32300), (32200), (32100), (32000), (31900), (31800), (31700), (31600), (31500), (31400), (31300), (31200), (31100), (31000), (30900), (30800), (30700), (30600), (30500), (30400), (30300), (30200), (30100), (30000), (29900), (29800), (29700), (29600), (29500), (29400), (29300), (29200), (29100), (29000), (28900), (28800), (28700), (28600), (28500), (28400), (28300), (28200), (28100), (28000), (27900), (27800), (27700), (27600), (27500), (27400), (27300), (27200), (27100), (27000), (26900), (26800), (26700), (26600), (26500), (26400), (26300), (26200), (26100), (26000), (25900), (25800), (25700), (25600), (25500), (25400), (25300), (25200), (25100), (25000), (24900), (24800), (24700), (24600), (24500), (24400), (24300), (24200), (24100), (24000), (23900), (23800), (23700), (23600), (23500), (23400), (23300), (23200), (23100), (23000), (22900), (22800), (22700), (22600), (22500), (22400), (22300), (22200), (22100), (22000), (21900), (21800), (21700), (21600), (21500), (21400), (21300), (21200), (21100), (21000), (20900), (20800), (20700), (20600), (20500), (20400), (20300), (20200), (20100), (20000), (19900), (19800), (19700), (19600), (19500), (19400), (19300), (19200), (19100), (19000), (18900), (18800), (18700), (18600), (18500), (18400), (18300), (18200), (18100), (18000), (17900), (17800), (17700), (17600), (17500), (17400), (17300), (17200), (17100), (17000), (16900), (16800), (16700), (16600), (16500), (16400), (16300), (16200), (16100), (16000), (15900), (15800), (15700), (15600), (15500), (15400), (15300), (15200), (15100), (15000), (14900), (14800), (14700), (14600), (14500), (14400), (14300), (14200), (14100), (14000), (13900), (13800), (13700), (13600), (13500), (13400), (13300), (13200), (13100), (13000), (12900), (12800), (12700), (12600), (12500), (12400), (12300), (12200), (12100), (12000), (11900), (11800), (11700), (11600), (11500), (11400), (11300), (11200), (11100), (11000), (10900), (10800), (10700), (10600), (10500), (10400), (10300), (10200), (10100), (10000), (9900), (9800), (9700), (9600), (9500), (9400), (9300), (9200), (9100), (9000), (8900), (8800), (8700), (8600), (8500), (8400), (8300), (8200), (8100), (8000), (7900), (7800), (7700), (7600), (7500), (7400), (7300), (7200), (7100), (7000), (6900), (6800), (6700), (6600), (6500), (6400), (6300), (6200), (6100), (6000), (5900), (5800), (5700), (5600), (5500), (5400), (5300), (5200), (5100), (5000), (4900), (4800), (4700), (4600), (4500), (4400), (4300), (4200), (4100), (4000), (3900), (3800), (3700), (3600), (3500), (3400), (3300), (3200), (3100), (3000), (2900), (2800), (2700), (2600), (2500), (2400), (2300), (2200), (2100), (2000), (1900), (1800), (1700), (1600), (1500), (1400), (1300), (1200), (1100), (1000), (900), (800), (700), (600), (500), (400), (300), (200), (100)
';
EXECUTE STMT;
SELECT SUM(id)
INTO _out
FROM t_param p
JOIN t_source s
ON s.id = p.param;
DEALLOCATE PREPARE STMT;
SET _cnt = _cnt + 1;
END WHILE;
SELECT _cnt;
END;
$$
CREATE PROCEDURE prc_list(cnt INT)
BEGIN
DECLARE _cnt INT;
DECLARE _out INT;
SET _cnt = 1;
WHILE _cnt <= cnt DO
PREPARE STMT FROM
'
SELECT SUM(id)
INTO @out
FROM t_source
WHERE id IN (50000, 49900, 49800, 49700, 49600, 49500, 49400, 49300, 49200, 49100, 49000, 48900, 48800, 48700, 48600, 48500, 48400, 48300, 48200, 48100, 48000, 47900, 47800, 47700, 47600, 47500, 47400, 47300, 47200, 47100, 47000, 46900, 46800, 46700, 46600, 46500, 46400, 46300, 46200, 46100, 46000, 45900, 45800, 45700, 45600, 45500, 45400, 45300, 45200, 45100, 45000, 44900, 44800, 44700, 44600, 44500, 44400, 44300, 44200, 44100, 44000, 43900, 43800, 43700, 43600, 43500, 43400, 43300, 43200, 43100, 43000, 42900, 42800, 42700, 42600, 42500, 42400, 42300, 42200, 42100, 42000, 41900, 41800, 41700, 41600, 41500, 41400, 41300, 41200, 41100, 41000, 40900, 40800, 40700, 40600, 40500, 40400, 40300, 40200, 40100, 40000, 39900, 39800, 39700, 39600, 39500, 39400, 39300, 39200, 39100, 39000, 38900, 38800, 38700, 38600, 38500, 38400, 38300, 38200, 38100, 38000, 37900, 37800, 37700, 37600, 37500, 37400, 37300, 37200, 37100, 37000, 36900, 36800, 36700, 36600, 36500, 36400, 36300, 36200, 36100, 36000, 35900, 35800, 35700, 35600, 35500, 35400, 35300, 35200, 35100, 35000, 34900, 34800, 34700, 34600, 34500, 34400, 34300, 34200, 34100, 34000, 33900, 33800, 33700, 33600, 33500, 33400, 33300, 33200, 33100, 33000, 32900, 32800, 32700, 32600, 32500, 32400, 32300, 32200, 32100, 32000, 31900, 31800, 31700, 31600, 31500, 31400, 31300, 31200, 31100, 31000, 30900, 30800, 30700, 30600, 30500, 30400, 30300, 30200, 30100, 30000, 29900, 29800, 29700, 29600, 29500, 29400, 29300, 29200, 29100, 29000, 28900, 28800, 28700, 28600, 28500, 28400, 28300, 28200, 28100, 28000, 27900, 27800, 27700, 27600, 27500, 27400, 27300, 27200, 27100, 27000, 26900, 26800, 26700, 26600, 26500, 26400, 26300, 26200, 26100, 26000, 25900, 25800, 25700, 25600, 25500, 25400, 25300, 25200, 25100, 25000, 24900, 24800, 24700, 24600, 24500, 24400, 24300, 24200, 24100, 24000, 23900, 23800, 23700, 23600, 23500, 23400, 23300, 23200, 23100, 23000, 22900, 22800, 22700, 22600, 22500, 22400, 22300, 22200, 22100, 22000, 21900, 21800, 21700, 21600, 21500, 21400, 21300, 21200, 21100, 21000, 20900, 20800, 20700, 20600, 20500, 20400, 20300, 20200, 20100, 20000, 19900, 19800, 19700, 19600, 19500, 19400, 19300, 19200, 19100, 19000, 18900, 18800, 18700, 18600, 18500, 18400, 18300, 18200, 18100, 18000, 17900, 17800, 17700, 17600, 17500, 17400, 17300, 17200, 17100, 17000, 16900, 16800, 16700, 16600, 16500, 16400, 16300, 16200, 16100, 16000, 15900, 15800, 15700, 15600, 15500, 15400, 15300, 15200, 15100, 15000, 14900, 14800, 14700, 14600, 14500, 14400, 14300, 14200, 14100, 14000, 13900, 13800, 13700, 13600, 13500, 13400, 13300, 13200, 13100, 13000, 12900, 12800, 12700, 12600, 12500, 12400, 12300, 12200, 12100, 12000, 11900, 11800, 11700, 11600, 11500, 11400, 11300, 11200, 11100, 11000, 10900, 10800, 10700, 10600, 10500, 10400, 10300, 10200, 10100, 10000, 9900, 9800, 9700, 9600, 9500, 9400, 9300, 9200, 9100, 9000, 8900, 8800, 8700, 8600, 8500, 8400, 8300, 8200, 8100, 8000, 7900, 7800, 7700, 7600, 7500, 7400, 7300, 7200, 7100, 7000, 6900, 6800, 6700, 6600, 6500, 6400, 6300, 6200, 6100, 6000, 5900, 5800, 5700, 5600, 5500, 5400, 5300, 5200, 5100, 5000, 4900, 4800, 4700, 4600, 4500, 4400, 4300, 4200, 4100, 4000, 3900, 3800, 3700, 3600, 3500, 3400, 3300, 3200, 3100, 3000, 2900, 2800, 2700, 2600, 2500, 2400, 2300, 2200, 2100, 2000, 1900, 1800, 1700, 1600, 1500, 1400, 1300, 1200, 1100, 1000, 900, 800, 700, 600, 500, 400, 300, 200, 100)
';
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
SET _cnt = _cnt + 1;
END WHILE;
SELECT _cnt;
END;
$$
DELIMITER ;
START TRANSACTION;
CALL prc_filler(50000);
COMMIT;
INSERT
INTO t_source
SELECT id, CONCAT('Value ', id)
FROM filler;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment