meowcat
commented
1 year ago Hi Jo,
1) The MySQL to SQLite converter is more or less your old script that you once posted, using mysql2sqlite and adding back the views (I should maybe add that). So yes it has the exact same layout as far as it can be reproduced.
2) The SQLite to mzVault converter is separate and converts to mzVault format, this has a different database layout.
3) I once did a 3-way comparison with MsBackendMassbankSql, having
- sqlite over NFS
- sqlite copied to local
- MySQL to a database hosted on the same physical machine but docker-to-docker.
SQLite copied to local was fastest, however I believe there are many factors, such as
- how it is actually used - in my application I used a ton of subsetting
Spectraobjects - IO speed and RAM
- network latency.
I have not yet tested mzVault format, for which I am making an MsBackend https://github.com/meowcat/MsBackendMzVault. There I am trying to do all subsetting via SQL filtering (lazily) and I wonder what difference this will make. If this is markedly faster than the current MsBackendMassbankSql implementation, I might try my hand at porting the method, since the queries are more or less the same.
First of all thanks for this converter @meowcat !
I was just wondering on two things:
1) does the SQLite have exactly the same format (i.e. data types, database layout)?
2) did you see any performance differences between the SQLite and MySQL versions?