I am trying to figure out what kind of database I should use for my project to get the best speed possible.
I am not sure if MySQL is the best solution, but I will use MySQL as an example to explain the data that I currently have. The data currently (or will) be arranged in 2 tables.
Table 1 has have 3 columns and Table 2 has have 1 column. Both tables will have around 1 billion rows each.
The project will get the first value from Table 2 and check if it exists in the first column of Table 1.
This project is currently running on a computer which has a mechanical drive.
Rough estimates suggest at the data will use around 500GB of drive space.
- To be able to insert the data into the tables as fast as possible,
- To be able to compare each column in Table 2 to see if the same value exists in column 1 of Table 1 as fast as possible.
At the moment, I am inserting the data into a MySQL database and batching the inserts into groups of 1000, which is averaging to about 200 inserts per second. At this rate, it will take about 2 months to generate and insert the 1 billion records..
The process of generating the data and inserting the data is using around 5% CPU, around 1GB RAM of the 16GB available, and when checking the disk IO, it is averaging about 2MB/s. So my guess is that the mechanical drive is the bottleneck. I can constantly hear it seeking away.
With the possible mechanical drive as the bottleneck in mind (correct me if I am wrong about this being the bottleneck), is MySQL the best option to achieve the 2 goals above as fast as possible, or is there a different type of database I should be considering and using instead?