|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?