aboutsummaryrefslogtreecommitdiff
path: root/web
diff options
context:
space:
mode:
authorGravatar Victor Huesca <victor.huesca@bootlin.com>2019-07-08 10:17:06 +0200
committerGravatar Thomas Petazzoni <thomas.petazzoni@bootlin.com>2019-07-12 15:44:22 +0200
commitd40fe4f389a60569e2369cb69f1703f7d8b1c6a3 (patch)
tree79068b0fe5b6dff6f765fa281580f7d9d058e04a /web
parent8b75b01e886f833a9536a8704f959ad8cc6fc1bf (diff)
downloadbuildroot-test-d40fe4f389a60569e2369cb69f1703f7d8b1c6a3.tar.gz
buildroot-test-d40fe4f389a60569e2369cb69f1703f7d8b1c6a3.tar.bz2
web/schema.sql: add indexes on the database schema
This commit adds indexes on the two columns of `symbol_per_result` to significantly improve queries involving symbols (this is an over 80M rows table). This table is heavily used to retrieve results matching a symbol, and without those indexes, the duration of such queries is prohibitive. While at it, we also mark these columns as foreign keys to ensure the integrity of the database. Signed-off-by: Victor Huesca <victor.huesca@bootlin.com> Signed-off-by: Thomas Petazzoni <thomas.petazzoni@bootlin.com>
Diffstat (limited to 'web')
-rw-r--r--web/schema.sql7
1 files changed, 6 insertions, 1 deletions
diff --git a/web/schema.sql b/web/schema.sql
index 65fd883..8b854a2 100644
--- a/web/schema.sql
+++ b/web/schema.sql
@@ -27,5 +27,10 @@ CREATE TABLE `symbol_per_result` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`result_id` int(11) NOT NULL DEFAULT '0',
`symbol_id` int(11) NOT NULL DEFAULT '0',
- PRIMARY KEY (`id`)
+ PRIMARY KEY (`id`),
+ CONSTRAINT `fk_result_id` FOREIGN KEY (`result_id`) REFERENCES `results`(`id`),
+ CONSTRAINT `fk_symbol_id` FOREIGN KEY (`symbol_id`) REFERENCES `config_symbol`(`id`),
+ INDEX `ix_symbol_id`(`symbol_id`),
+ INDEX `ix_result_id`(`result_id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;