Would querying from a partitioned table be faster than from multiple tables with the same table definition?

Question

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?

Answer 1

Run the same queries twice. Did they run faster the second time? Guess what. The first time the data was sitting on disk and had to be pulled from disk. The second time, it was all sitting in RAM. The difference might be 10-fold.

Why would you ever fetch 5M rows at once? And, if you split 50M rows into 10 'partitions', why would that 5M be relevant? Might you need a different slice of the data?

Partitioning, either manually, or with PARTITION, rarely provide any performance benefit for real tasks.

If you often need to fetch 10M rows, let's discuss ways to avoid fetching that many rows, yet still get the same answer. Using Summary Tables is a common technique in Data Warehousing.

Provide more details, and I will explain in more detail why I am throwing cold water on your Question.