ENH: Allow custom mysql queries
Created by: Alexander Schlemmer
In order to address performance critical applications I have a suggestion: Allow to include raw mysql queries and provide access via a REST endpoint.
Example use case:
An institution is using the django-frontend and replaced their MySQL-database with CaosDB in order to become more flexible. Although the backend works as expected some queries become really slow, although they had a simple MySQL-representation before, e.g.: SELECT ID from JOIN ...
.
Solution:
- Transform the original mysql query in its (use-case specific) analogon using CaosDBs MySQL-tables.
- Connect the specific MySQL-procedure frontend via e.g. "https://name-of-instance.org/CustomQuery/NameOfSpecificQuery?var1=val1"
-
val1
could be used within the query as variable e.g. in a where-clause.
The query and its CQL counterpart should become part of integration tests. Tests should be really straight-forward here, as result sets are expected to be equivalent.
Another advantage is that this procedure allows for direct performance comparisons to simple MySQL-statements and therefore provide an objective baseline for optimizations and for calculating the performance-loss due to the increased flexibility in CQL.
Imported comments:
By Alexander Schlemmer on 2020-12-16T14:00:43.790Z
Discussed on 2020-12-16:
- Use case for this rather testing.
- One of the main problems is permission checking.
- It seems to be consensus to spend more time on improving performance of specific cases in CQL directly.