[buildroot-test,v2,3/4] web/schema.sql: add indexes on the database schema
diff mbox series

Message ID 20190708081707.28348-4-victor.huesca@bootlin.com
State Accepted
Headers show
Series
  • allow results to be filtered by symbols
Related show

Commit Message

Victor Huesca July 8, 2019, 8:17 a.m. UTC
Add indexes on the two columns of `symbol_per_result` to improve significantly
queries involving symbols (this is an over 80M rows table).
This table is heavily used to retrieve results matching a symbols and it is not
an option filter symbols without these indexes.

Also mark these same columns as foreign keys to ensure integrity of the
database.

Signed-off-by: Victor Huesca <victor.huesca@bootlin.com>
---
 web/schema.sql | 7 ++++++-
 1 file changed, 6 insertions(+), 1 deletion(-)

Comments

Thomas Petazzoni July 12, 2019, 1:46 p.m. UTC | #1
Hello Victor,

On Mon,  8 Jul 2019 10:17:06 +0200
Victor Huesca <victor.huesca@bootlin.com> wrote:

> Add indexes on the two columns of `symbol_per_result` to improve significantly
> queries involving symbols (this is an over 80M rows table).
> This table is heavily used to retrieve results matching a symbols and it is not
> an option filter symbols without these indexes.
> 
> Also mark these same columns as foreign keys to ensure integrity of the
> database.
> 
> Signed-off-by: Victor Huesca <victor.huesca@bootlin.com>

Thanks, I've applied to buildroot-test with a slightly improved commit
log:

  https://git.buildroot.org/buildroot-test/commit/?id=d40fe4f389a60569e2369cb69f1703f7d8b1c6a3

Thomas

Patch
diff mbox series

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;