MySQL always tries to make toast of my good ideas. This is the only time is succeeds in making toast (see bug #2). |
This time I'm working on star schema optimzation (which will be my next blog post). MySQL takes about .24 seconds to project out a list of parts that match a filter, but it takes 43 (not .43, FORTY-THREE) seconds to turn it into a CSV list using GROUP_CONCAT.
select P_PartKey from part where where P_MFGR = 'MFGR#1' or P_MFGR='MFGR#2';
399597 rows in set (0.24 sec)
mysql> select group_concat(P_PartKey) into @pkey from part where P_MFGR = 'MFGR#1' or P_MFGR='MFGR#2';
Query OK, 1 row affected (43.25 sec)
So I decided to just stick the part table into FastBit instead (using my FastBit_UDF tools):
fb_create('/var/lib/fastbit/part', 'P_PartKey:int,P_Name:text,P_MFGR:text,P
-- this loads the table, which is one time only
select fb_insert2('/var/lib/fastbit/part',P_Par
Query OK, 1 row affected (15 seconds)
-- create the same CSV list using fb_inlist():
select fastbit.fb_inlist('/var/lib/fastbit/part'
Query OK, 1 row affected (0.16 sec)
I think .16 seconds is a lot better than 43! :D
You can find the Fastbit_UDF tools here: