I have a table that contains about 50 million rows that I have partitioned by 5 million rows per partition. I have manually partitioned it into Table_1, Table_2, Table_3, etc., not using MySql's built-in partition feature using the PARTITION keyword because I didn't know of this feature when I partitioned my table.
A lot of the times, I'll have to retrieve 10 million worth of rows, which would mean I'll have to query 2 partitions worth of data.
It may take anywhere from 2-10 seconds to retrieve the entire data (5 million rows) from just 1 table, which is pretty slow. Though after the initial query, the same query would be very fast (a few hundred ms).
Would partitioning my table(s) using the built in MySql partition feature help querying faster in my case?