�ݺ�ߣ

#JAB14 - @EliAschkenasy �C Integral DB Design

?
?
?
?
?
?
?

? WHO AM I ?
?
?
?
?
?
?

MySQL Integral DB Design #JAB14

Root Node
1
2
3
4 5
lft rgt
1 36
2 15
3 8
4 5
6 7
9 14
10 11
12 13
16 21
17 18
19 20
22 35
23 28
24 25
26 27
29 34
30 31
32 33
6 7
8 9
1
0
1
1
1
2
1
3
1
4
1
5
1
6
1
7
1
8
1
9
2
0
2
1
2
2
3
5
2
3
2
8
2
4
2
5
2
6
2
7
2
9
3
4
3
0
3
2
3
1
3
3
3
6

Root Node
1
2
3
4 5
lft rgt
1 36
2 15
3 8
4 5
6 7
9 14
10 11
12 13
16 21
17 18
19 20
22 35
23 28
24 25
26 27
29 34
30 31
32 33
6 7
8 9
1
0
1
1
1
2
1
3
1
4
1
5
1
6
1
7
1
8
1
9
2
0
2
1
2
2
3
5
2
3
2
8
2
4
2
5
2
6
2
7
2
9
3
4
3
0
3
2
3
1
3
3
3
6
ROOT NODE
root = lft == 1

Root Node
1
2
3
4 5
lft rgt
1 36
2 15
3 8
4 5
6 7
9 14
10 11
12 13
16 21
17 18
19 20
22 35
23 28
24 25
26 27
29 34
30 31
32 33
6 7
8 9
1
0
1
1
1
2
1
3
1
4
1
5
1
6
1
7
1
8
1
9
2
0
2
1
2
2
3
5
2
3
2
8
2
4
2
5
2
6
2
7
2
9
3
4
3
0
3
2
3
1
3
3
3
6
TOTAL NODES
(rgt �C lft + 1) / 2 = total
(36 �C 1 + 1) / 2 = 18

Root Node
1
2
3
4 5
lft rgt
1 36
2 15
3 8
4 5
6 7
9 14
10 11
12 13
16 21
17 18
19 20
22 35
23 28
24 25
26 27
29 34
30 31
32 33
6 7
8 9
1
0
1
1
1
2
1
3
1
4
1
5
1
6
1
7
1
8
1
9
2
0
2
1
2
2
3
5
2
3
2
8
2
4
2
5
2
6
2
7
2
9
3
4
3
0
3
2
3
1
3
3
3
6
IS LEAF NODE?
leaf = (rgt �C lft ==1) ? true : false;
true = (5 �C 4 == 1)
false = (8 �C 3 == 1)

Root Node
1
2
3
4 5
lft rgt
1 36
2 15
3 8
4 5
6 7
9 14
10 11
12 13
16 21
17 18
19 20
22 35
23 28
24 25
26 27
29 34
30 31
32 33
6 7
8 9
1
0
1
1
1
2
1
3
1
4
1
5
1
6
1
7
1
8
1
9
2
0
2
1
2
2
3
5
2
3
2
8
2
4
2
5
2
6
2
7
2
9
3
4
3
0
3
2
3
1
3
3
3
6
LEAF NODE OPTIMIZATION
Optimized implementation
SELECT x, x, x
FROM #__
WHERE lft = (rgt -1)

Root Node
1
2
3
4 5
lft rgt
1 36
2 15
3 8
4 5
6 7
9 14
10 11
12 13
16 21
17 18
19 20
22 35
23 28
24 25
26 27
29 34
30 31
32 33
6 7
8 9
1
0
1
1
1
2
1
3
1
4
1
5
1
6
1
7
1
8
1
9
2
0
2
1
2
2
3
5
2
3
2
8
2
4
2
5
2
6
2
7
2
9
3
4
3
0
3
2
3
1
3
3
3
6
SUBTREE SELECTION OPTIMIZATION
SELECT c.type AS choices, b.type AS
bottom
FROM #__ AS c, #__ AS b
WHERE c.lft BETWEEN b.lft AND b.rgt;
Anything between 22 and 35

