[Feature] Adding indexes on relational tables serving as 'join' tables

[meuriceloup]

Hello,

At this moment, no indexes are created on 'join' tables. For instance, this TML schema:

entity Product {
    id : String
    orders -> Order[0..*]
}
entity Order {
    id : String
    products -> Product."Product.orders"[1]
}

relationaldb RelationalDatabase {
   tables {
      table {
         "Product" : "Product"
         idSpec ("Product.id")
      }
      table {
         "Order" : "Order"
         idSpec ("Order.id")
      }
   }
}

will physically result in the creation of 3 MariaDB tables:

+---------------------------------+ | Tables_in_RelationalDatabase | +---------------------------------+ | Order | | Order.products-Product.orders | | Product | +----------------------------------+

While Order and Product tables serve for storing, resp., orders and products, the 'Order.products-Product.orders' table is created for storing and establishing the relation between an order and a product. However, no indexes are created on this 'join' table. I understand that you prefer not creating any explicit foreign keys but why not creating indexes? I think creating indexes on join tables will drastically improve the query access time (especially QL queries resulting in join SQL queries). Don't you think so?

[DavyLandman]

Good suggestion, we should indeed generate indexes for these columns.

[tvdstorm]

We're creating explicit foreign key constraints with cascade delete now.