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

Wouldnt a better a approach to be to have a table that stores DateTime of access, page id, ip address etc etc. Then every time a page is access you simply add a row to the table. That will give you the data at a raw level and then you can simply aggregate it to answer the questions that you want.

Storing the data in this way also allows you to answer more granular questions like how many page views were made on a particular day or week? Which you wouldn't be able to do with the table structure you have purposed in your question.

Answer 2

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).