Root Node
1
2
3
4 5
lft rgt
1 36
2 15
3 8
4 5
6 7
9 14
10 11
12 13
16 21
17 18
19 20
22 35
23 28
24 25
26 27
29 34
30 31
32 33
6 7
8 9
1
0
1
1
1
2
1
3
1
4
1
5
1
6
1
7
1
8
1
9
2
0
2
1
2
2
3
5
2
3
2
8
2
4
2
5
2
6
2
7
2
9
3
4
3
0
3
2
3
1
3
3
3
6
SUBTREE SELECTION ONLY SUBTREE
Optimized implementation
SELECT c.type AS choices, b.type AS
bottom
FROM #__ AS c, #__ AS b
WHERE c.lft BETWEEN (b.lft+1) AND b.rgt;
Anyone between 23and 35

Root Node
1
2
3
4 5
lft rgt
1 36
2 15
3 8
4 5
6 7
9 14
10 11
12 13
16 21
17 18
19 20
22 35
23 28
24 25
26 27
29 34
30 31
32 33
6 7
8 9
1
0
1
1
1
2
1
3
1
4
1
5
1
6
1
7
1
8
1
9
2
0
2
1
2
2
3
5
2
3
2
8
2
4
2
5
2
6
2
7
2
9
3
4
3
0
3
2
3
1
3
3
3
6
PATH TO A NODE (breadcrumb)
SELECT alias
FROM #__
WHERE lft < 4 AND rgt > 5
ORDER BY lft ASC;

Root Node
1
2
3
4 5
lft rgt
1 36
2 15
3 8
4 5
6 7
9 14
10 11
12 13
16 21
17 18
19 20
22 35
23 28
24 25
26 27
29 34
30 31
32 33
6 7
8 9
1
0
1
1
1
2
1
3
1
4
1
5
1
6
1
7
1
8
1
9
2
0
2
1
2
2
3
5
2
3
2
8
2
4
2
5
2
6
2
7
2
9
3
4
3
0
3
2
3
1
3
3
3
6
PATH TO A NODE
SELECT GROUP_CONCAT(alias SEPARATOR '/') as
path
FROM options
WHERE lft <= 4 AND rgt >= 5
ORDER BY lft ASC;
(sandal/blouse/skirt/woman)

PATH TO A NODE
SELECT GROUP_CONCAT(alias SEPARATOR '/') as
path
FROM con_menu
WHERE lft <= 20 AND rgt >= 21 AND lft > 1
ORDER BY lft ASC;
(Messaging/Read Private Message)

Root Node
1
2
3
4 5
lft rgt
1 36
2 15
3 8
4 5
6 7
9 14
10 11
12 13
16 21
17 18
19 20
22 35
23 28
24 25
26 27
29 34
30 31
32 33
6 7
8 9
1
0
1
1
1
2
1
3
1
4
1
5
1
6
1
7
1
8
1
9
2
0
2
1
2
2
3
5
2
3
2
8
2
4
2
5
2
6
2
7
2
9
3
4
3
0
3
2
3
1
3
3
3
6
LEVEL OF NODE
SELECT b.id, COUNT(a.id) AS level
FROM #__ AS a, #__ AS b
WHERE b.lft BETWEEN a.lft AND a.rgt
AND a.lft = 19
GROUP BY b.id

Root Node
1
2
3
4 5
lft rgt
1 36
2 15
3 8
4 5
6 7
9 14
10 11
12 13
16 21
17 18
19 20
22 35
23 28
24 25
26 27
29 34
30 31
32 33
6 7
8 9
1
0
1
1
1
2
1
3
1
4
1
5
1
6
1
7
1
8
1
9
2
0
2
1
2
2
3
5
2
3
2
8
2
4
2
5
2
6
2
7
2
9
3
4
3
0
3
2
3
1
3
3
3
6
PARENT ID OF A NODE
SELECT id, (SELECT id
FROM #__ t2
WHERE t2.lft < t1.lft AND t2.rgt > t1.rgt
ORDER BY t2.rgt-t1.rgt ASC
LIMIT 1)
AS parentid FROM #__ t1
ORDER BY (rgt-lft) DESC

