How do you quickly search databases that are hundreds of millions of records? | Hacker News Reader

How do you quickly search databases that are hundreds of millions of records?

9 pointsvachnadze9 comments

I am new here and so far I've downloaded one database of 100 million records and another database of 200 million records (the recent twitter hack). I thought that exploring these databases would be a good excuse for me to learn MySQL. So far it's been a major headache and I've spent probably at least 20 hours at it and have gotten next to nowhere. I've used ChatGPT to guide me through the process, and it has worked wonders in getting me as far as I've gotten, but I encounter error after error and have troubleshooted at least 50 different problems so far. So at the moment I'm stuck with using a large text viewer, but that will take over an hour to run a single query. In trying to get the files to load into a MySQL table, the most common problem is with the way the databases are parsed. Whatever delimiter is used in the file always appears somewhere in the data as well. The other common problem is that these files contain weird characters that MySQL can't handle. I've even used python to try to clean the files, but that also sends me down a rabbit hole of error after error after error. So before I put a lot more time into this, am I even headed in the right direction? I figured MySQL is a good tool to know and is meant for large databases. But is it not appropriate for databases in the hundreds of millions of records? I want to be able to search a name or e-mail and not wait more than a minute or so for the query to finish (and I know MySQL will allow me to do partitions and other time saving tricks). I don't really have an interest in using these databases in scripting. My main interest is just running simple queries on them. So are there better tools that aren't too difficult to learn or expensive to use?

Use an analytic database with column storage. Even on modest hardware they can insert millions of rows per second in a variety and query hundreds of millions per second. They are ideal for exploring data.

MySQL by contrast is ideal for transaction processing, which is a very different problem.

I work on ClickHouse so I'm going to recommend that. It's open source and runs on everything from laptop to Kubernetes to cloud. There are multiple cloud services for ClickHouse as well, if you don't wnat to run it yourself. If you want to use ClickHouse have a look at the Altinity Quickstart for ClickHouse video on Youtube to learn how it works. [0]

[0] https://www.youtube.com/watch?v=phTu24qCIw0

I work at Altinity and did the video. There are also many other great analytic databases beside ClickHouse, including: Snowflake, BigQuery, Druid, and Redshift.

Try just using ripgrep, sed, cut, etc. I found it is good enough for most data analytics I want to do on dumps like these. Particularly replacing grep with ripgrep makes a big difference.

If that isn't fast enough, perhaps try feeding them into clickhouse. There is a recent talk on fosdem about how to load dumps easily into it: https://fosdem.org/2023/schedule/event/fast_data_analytical_...

You probably want to step away from the code editor and to the whiteboard, and understand what you are looking for from the data set.

Look at the table names, do they tell any story?

Look at the constraints, look at the foreign keys. Do the attributes stand out in any way?

Map it out and maybe you'll find a strategy.

Then when you sit down you'll probably have a better idea what you're dealing with.

I had to migrate big databases a couple times in my career and the db expert I worked with basically didn't even think about code for a week. We were like CSI agents trying to figure out wtf is going on.

MySQL can do the analysis you need with the right indexing (I have worked with 100m+ row analytics in MySQL) but a columnar database as others have mentioned would be better, as would a columnar version of MySQL like infobright or infinidb. (At one former firm we switched from mysql to infobright for some jobs due to significant speed benefits working with hundreds of millions of rows while retaining backward compatibility with our code base.) (Although for string searches if you want fuzzy search you might be better off with ElasticSearch/Lucene than a relational database.)

As for delimiters, escape characters, bad characters (and character sets and collation settings) and import/export techniques, these exist across databases. If the data were clean, analytics would be a quick and easy job, but it rarely is; that challenge is kind of the nature of the problem domain.

I'd use clickhouse-local, ideal for this workload without needing to load into a full ClickHouse instance.

https://clickhouse.com/blog/extracting-converting-querying-l...

I would definitely try postgres before MySQL.

Had plenty of shitty query optimizations from MySQL.

But besides that, how much mb are 100million?

And what is your goal?

I have used SQL Server for a long time. I have imported 100s millions of records without an issue. It does take time and you need to know how to index properly. With SQL Server, you can add columnar indexes onto the same table with traditional indexes.

perhaps you're trying for too high a level of complexity? try treating them as text files and using "grep" tools on them.

Assuming you os has grep agree it is fastest.

to search for name joe as example:

> grep -r joe .

-r will do a recursive search in case you have files in folders and those folders have more folders with files

Reply on news.ycombinator.com