࣮ߦܭը CREATE TABLE `demo`.`users` ( `id` INT NOT NULL AUTO_INCREMENT COMMENT '', `name` VARCHAR(45) NOT NULL COMMENT '', `age` INT NOT NULL COMMENT '', `created` DATETIME NOT NULL DEFAULT NOW () COMMENT '', PRIMARY KEY (`id`) COMMENT '' ); ——શ݅Λબ͢ΔͷͰςʔϒϧεΩϟϯ SELECT * FROM demo.users; ——PRIMARY KEYʢINDEXʣΛར༻ͨ͠ྫ SELECT * FROM demo.users WHERE id > 100; ——INDEX͕ແ͍ͷͰςʔϒϧεΩϟϯ SELECT * FROM demo.users WHERE age > 20;
αϒΫΤϦ —— INDEXͱͯ͠PRIMARY KEY͕ޮ͍͍ͯΔ SELECT * FROM users WHERE id < 1000 AND id > 800 —— 1ճ͔࣮͠ߦ͞Εͳ͍ SELECT * FROM (SELECT * FROM users WHERE id < 1000 AND id > 800) AS dummy
૬ؔαϒΫΤϦ ——INDEX͕ར༻Ͱ͖Ε૬ؔαϒΫΤϦͰ͍ SELECT * FROM users WHERE created = '2016-02-27 04:31:32' AND id IN (SELECT id FROM users WHERE id BETWEEN 10 AND 100000)
ෳར༻ͨ͠INDEX CREATE TABLE public.users ( id integer NOT NULL DEFAULT nextval('users_id_seq'::regclass), name text NOT NULL, age integer NOT NULL, created timestamp without time zone NOT NULL DEFAULT now(), CONSTRAINT users_pkey PRIMARY KEY (id)); CREATE INDEX users_created_idx ON public.users USING tree (created); ——idͱcreatedͷINDEXΛར༻͢Δ SELECT * FROM users WHERE id < 100 AND created < '2016-02-27 05:41:28';
૬ؔαϒΫΤϦ ——σϞͰINDEXΛͬͯ͘Εͳ͍ͷͰINDEXΛ༏ઌతʹར༻͢ΔΑ͏ʹࢦఆ SET ENABLE_SEQSCAN=OFF; SELECT * FROM users WHERE id IN (SELECT id FROM users WHERE id BETWEEN 10 AND 100000)
࣮ફͰϋϚΔ᠘ w ूܭର͕େ͖͘ͳͬͯूܭ࣌ʹϝϞϦʹΒͳ͍ ˠ5FNQPSBSZΛ%JTLʹు͘ͷͰॏ͘ͳΔ w */%&9͕ແ͘5"#-&εΩϟϯΛ͍ͯͨ͠ ˠςετ࣌σʔλ͕খ͔ͬͨ͞ͷͰʹؾ͔ͮͳ͔ͬͨ w ϓϩάϥϜ͕ϧʔϓͷ͚ͩΫΤϦΛ͍͛ͯͨ ˠߦ͕૿͑Δͱϧʔϓճ͕૿͑ͯॏ͘ͳΔ w ߋ৽͕ܹ͘͠*/%&9ͷஅยԽ͕ൃੜͨ͠ ˠ61%"5&%&-&5&ʹΑͬͯ*/%&9͕அยԽͯ͘͠ͳΔ