Root Node
1
2
3
4 5
lft rgt
1 36
2 15
3 8
4 5
6 7
9 14
10 11
12 13
16 21
17 18
19 20
22 35
23 28
24 25
26 27
29 34
30 31
32 33
6 7
8 9
1
0
1
1
1
2
1
3
1
4
1
5
1
6
1
7
1
8
1
9
2
0
2
1
2
2
3
5
2
3
2
8
2
4
2
5
2
6
2
7
2
9
3
4
3
0
3
2
3
1
3
3
3
6
MAXIMUM DEPTH
SELECT MAX(level) AS height
FROM (
SELECT b.id, (COUNT(a.id) - 1) AS level
FROM #__ AS a, #__ AS b
WHERE b.lft BETWEEN a.lft AND a.rgt
GROUP BY b.id
) AS L1

Root Node
1
2
3
4 5
lft rgt
1 36
2 15
3 8
4 5
6 7
9 14
10 11
12 13
16 21
17 18
19 20
22 35
23 28
24 25
26 27
29 34
30 31
32 33
6 7
8 9
1
0
1
1
1
2
1
3
1
4
1
5
1
6
1
7
1
8
1
9
2
0
2
1
2
2
3
5
2
3
2
8
2
4
2
5
2
6
2
7
2
9
3
4
3
0
3
2
3
1
3
3
3
6
INSERT A NODE
UPDATE #__ SET rgt=rgt+2 WHERE rgt >= 25;
UPDATE #__ SET lft=lft+2 WHERE lft >= 24;
INSERT INTO #__ SET lft=24, rgt=25, ��.;

Root Node
1
2
3
4 5
lft rgt
1 36
2 15
3 8
4 5
6 7
9 14
10 11
12 13
16 21
17 18
19 20
22 35
23 28
24 25
26 27
29 34
30 31
32 33
6 7
8 9
1
0
1
1
1
2
1
3
1
4
1
5
1
6
1
7
1
8
1
9
2
0
2
1
2
2
3
7
2
3
3
0
2
6
2
7
2
8
2
9
3
1
3
6
3
2
3
4
3
3
3
5
3
8
INSERT A NODE
UPDATE #__ SET rgt=rgt+2 WHERE rgt >= 25;
UPDATE #__ SET lft=lft+2 WHERE lft >= 24;
INSERT INTO #__ SET lft=24, rgt=25, ��.;
2
4
2
5

? WHO AM I ?
? NESTED SET INTRO
? NESTED SET BASIC QUERIES
? NESTED SET BASIC QUERIES OPTIMIZATION
?
?
?

Common INSERT
INSERT INTO #__ (part_number, unit_price,
eau,��.)
VALUES (��.);
Common SELECT
SELECT unit_price FROM #__
WHERE part_number = $part_number;

Common INSERT
eau,��.)
VALUES (��.);
Common SELECT
WHERE part_number = $part_number;
PROBLEMS:
1. Speed
2. Data accuracy (inconsistent white spacing)

Amended INSERT
php: $concat = preg_replace('/s+/', '', $input);
eau,part_number_concat,��)
VALUES (��,$concat);
Amended SELECT
WHERE part_number_concat = $concat;
PROBLEMS:
1. Speed

Optimized INSERT
php: $crc = crc32($concat);
eau,part_number_concat,crc_partnumberconcat��)
VALUES (��,$concat,$crc);
Optimized SELECT
WHERE crc_partnumberconcat = $crc
AND part_number_concat = $concat; (singularity)
PROBLEMS:
1. Speed

Optimized INSERT
php: $crc = crc32($concat);
eau,part_number_concat,crc_partnumberconcat��)
VALUES (��,$concat,$crc);
Optimized SELECT
WHERE crc_partnumberconcat = $crc
PROBLEMS:
1. Speed
2. Data accuracy (inconsistent 32bit vs 64bit)

Optimized INSERT
eau,part_number_concat,sha_partnumberconcat��)
VALUES (��,$concat,SHA1($concat));
Optimized SELECT
WHERE sha_partnumberconcat = SHA1($concat);
PROBLEMS:
1. Speed
2. Data accuracy (inconsistent 32bit vs 64bit)

Optimized INSERT (* BATCH)
ALTER TABLE #__ DROP INDEX x
eau,part_number_concat,sha_partnumberconcat��)
VALUES (��,$concat concat,SHA1($concat));
ALTER TABLE #__ ADD INDEX x (��column��)

