Using EnsDb databases in a MySQL server has currently some performance issues. The reason are the indices and identifiers that are character. SQLite has no problem with that, MySQL struggles when it comes to join tables.
Why not introducing internal IDs also for the SQLite backend: no performance difference between character and integer-based indices/columns in SQLite. Including them would only blow the database up.
Using
EnsDb
databases in a MySQL server has currently some performance issues. The reason are the indices and identifiers that arecharacter
. SQLite has no problem with that, MySQL struggles when it comes to join tables.Needed changes:
integer
internal IDs (e.g.internal_gene_id
). Done in https://github.com/jotsetung/ensembldb/commit/2e67d497efd9fc7a45c6e1fca3deb21d1d481141Why not introducing internal IDs also for the SQLite backend: no performance difference between
character
andinteger
-based indices/columns in SQLite. Including them would only blow the database up.