We setup a testing environment with some sample data, the source data is stored in the following Synapse table:
https://www.synapse.org/#!Synapse:syn26050977/tables/
We setup both a MySQL RDS instance and an AWS Elasticsearch cluster where we imported the data to perform some testing around indexing and searching. The number of records is very small (51) so we cannot gauge performance metrics.
Some example queries are taken from: https://sagebionetworks.jira.com/browse/PLFM-6876
MySQL
A dedicated table was setup importing the data from the Synapse table: only string, string_list and largetext columns were imported. This totaled to 46 columns. The setup is very similar to a real Synapse table (same datatypes were used).
MySQL limits the number of secondary indexes to 64, and the total number of columns in one index to 16. We added a special column that contains the concatenated values of all the columns and created a FULL TEXT index on that particular column.