Creating indexes in model first

Posts   
 
    
mihies avatar
mihies
User
Posts: 800
Joined: 29-Jan-2006
# Posted on: 05-Apr-2016 15:33:51   

I guess creating indexes is not supported by the designer. It'd be nice to have though. What is the suggested way to create those? Manually, through a sql script which is manually reapplied after each time database's schema creation is run?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 05-Apr-2016 18:15:12   

What is the suggested way to create those? Manually, through a sql script which is manually reapplied after each time database's schema creation is run?

The schema creation script shouldn't be run multiple times. The Update script is the one that is expected to be run every time you change the model. Since it is an Update script, it should not drop any indexes you have already created.

mihies avatar
mihies
User
Posts: 800
Joined: 29-Jan-2006
# Posted on: 05-Apr-2016 18:19:40   

It depends, of course. Yes, if one is upgrading then it should just add/modify new indexes. But sometimes I just need to create from scratch.

Either way, the question remains - is there any support whatsoever for index creation within designer? There is constraint support, indexes are really very similar. Handling indexes manually is at least error prone.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 06-Apr-2016 15:11:55   

mihies wrote:

It depends, of course. Yes, if one is upgrading then it should just add/modify new indexes. But sometimes I just need to create from scratch.

Either way, the question remains - is there any support whatsoever for index creation within designer? There is constraint support, indexes are really very similar. Handling indexes manually is at least error prone.

The thing with indexes is that they don't really belong to the model: they belong to the queries run on the database: they're there to make querying faster. This means that the best time to add indices is when you have analyzed which queries are ran so you can make them faster with dedicated indexes.

Just adding indexes on FKs might be tempting but it might be they're counter productive (if that table is more written than read, it hurts performance). It also might be, e.g. in case of compound FKs, you need multiple indexes, one for each pair/single field of the compound FK as it is used in queries.

Nevertheless we do have a workitem to add indexes, as people want to be able to specify them with the model, even though it makes little sense. It won't be in 5.0 though simple_smile

Frans Bouma | Lead developer LLBLGen Pro
mihies avatar
mihies
User
Posts: 800
Joined: 29-Jan-2006
# Posted on: 06-Apr-2016 15:49:22   

Great that it is in the queue. I digress on little sense though. It is not just about FKs. I can predict which queries I'll use often and what index would help.

Anyway, looking forward to it.