Teiid Designer Question regarding Index Import

[cjohn001]

Hello together,

I have a question regarding the usage of Teiid Designer. When I import a source model from my database  I do this via Import / Teiid Connection >> Source Model

The dialog which opens has an option "Import Indexes" which by default is set to false. I never switched it to true so far. However, the indexes in my model are so far always exported. At least they result in navigation properties. Moreover, my current thinking is, that I use a index in my underlaying mysql database primary to get an speedup for sorting as a btree is generated for an index column. So I am wondering for what the switch "Import Indexes" in Teiid Designer is good for.

Thanks for your help.

Best regards,

Christoph

[rareddy]

Yes, indexes are for the establishing relations, that can be used in the optimized query plans, navigation in odata and also during the materialization table creation and index maintenance. As per why switch not being honored in Designer could be a bug.

[cjohn001]

Hello Ramesh,

not sure if there is really an error in Teiid Designer or if just my thinking is wrong. I just activated the index feature and I got the indexes as additional entities exported in the package diagram of my source, see attached screenshot.

Does this mean, that Teiid needs to know from the presence of the index in the mysql database to actually use it, rather than using a direct table access to the relevant attribute in the mysql database? So far I thought the index is a mysql database internal construct which only the database uses to improve query performance. To my best understanding I would not write a sql statement different for querying an attribute with and without index or is this something I would have to do in sql and I just did not know so far? 

[rareddy]

I am not sure (do not know) what was the original intention behind them was. I always seem to remember to delete them if I accidentally imported them. The difference is "index keys" vs "indexing". Teiid cares about the "index keys", PK, FK, Unique etc. Where as source databases, as well as Teiid in the case of materialization, make use of the "index keys" to indexing i.e. btree based lookups. Anyways, that is the way I see it.

[cjohn001]

Hello Ramesh,

so if I understand you correct, than teiid only needs the knowledge of index keys in case of materialization to build a btree on the column itself. Hence as long as I am not using matrealization I do have no need for them. Ok understood, than I can delete them again. The index feature in the importer than starts to make sence and I would expact that the behavior than is correct.

Best regards,

Christoph

[rareddy]

I also said they need for optimized query plans before

[cjohn001]

Ok,

now I would like to know more

I am currently discussing a similar topic with Steve.

[TEIID-5680] Improve performance of odata expand operations - JBoss Issue Tracker

https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary?$select=idDiaryEntry,MealNumber&$expand=fkDiaryToFDBProducts($select=brands,idCode,product_name)&$filter=AddedDateTime%20ge%202019-03-06T00:00:00%2B01:00%20and%20AddedDateTime%20le%202019-03-07T00:00:00%2B01:00&$orderby=MealNumber%20desc&$skip=0&$top=100

In the Teiid importer settings in Teiid Designer, there are different options to get a cardinality value for the tables in order to optimize queries. So using the " Import Statistics" switch in the importer solved a performance issue with join operations for me. Seems like the switch only works for Oracle and mysql databases. From the tooltips in the exporter settings it seems like using "import indexes" together with "use any index cardinality" also results in cardinality data.

Do you know more about the optimization involved here? Would I end up with better parameters for optimization in case of exported indexes?

A question that is still open in the discussion with Steve is how Teiid can get the cardinality info interactively. He sketched an approach which is planned for openshift. However, is the way via the indexes a potential possibility to  get cardinality data at startup from the database?

Maybe we should shift the discussion than into the linked issue,  the info would than be in one place.

[rareddy]

I do not know too much about Designer and its various options to gather the data. Import Statistics is the correct one for sure. Basically when the cardinality information is there, then the optimizer makes better decisions on pushdown functionality and/or planning the join planning.

Interactive/runtime update of this information has been a long time request, and it is costly so we never took it upon doing. [TEIID-245] Update costing metadata at runtime - JBoss Issue Tracker

Yes, if you are inclined you can do this during startup, as it is just a property on the view, you can do I think "ALTER View Options (add cardinality)" something like that. But in order for you to grab the costing information, you would need to write a MetadataFactory that can read this information from data source and apply to the view. Note this can make deployment times longer, but if you are OK with it then it is not too difficult. Yes, java coding is required

Ramesh..

[cjohn001]

OK,

than I will probably shift this into a future decission. First I would like to get my app running. Optimization and scaling than will be the next steps Looks like I will not find a way around this Java thing than