MySQL Performance: Many tables or many rows?

Question

I want to log access to pages in my PHP/MySQL app to implement a view count similar to the one on SO. My plan is to count the requests by unique IP addresses on each page. There about 5000 different pages with a view-count. (I know counting IPs is not exact but that is OK for my purposes.)

I see two options to do organize the database tables:

• Either one large table with the fields “page_id”, “request_ip”. Assuming each page has 50 views by unique IPs on average, I'd get 5000 x 50 = 250 000 rows. As the views are displayed on the pages, the table will have read and write access for each request on each page.
• The other option is to have one table per page with a single column “request_ip”. I'd then have 5000 tables storing 50 rows on average. A table will only get accessed when it's page is viewed.

Which one is better generally and performance wise? Or am I completely on the wrong track?

Answer 1

5000 tables means 5000 different queries + 5000 different sets of index + 5000 different sets of data competing for space in the server's caches. Performance will most likely be abysmal.

Multiple tables storing exactly the same data structure is almost ALWAYS a bad design. If you're worried about performance, you can use MySQL's partitioning support to split the table into multiple pieces automatically, and that's done transparently to the end-user (eg. your queries).