In my current job I maintain a database of 2B records that is (unfortunately) on spinning rust and on a last-gen Intel desktop CPU system. This database is a read-only data warehousing sort of setup.

If you need to do operations like this on a database with this setup I'd suggest checking out the following config options:

    1. max_heap_table_size
    2. read_buffer_size
    3. sort_buffer_size
    4. join_buffer_size
    5. thread_concurrency
    6. tmp_table_size

I'd also suggest looking into TEMPORARY TABLEs with ENGINE=MEMORY.

I have not included values in this because the values that make sense for you are likely not the values that make sense for me. Check out MySQL's documentation for what these values effect [0]. The defaults in even MySQL's huge config are very outdated for the horsepower that modern computers bring to the table. It's funny that MySQL's once massive 4GB of RAM config file is now the appropriate setup for my laptop.

[0] - https://dev.mysql.com/doc/refman/5.7/en/server-system-variab...