Does MySQL have limits for the amount of rows?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Does MySQL have limits for the amount of rows?

Post by impulse() »

I had the idea to create a hit counter for my site by adding 1 to the number of rows in a table on each page loads. Will the table ever reach a limit? Is this generally bad scripting or not such a bad idea?
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

mysql is made to store millions (sometimes billions) of records. It's limits are more dependant on the server than on mysql itself (I believe)
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post by jayshields »

The limit varies on operating systems, it's in the millions/billions on any though.

Adding a row to a table on page hits is one of the worst ideas I've ever heard though. Just increment an integer in a field each page hit.

Code: Select all

UPDATE `table` SET `hits` = `hits`+1
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Tables can have hundreds upon hundreds of millions rows. The limitation is more dependant on your operating systems' limits.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post by jayshields »

Lol, we all posted at the same time with almost identical posts.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

wanna know why?

cuz we're freakin awesome! That's why!
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

I believe the limit is dependant on the max file size allowed on the operating system. So I wouldn't say it can hold X amount of rows, but rather X amount of data.

To the OP.. that scripting is terrible :P Do what was said above to simply increase an integer.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

scottayy wrote:To the OP.. that scripting is terrible :P Do what was said above to simply increase an integer.
Hate to buck the trend*, but I would do this (and do do it) the way the OP is suggesting. A simple incrementing number doesn't tell you anything beyond the number of times the page has been viewed. If you add a record per hit (with things like the session id and page url (inc. variables)) you can track exact path a user has taken through your site with the amount of time spent on each page. That's very useful information.



* I love to buck the trend.
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post by impulse() »

My plan is to add onto this to include IP address, time & date. Just need to increase my skillz to deal with the IP grabbing :)
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

To get the IP Address it is extremely simple... look in the manual at the $_SERVER superglobal (specifically $_SERVER['REMOTE_ADDR'])
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post by impulse() »

GM wrote:To get the IP Address it is extremely simple... look in the manual at the $_SERVER superglobal (specifically $_SERVER['REMOTE_ADDR'])
That worked a treat mate. I've gone back to logging each visitor as a row which includes the visitor number and IP. Does it get to the point in a PHP coders life when they stop patting themselves on the back for their creations because it all seems to have gotten old? I've only just started in the last few weeks and I find myself feeling really proud for the most simple things :)
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

I haven't been doing PHP for long (maybe 6 months, only as a hobby), and I'm still at the back-patting stage :)
Post Reply