Hits Counter Table
CREATE TABLE hit_counter (
cnt INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB;
INSERT INTO hit_counter (cnt) VALUES (0);
UPDATE hit_counter SET cnt = cnt + 1;
SELECT cnt FROM hit_counter;

Hits Counter Table
cnt INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB;
INSERT INTO hit_counter (cnt) VALUES (0);
UPDATE hit_counter SET cnt = cnt + 1;
SELECT cnt FROM hit_counter;
Hits Counter Table (Optimized)
slot TINYINT UNSIGNED NOT NULL PRIMARY KEY,
cnt INT UNSIGNED NOT NULL
) ENGINE=InnoDB;
INSERT INTO hit_counter VALUES
(0,0), (1,0), (2,0), (3,0), (4,0);
-- any amount of slots you require (n-1)
UPDATE hit_counter SET cnt = cnt + 1
WHERE slot = FLOOR(RAND() * 5);
-- the amount of slots you assigned
SELECT SUM(cnt) FROM hit_counter;

? WHO AM I ?
? NESTED SET INTRO
? NESTED SET BASIC QUERIES
? NESTED SET BASIC QUERIES OPTIMIZATION
? STRING LOOKUP OPTIMIZATION TRICK
? INDEXING OPTIONS
?

SELECT * FROM Orgchart SELECT a, c FROM Orgchart SELECT a, c FROM Orgchart
WHERE lft - rgt = 1; WHERE lft - rgt = 1; WHERE lft =(rgt �C 1);
SELECT name FROM people mysql> ALTER TABLE people ADD KEY (idx_name(6));
WHERE name = ��Hans��;
CREATE TABLE t ( KEY(c1,c2,c3) ? KEY(c1,c3) Specificity!
c1 INT,
c2 INT,
c3 INT,
KEY(c1),
KEY(c2),
KEY(c3)
);

SELECT * FROM Orgchart SELECT a, c FROM Orgchart SELECT a, c FROM Orgchart
WHERE lft - rgt = 1; WHERE lft - rgt = 1; WHERE lft =(rgt �C 1);
SELECT name FROM people mysql> ALTER TABLE people ADD KEY (idx_name(6));
WHERE name = ��Hans��;
CREATE TABLE t ( KEY(c1,c2,c3) ? KEY(c1,c3) Specificity!
c1 INT,
c2 INT,
c3 INT,
KEY(c1),
KEY(c2),
KEY(c3)
);
SELECT cc FROM payment WHERE staff_id = 2 AND customer_id = 584;
KEY(staff_id,customer_id) ?
SELECT SUM(staff_id = 2), SUM(customer_id = 584) FROM paymentG
** 1. row **
SUM(staff_id = 2): 7992
SUM(customer_id = 584): 30
ALTER TABLE payment ADD KEY(customer_id, staff_id);

CREATE TABLE profile(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
sex CHAR(1) NOT NULL,
age TINYINT NOT NULL,
country VARCHAR(255) NOT NULL,
region VARCHAR(255) NOT NULL DEFAULT ��,
city VARCHAR(255) NOT NULL DEFAULT ��,
color_hair VARCHAR(255) NOT NULL DEFAULT ��,
color_eyes VARCHAR(255) NOT NULL DEFAULT ��,
name
��
��
rating TINYINT NOT NULL DEFAULT 1,
PRIMARY KEY(id)
);

name
��
��
PRIMARY KEY(id)
);
WHERE age BETWEEN 18 AND 25
ORDER BY rating ASC
MySQL can��t use added index if primary
index uses range criterion
KEY(sex, country)
NO Selectivity!!!
KEY(sex, country)
Assumption: all searches will include sex and
most will include country
Trick: AND sex IN('m', 'f')

name
��
��
PRIMARY KEY(id)
);
(sex, country, age)
(sex, country, region, age)
(sex, country, region, city, age)
Using the IN() trick, we can implement just
the
(sex, country, region, city, age) index.
Why is age at end?
Remember our range problem?
MySQL uses indexes from left to right until
the first range query
Trick: Convert WHERE age BETWEEN 18 and 25 to
WHERE age IN(18,19,20,21,22,23,24,25)

?

?
?

?
?
?

?
?
?
?

?
?
?
?
?

?
?
?
?
?
?

�ݺ�ߣ

MySQL Integral DB Design #JAB14

More Related Content

MySQL Integral DB Design #JAB14