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 joinConcVal
toCVBits
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
.