When Does a MySQL Table Hit Its Limits? Insights and Demo

MySQL itself does not impose a hard limit on the number of rows in a single table; the limit is determined by the operating system’s file size constraints, with a common industry myth of 5 million rows.

Alibaba’s “Java Development Manual” recommends sharding when a single table exceeds 5 million rows or 2 GB in size.

A simple test

The following Java method repeatedly inserts rows into a users table while printing the count to the console.

private static void insertDataDemo() {
  DButil dButil = new DButil();
  myCon = dButil.getConnection();
  try {
    int i = 0;
    while (1 == 1) {
      i++;
      String sql = "insert into users (user_name,user_password)"
        + " value ('" + i + "','password')";
      sta = myCon.createStatement();
      sta.execute(sql);
      System.out.println(i);
    }
  } catch (Exception e) {
    e.printStackTrace();
  } finally {
    dButil.close();
  }
}

After running overnight, more than two million rows were inserted, but the insertion speed slowed to one row every 2–3 seconds, which is unacceptable.

The maximum data volume a MySQL table can hold is not related to the row count but to MySQL configuration and hardware. MySQL loads table indexes into memory for performance; sufficient InnoDB buffer size allows full in‑memory loading and fast queries.

When a table grows beyond the memory capacity for its indexes, queries incur disk I/O, causing performance degradation. The exact impact also depends on table schema design, but the root cause is memory limitation.