Hey All,

I'm the dev of Repost Sleuth here on Reddit.

I'm looking for input on the most performant way to select ~100 million rows from a table with 1.3 billion rows in a MySQL database.

Example table looks like this

There's a composite index containing post_type and created_at

I periodically need to select a year worth of rows as fast as possible.

A query similar to:

SELECT id, hash, post_id FROM post WHERE post_type='image' AND (created_at BETWEEN '2022-1-1' AND '2022-7-15');

Explain for query:

https://preview.redd.it/u71h5hu53ub91.png?width=690&format=png&auto=webp&s=c64bd5dc5513088a3c2766b2e7c62fbcdbf01b88

The test database above explain was run on has 580 million rows.

For the past several years I've maintained a table for each year. This allowed me to select the whole table. I was able to pull all records fast enough for my needs.

I'm experimenting with keeping all data in a single table VS split by years to simplify things. However, with the table and query described above, it takes a painfully long time to pull the data I need.

I'm hoping someone here with more experience can offer some suggestions to optimize this. I'm not sure if there's ways to do the indexes better or possibly core MySQL tweaks

I'd appreciate any input.