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.
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?
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.
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.
I also said they need for optimized query plans before
now I would like to know more
I am currently discussing a similar topic with Steve.
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.
1 of 1 people found this helpful
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
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