Frankly bizarre article. The author argues that SQLite is inappropriate for storing 16GB of <10KB text files because if you don't index by filename, looking up a file by its filename is going to be very slow (What he experienced).
He then declares that "The data will now (probably) be transferred to a sharded MySQL database cluster and that will speed up almost anything by large margins.", as if MySQL wouldn't be similarly slow if it had to query 16GB of data without indexes. I don't get his thought process at all, and he writes with such conviction.
> The argument that SQLite files can theoretically be 281 Terabytes large is a spurious one. And it is being purported by the SQLite team despite them knowing better.
and
> Think about it: Do you know how large the biggest hard disks or SSDs are nowadays? They’re not much larger than maybe 10 Terabyte.
Wow. Your file-based database is limited by your available space. Shocker!
What insane amounts of data are we talking about?
> [Name] asked what the best way would be to store about 16GB of research data efficiently
So it's 16 GB. My SSD can handle that.
> The biggest limit is physics
Ah, physics! Good old Bekenstein bound. No?
Let's move on.
> When I created the database in the first place, it created a pretty basic index
and
> But as soon as I tried to search for something that was not indexed – the filename column –, SQLite had to actually open the database file and check every single entry from the very beginning until it found the entry I was looking for.
So you're doing it wrong, you even know you're doing it wrong, you know what to do, but you definitely don't want maybe issue a CREATE INDEX?
3 comments
[ 3.3 ms ] story [ 18.3 ms ] threadHe then declares that "The data will now (probably) be transferred to a sharded MySQL database cluster and that will speed up almost anything by large margins.", as if MySQL wouldn't be similarly slow if it had to query 16GB of data without indexes. I don't get his thought process at all, and he writes with such conviction.
> The argument that SQLite files can theoretically be 281 Terabytes large is a spurious one. And it is being purported by the SQLite team despite them knowing better.
and
> Think about it: Do you know how large the biggest hard disks or SSDs are nowadays? They’re not much larger than maybe 10 Terabyte.
Wow. Your file-based database is limited by your available space. Shocker!
What insane amounts of data are we talking about?
> [Name] asked what the best way would be to store about 16GB of research data efficiently
So it's 16 GB. My SSD can handle that.
> The biggest limit is physics
Ah, physics! Good old Bekenstein bound. No?
Let's move on.
> When I created the database in the first place, it created a pretty basic index
and
> But as soon as I tried to search for something that was not indexed – the filename column –, SQLite had to actually open the database file and check every single entry from the very beginning until it found the entry I was looking for.
So you're doing it wrong, you even know you're doing it wrong, you know what to do, but you definitely don't want maybe issue a CREATE INDEX?