MySQL Performance - Number of Tables Vs. Number of Rows

This are some pros and cons of this two approaches in MySQL.

1. Many small tables.

Cons:

• More concurrent tables used means more file descriptors needed (check this)
• A database with 100.000 tables is a mess.

Pros:

• Small tables means small indexes. Small indexes can be loaded entirely on memory, that means that your queries will run faster.
• Also, because of small indexes, data manipulation like inserts will run faster.

2. Few big tables

Cons:

• A huge table imply very big indexes. If your index cannot be entirely loaded on memory most of the queries will be very slow.

Pros:

• The database (and also your code) it's clear and easy to mantain.
• You can use partitioning if your tables became so big. (check this).

From my experience a table of two millions rows (I've worked with 70 millions rows tables) it's not a performance problem under MySQL if you are able to load all your active index on memory.

If you'll have many concurrent users I'll suggest you to evaluate other technologies like Elastic Search that seems to fit better this kind of scenarios.