2

I have question very unusual for databases. I’m not a good English speaker, but good English reader :) so for explanation I’ll give you examples.

Part1

In MySQL, I have two tables with relations that I couldn't perform like primary-foreign key. Forward speaking, this is some "one to many" relation. This is examples of tables:

#1 – States
ID      ConcVal    Type     Time
1        14        1        05:30
2        13        1        06:30
3        10        2        07:00
4        13        2        08:50
5        10        2        09:00
6        13        2        09:40

ConcVal is less or equal 15 (in binary 1111) (actually I have 32 bit or even might more in future, but for simplify question it's only 4 bit). ConcVal - is something like superposition of Descriptions in binary. So ConcVal value may be the same for all Types, but meaning, that is described in second table, is different.

#2 - Descriptions
ID  Type    CVbits  Description
1   1       0       Some text for `type` 1
2   1       1       Some other text
3   1       2       Some other text
4   1       3       Some last text for type 1
5   2       0       Some text for type 2
…
8   2       3       Some last text for `type` 2

The relation between data makes by binary representation of ConcVal value: all bits that are “1” (excluding zero bit) relate to corteges of #2 by column CVbits and Type. Type in #1 and in #2 - the same thing. So:

ConcVal = 14 (1110) relates to corteges with CVbits {1;2;3};

ConcVal = 13 (1101) >> CVbits: {2;3};

ConcVal = 12 (1100) >> CVbits: {2;3}.

If talking about tables, the next one imaginary (actually, I did it) join can help understand my needs:

StateID     Type    ConcVal     (bin)       CVbits  DescrID
    1       1       14          (1110)      1       1
    1       1       14          (1110)      2       2
    1       1       14          (1110)      3       3
    …
    6       2       13          (1100)      2       6
    …
    6       2       13          (1100)      3       8

Part2 (very small)

I know that logic described above better implement in front-end, but I want test how much can provide DBMS, so I wrote UDF, called getCVbits, which return string (call this bitorderstring) contained position numbers of bits in ConcVal, something like that “,1,2,”, and query left join with clause “where getCVbits (a.ConcVal) LIKE concat(‘%,’b.CVbits,’,%’)”. So, as you purpose, that query execute too long. Something near 1,2~2,0 sec for 300~3000 rows. I guess, UDF is too slow. But if you want, I think I can give you real query.

Part3

What if I will write insert and update triggers which are used getCVbits and calculate needed string. This string I can store in #1. That allows querying much faster, but goes against data normalization.

Question:

Q1. Is storing computed bitorderstring are good way or maybe let user wait a little? =) (I guess, result set will be much more than I tested)

Q2. How can I join States to Descriptions without LIKE operator in case using getCVbits? Actually, I can return from UDF anything I need.

Q3. Are another ways to implement “relation” between tables... data?

UPDATED: So, like I promised, the results of 2 queries:

SELECT
    b.ID AS StatusID,
    b.Type as Type,
    b.CVTime AS CVTime,
    b.ConcVal AS ConcVal,
    CONV(b.ConcVal, 10, 2) AS BinStr,
    a.ID AS DescrID,
    a.CVbits AS CVbits,
    a.Description AS Description
FROM
    (Reasons a
    JOIN States b ON ((a.Type = b.Type)))

and #1

WHERE
    (a.CVbits <> 0)
        AND (a.CVbits <> 8)
        AND (a.CVbits <> 16)
        AND (1 << a.CVbits) & b.ConcVal

and #2

Like suggested Rick James:

An easier (an faster at run-time) solution would be make CVBits an integer and store the binary-equivalent value (1, 2, 4, 8, 16) then join ConcVal to CVBits using a binary AND operator.

WHERE
    (a.CVbits <> 0)
        AND (a.CVbits <> 8)
        AND (a.CVbits <> 16)
        AND (a.CV & b.ConcVal)

Not so different at ~4280 rows returned (duration/fetch):

#1 (1<<CVbits & ConcVal)    #2 (CV & ConcVal)
0.016 sec / 0.421 sec       0.015 sec / 0.687 sec
0.016 sec / 0.156 sec       0.000 sec / 0.296 sec
0.031 sec / 0.234 sec       0.016 sec / 0.171 sec
0.016 sec / 0.218 sec       0.016 sec / 0.359 sec
0.016 sec / 0.172 sec       0.016 sec / 0.125 sec

instead (1.794 sec / 78.235) sec when i use UDF and LIKE.

1
WHERE (1 << CVbits) & ConcVal
  AND CVbits != 0    -- (to "exclude the zero bit")
  • thanks a lot, man. I guess it's what I need. I will test at work – brz Apr 8 '15 at 23:05
  • all my troubles seems so far away=). It works with little change: 1 << (4-CVbits) goes to 1 << CVbits. Thanks you very much. Would you edit answer to choose it as Answered? – brz Apr 9 '15 at 6:13
  • Hmmm... Now for a time machine to go back and figure out why I did the expression wrong. :( – Rick James Apr 9 '15 at 15:18
  • for zero bit not necessary add ' CVbit != 0' if no values in #2 table. Thanks again. – brz Apr 9 '15 at 15:38
1

Q1. Is storing computed bitorderstring are good way or maybe let user wait a little? =) (I guess, result set will be much more than I tested)

MySQL supports columns of type bit. These only use one bit each, in sets of 8, as you would expect. This will give the same disk and memory density as you're currently getting, but with much easier query writing.

Q2. How can I join States to Descriptions without LIKE operator in case using getCVbits? Actually, I can return from UDF anything I need.

@Rick Has it: you need to do binary maths on tblDescriptions.CVbits. An easier (an faster at run-time) solution would be make CVBits an integer and store the binary-equivalent value (1, 2, 4, 8, 16) then join ConcVal to CVBits using a binary AND operator.

Q3. Are another ways to implement “relation” between tables... data?

Yes. Normalise your design. ConcVal breaks first normal form because it holds many values. Create a new table which contains only tblStates.ID and tblDescriptions.ID.

And please, please stop prefixing your table names with 'tbl'.

  • Thanks for response. Storing bitorderstring i needed just to join with LIKE in my previous "solution". Now, it's not necessary. About normalization - I agree with you, but I didn't say that ConcVal is raw input and user need to pick up right bit, which will store in another table. So, i guess, my Q3 not actually almost right. An integer that store binary-equivalent value makes sense! I guess, it's second solution. I'll test perfomance and post here. – brz Apr 9 '15 at 6:33
  • @brz I suggest you also test the last suggestion ("Normalize ...") – ypercubeᵀᴹ Apr 9 '15 at 16:35
  • @ypercube i did tests, but was not so much records so no so big difference between queries – brz Apr 10 '15 at 11:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.