GROUP_CONCAT is very slow. So I used FastBit for the table instead! 43 seconds down to .16!

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_Category:text,P_Brand:text,P_Colour:text,P_Type:text,P_Size:text,P_Container:text');

-- this loads the table, which is one time only
select fb_insert2('/var/lib/fastbit/part',P_PartKey, P_Name, P_MFGR, P_Category, P_Brand, P_Colour, P_Type, P_Size, P_Container) from part group by 1;
Query OK, 1 row affected (15 seconds)

-- create the same CSV list using fb_inlist():
select fastbit.fb_inlist('/var/lib/fastbit/part',"select P_PartKey where P_MFGR='MFGR#1' or P_MFGR='MFGR#2'") into @plist;
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:

https://github.com/greenlion/FastBit_UDF