Search code examples
mysqlpdoinnodbquery-performance

MySQL PDO create and populate 1000 small tables in 3 seconds or less?

Is it possible? From a single process? DB is on SATA disk. I am using ubuntu 14.04. All tables have 20-60 rows and 6 columns each. I am using transactions.

The current sequence is:

  1. Create table
  2. Start transaction
  3. Insert #1
  4. Insert #2
  5. ...
  6. Insert #n
  7. Commit

Right now I am getting about 3-4 tables/second.

Conclusion: When I disabled logging my performance became similar to phpmyadmin. So, as Rick James suggested, I guess there is no way to achieve further improvements without a faster storage.

Solution

  • On a spinning drive, you can get about 100 operations per second. CREATE TABLE might be slower since it involves multiple file operations in the OS. So, I would expect 1000 CREATE TABLEs to take more than 10 seconds. That's on Ubuntu; longer on Windows.

    It is usually poor schema design to make multiple tables that are identical; instead have a single table with an extra column to distinguish the subsets.

    INSERTing 40K rows--

    • 40K single-row INSERTs with autocommit=ON -- 400 seconds.
    • 1000 multi-row INSERTs of 20-60 rows each, again COMMITted after each statement -- 10 seconds.
    • A single INSERT with 40K rows (if you don't blow out some other limitation) -- possibly less than 1 second.

    Do not use multi-statement queries; it is a potential security problem. Anyway, it won't help much.