List:General Discussion« Previous MessageNext Message »
From:hsv Date:June 13 2013 2:57am
Subject:Re: How do I select all rows of table that have some rows in
another table (AND, not OR)
View as plain text  
>>>> 2013/06/11 12:59 -0700, Daevid Vincent >>>>
Also, just for S&G this is how we are currently implementing it, but we feel
the REGEXP is killing our queries and while "clever" is a bit hacky and
nullifies any indexes we have on the genres column as it requires a
file_sort table scan to compare substrings basically...

SELECT * FROM scene_all_genres WHERE scene_id = 17;

scene_id  genres                  
--------  ------------------------
      17  1|3|10|19|38|53|58|59|  

SELECT * FROM scene_all_genres WHERE scene_id = 11;

scene_id  genres             
--------  -------------------
      11  1|10|19|31|32|59|  
<<<<<<<<
Except that, it seems to me, it somehow reflects the reality of assigning attributes to
the scenes (movies?) that you catalog. In a way, it looks very much like using a
bitstring wherin each place stands for one attribute. If you then have also a bitstring
for each user s likes and one for rows (peeves), telling howmany 1s are at the same place
for the "genres" and liking (bit-AND, MySQL "&" followed by telling the number of 1s),
and same for the "genres" and the row or peeve yields a number howmany match for liking,
and how many match for becoming peeved. If the liking is enough greater than the becoming
peeved, the scene and the user match.

Unhappily, although this, using bitstring for set of attributes to match, is an old and
well understood topic, MySQL s support for bitstrings is poor, limited to integers (as C
is so limited)--that is, to 64 bits. If you have more, you have to use more "words".
There is, furthermore, no function for telling howmany 1s (or 0s) there are in an
integer.

Now, if, in a more perfect world, MySQL had bitstring, and, furthermore, MySQL s SET were
mapped onto bitstring, where it belongs, you could not only use bit operations (MySQL s
& | ^), but also name the bits as you like.

The problem with writing one s own bit-telling function is, of course, time, and hiding
useful information from the optimizer. In any case, here is a function for it, using an
old well worn trick that depends on binary arithmetic:

delimiter ?
create function bittell(B INTEGER) RETURNS INTEGER
DETERMINISTIC
NO SQL
COMMENT 'Howmany 1s in argument?'
begin
declare E integer;
SET E = 0;
WHILE B <> 0 DO
	set B = (B-1) & B, E = E + 1;
end WHILE;
RETURN E;
end ?
delimiter ;

If you stick with the character-string set, with a slight change in representation you can
use a simpler-looking pattern--not more efficient, if MySQL s implementation is good, but
of easier reading: separate the decimal numerals with a character that is neither a
decimal digit nor a REGEXP operator, and bound the whole string with it--comma or
semicolon (among others) are good.
       ',1,10,19,31,32,59,' REGEXP ',10,+.*,38,' is 0
       ',1,10,19,31,32,59,' REGEXP ',10,+.*,32,' is 1
(See also FIND_IN_SET.)

Somewhere I read that for lack of support bitstring has been withdrawn from the SQL
standard. This is such an obvious use; why is it not supported?

Thread
How do I select all rows of table that have some rows in another table (AND, not OR)Daevid Vincent11 Jun
  • Re: How do I select all rows of table that have some rows in anothertable (AND, not OR)shawn green11 Jun
    • RE: How do I select all rows of table that have some rows in another table (AND, not OR)Daevid Vincent11 Jun
      • RE: How do I select all rows of table that have some rows inanother table (AND, not OR)Rick James12 Jun
        • RE: How do I select all rows of table that have some rows in another table (AND, not OR)Daevid Vincent12 Jun
          • RE: How do I select all rows of table that have some rows inanother table (AND, not OR)Rick James12 Jun
      • Re: How do I select all rows of table that have some rows in anothertable (AND, not OR)shawn green12 Jun
  • Re: How do I select all rows of table that have some rows inanother table (AND, not OR)hsv13 Jun
RE: How do I select all rows of table that have some rows in another table (AND, not OR)Daevid Vincent12 Jun
Re: How do I select all rows of table that have some rows inanother table (AND, not OR)hsv13 Jun
RE: How do I select all rows of table that have some rows in another table (AND, not OR)hsv18 Jun