Re: Effective query for listing flags in use by messages in a folder

På tirsdag 17. mars 2015 kl. 14:31:28, skrev Hector Vass <
hector(dot)vass(at)metametrics(dot)co(dot)uk <mailto:hector(dot)vass(at)metametrics(dot)co(dot)uk>>: A couple
of ideas to try ... the approach you take will depend on volume of records, how
quickly/how much they change and the overheads of updating/maintaining this data

/** you could consider holding the flags as integers as you can do more stuff
with simple math **/   drop table if exists message; create table message(    
folder_id integer not NULL,     is_seen int not null default 0,     is_replied
int not null default 0,     is_forwarded int not null default 0,     is_deleted
int not null default 0,     is_draft int not null default 0,     is_flagged int
not null default 0 );  insert into message values (1,0,0,0,0,0,0),
(2,0,0,0,0,0,1), (3,0,0,0,0,1,1), (4,0,0,0,1,0,1) ; select folder_id from
message where is_seen+is_replied+is_forwarded+is_deleted+is_draft+is_flagged>0;
  /** of course holding flags as columns can be inefficient and not as flexible
as holding them as key value pairs **/ drop table if exists message; create
table message(     folder_id integer not NULL,     is_flag char(1),     is_val
int );  insert into message values
(1,'s',0),(1,'r',0),(1,'f',0),(1,'d',0),(1,'a',0),(1,'f',0),
(2,'s',0),(2,'r',0),(2,'f',0),(2,'d',0),(2,'a',0),(2,'f',1),
(3,'s',0),(3,'r',0),(3,'f',0),(3,'d',0),(3,'a',1),(3,'f',1),
(4,'s',0),(4,'r',0),(4,'f',0),(4,'d',1),(4,'a',0),(4,'f',1) ; select folder_id
from message where is_val>0 group by 1;   /** key value pairs can use a lot of
space as the folder_id has to be repeated ... **/ /** so why bother holding
value of Zero at all just hold those with a flag**/ /** key value approach has
advantages as you simply add flags so no update or delete operations **/ delete
from message where is_val=0; select folder_id from message group by 1;   /**
Commonly the bottleneck comes down to the speed at which you can store/update
the flags sql not the best at generating bit map fields but you can or you can
consider using a stored procedure in C ... then this would be my preferred
approach **/   drop table if exists message; create table message(    
folder_id integer not NULL,     is_flag int ); insert into message values (1,0),
(2,1), (3,3), (4,5) ; select folder_id from message where is_flag>0; select
folder_id,(is_flag::bit(7))::char(7) from message where is_flag>0; --or can do
bit level operations which are v fast select 'deleted flag set',folder_id from
message where is_flag&4>0; select 'deleted flag set or seen draft set
',folder_id from message where is_flag&6>0;   /** if you can compress the data
into bit field you may not need to bother with overhead of indexes as scanning
whole table or partition can be very fast **/     Thanks for your comments.   I
don't see how any of your suggestions help with listing flagsin use in a
folder. My example-query lists a distinct set of flags in use in a folder.   --
Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com> www.visena.com
<https://www.visena.com> <https://www.visena.com>