Juiste index om a join te versnellen
Omdat veel velden bekend zijn onder verschillende namen (en ook nog wel eens willen veranderen van naam) heb ik in die tabel de namen niet opgenomen, die staan in een aparte tabel. De naam tabel heeft ook nog een flag om aan te geven wat voor naam erin staan (primaire naam, secundaire naam of voormalige naam). Met andere woorden, er kunnen meerdere namen per vliegveld in die tabel zitten.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE airports(
airport_id INT UNSIGNED AUTO_INCREMENT,
description TEXT,
geo_id INT UNSIGNED NOT NULL DEFAULT 1,
lng FLOAT(11,8) DEFAULT 0,
lat FLOAT(11,8) DEFAULT 0,
status TINYINT UNSIGNED NOT NULL DEFAULT 0,
airport_type TINYINT UNSIGNED NOT NULL DEFAULT 0,
create_date DATETIME NOT NULL,
last_update TIMESTAMP,
PRIMARY KEY(airport_id),
INDEX(geo_id)
)CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=InnoDB;
CREATE TABLE airport_names(
airport_name_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
airport_id INT UNSIGNED NOT NULL,
airport_name VARCHAR(150) NOT NULL,
name_status TINYINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY(airport_name_id),
INDEX(airport_id, name_status)
)CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=InnoDB;
airport_id INT UNSIGNED AUTO_INCREMENT,
description TEXT,
geo_id INT UNSIGNED NOT NULL DEFAULT 1,
lng FLOAT(11,8) DEFAULT 0,
lat FLOAT(11,8) DEFAULT 0,
status TINYINT UNSIGNED NOT NULL DEFAULT 0,
airport_type TINYINT UNSIGNED NOT NULL DEFAULT 0,
create_date DATETIME NOT NULL,
last_update TIMESTAMP,
PRIMARY KEY(airport_id),
INDEX(geo_id)
)CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=InnoDB;
CREATE TABLE airport_names(
airport_name_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
airport_id INT UNSIGNED NOT NULL,
airport_name VARCHAR(150) NOT NULL,
name_status TINYINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY(airport_name_id),
INDEX(airport_id, name_status)
)CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=InnoDB;
Om alle namen bij een vliegveld te kunnen krijgen kan ik deze query gebruiken:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT a.airport_id, a.description, a.geo_id, a.lng, a.lat, a.status,
a.airport_type, a.create_date, a.last_update,
b.airport_name, c.airport_name AS secondary_name, GROUP_CONCAT(d.airport_name) AS former_name
FROM airports a
INNER JOIN airport_names b ON (
a.airport_id = b.airport_id
AND b.name_status = 1
)
LEFT JOIN airport_names c ON (
a.airport_id = c.airport_id
AND c.name_status = 2
)
LEFT JOIN airport_names d ON (
a.airport_id = d.airport_id
AND d.name_status = 3
)
GROUP BY a.airport_id, a.description, a.geo_id, a.lng, a.lat, a.status,
a.airport_type, a.create_date, a.last_update, b.airport_name,
c.airport_name
a.airport_type, a.create_date, a.last_update,
b.airport_name, c.airport_name AS secondary_name, GROUP_CONCAT(d.airport_name) AS former_name
FROM airports a
INNER JOIN airport_names b ON (
a.airport_id = b.airport_id
AND b.name_status = 1
)
LEFT JOIN airport_names c ON (
a.airport_id = c.airport_id
AND c.name_status = 2
)
LEFT JOIN airport_names d ON (
a.airport_id = d.airport_id
AND d.name_status = 3
)
GROUP BY a.airport_id, a.description, a.geo_id, a.lng, a.lat, a.status,
a.airport_type, a.create_date, a.last_update, b.airport_name,
c.airport_name
Alleen is deze query mij te traag. Als ik hem als hierboven uitvoer (met een limit op 10 resultaten) duurt het 0.46 seconden. Laat ik de primaire naam weg is het 0.35 seconde, laat ik ook de secundaire weg is het 0.04 seconden (merk op dat de voormalige namen dan dus nog wel worden opgehaald).
Nu is het zo dat op het moment in de naam tabel alleen nog maar primaire namen staan, geen secundaire en ook geen voormalige namen. In de toekomst verwacht ik dat de query dus alleen nog maar trager wordt.
Vraag is, welke index kan ik gebruiken om dit te versnellen? Zoals boven te zien is heb ik al een index op airport_id en name_status. Deze heb ik ook geprobeerd in omgekeerde volgorde, maar dat maakt niets uit. Als ik met een EXPLAIN statement het executieplan bekijk dan wordt voor de primaire naam die index niet gebruikt.
Iemand een ander idee voor een index? Ook andere tabelstructuren zijn een optie.
Gewijzigd op 03/09/2013 09:22:25 door Erwin H
Nu moet ik wel een andere manier vinden om dat er dan alsnog weer in te krijgen, maar in elk geval is de query weer zo snel als ik zou willen. Dank je.