bnENpQug

13
2
3
41 DENOMRMALIZATION
5
6We place all attributes in the same table. This will remove joins on the three original tables and subquery.
7
8DROP TABLE IF EXISTS passing;
9CREATE TABLE passing(
10regno VARCHAR(8),
11time VARCHAR(20),
12station INTEGER,
13owner VARCHAR(40),
14PRIMARY KEY(regno,time),
15FOREIGN KEY(regno) REGERENCES car(regno)
16);
17
18
192 CREATING INDEXES
20Creating index on table toll_station on the attribute name.
21
22EXPLAIN SELECT SQL_NO_CACHE c.owner
23FROM car c
24WHERE c.regno IN (SELECT p.regno
25FROM passing p, toll_station s USE INDEX(newIndex)
26WHERE p.station=s.id
27AND s.name LIKE 'Sandviken');
28
29CREATE INDEX newIndex ON toll_sation(name);
30
313 SPECIFYING INDEX HINTS
32
33We specify that the DMBS tuses the primary key for subscription in addition to the primary key of passing.
34
35EXPLAIN SELECT SQL_NO_CACHE c.owner
36FROM car c
37WHERE c.regno IN (SELECT p.regno
38FROM passing p, toll_station s USE INDEX(PRIMARY)
39WHERE p.station=s.id
40AND s.name LIKE 'Sandviken');
41
424 REWRITING THE SQL QUERY
43
44We use SELECT DISTINCT instead of using subqueries. Will not make it faster.
45
46EXPLAIN SELECT SQL_NO_CACHE DISTINCT c.owner
47FROM car c, passing p, toll_station s
48WHERE c.regno=p.regno
49AND p.station=s.id
50AND s.name='Sandviken';