Start a new topic

SQLite vs MySQL (for freelance use)

 Hi Igor,

Just a quick question about SQLite vs MySQL. Is there any point looking into CT's MySQL functioanality, or should I just stick to SQLite? I don't plan to share any resources with anyone, just wondering if either has any benefits for me as a freelancer, working on my single work computer. In terms of lookup speed, features, etc.


Michael: ...or should I just stick to SQLite? 

SQLite will do nicely. No reason to look into MySQL for other reasons than basic functionality for CT.


Hi Hans,

Yeah, that seems reasonable. Am going to create a fresh SQLite db (instead of using my old/huge TMLookup db, which is what I was using in the past with Total Recall), and start again.


Hi Michael,

In terms of performance, there shouldn't be much difference between the two databases. However, MySQL is extremely popular with lots of GUI tools. It is also the way to go when accessed from more than one machine simultaneously (e.g. from the server computer). I know you like experimenting with various solutions and it is really easy to set up both systems switching between them in Preferences >  General tab > Database connection. 

>I know you like experimenting with various solutions

Yeah, Michael, that was my impression too! So, tell me, how did your experiment to walk with both legs in one trouser leg end? Did you fall on your nose?

@the CT crew: As you all probably guessed already, I will be trying them both, and probably posting all kinds of weird & wonderful stuff about them at 4 in the morning for the next few days (and most likely annoying the hell out of Woorden of/from the Pants (US)/Trousers (UK)).

@Hans, I ended up with no legs to stand on, and yet a lot of extra floorspace, somehow. If you catch my drift.


Some of my first testing data:

importing PATENTS.tmx (4 MB) into a MYSQL vs SQLite db


mysql: 37 seconds

sqlite: 1:58

Interesting! This could really add up when importing HUGE TMXs.


The Count of Hastings: sqlite: 1:58

I never tried MySQL in CT, but has it been set to "indexing"?


Hmm, not sure what "setting it to indexing" would entail. 

I just created an empty table in both sqlite and mysql, and then timed importing the .tmx into each of them. 

I also think SQLite took far too long, it's only 4MB. And how big was the resulting table, in both cases?


Joseph: I just created an empty table in both sqlite and mysql, and then timed importing the .tmx into each of them

In the "pragmas", you can set a database to "Index." That can take a while, though I think SQLite took too long, whereas MYSql may have skipped indexing altogether.


Hmm, well, my sqlite db is 5.12 MB. This is with only that 1 table, with that one .tmx in it. I also tried it from scratch again, with a completely fresh db, and got a similar time for the sqlite import (1:48 this time).

Regarding the mysql, I have no idea how to even figure out where the relevant db file is on disk, so I can't count its size. I only just started investigating using MYSQL on my work computer. @Igor: any idea where to look? 

This is definitely one of the big advantages to using sqlite: everything is in a single file and is much more overzichtelijk.

btw: Recall speed is roughly identical.

Found the size of the MYSQL db, I think. I exported it as a "dump". It's 2.61 MB.

1 person likes this

Found the data. It's @ C:\Users\All Users\MySQL\MySQL Server 5.7\Data

Login to post a comment