storing large arrays

goobers

Hi,

I am currently trying to do the following:

Firstly, I am keeping track of unique views on certian pages on my website, by storing the ip address of the users who visit in array, serializing it, and storing it back in the database. Everytime a user visits a page, i pull the array, unserialize it, check if that user is already there. If not, I add his/her IP, and increment a counter called views.

Now this works fine and all, until you start getting a very very large number of views. (hundreds of thousands or more). What is a better way to keep track of unique visitors on a site?

Thanks

leatherback

have a table with visitors, each record one IP address in a unique field. I think the mysql command you need is: update into...

goobers

So essentially having hundreds of thousands of entries in a db is better than having a huge array ?

If that is the case, i'm not sure how I would use 'update into'.

Would i have to first

"select * from visitor_table where ip_address = '123.456.789.345' limit 1"

and check row count, then insert a new entry if there is none.

Is there a more efficient way of doing that ?

Edit:

I will actually be doing this per page (i.e. a news page, I want to keep track of unique visitors on each new article posted), won't the database grow very very quickly if I have a large number of visitors...

leatherback

the difference is that you are retrieving a large array, which you need to process (unserialize) and trawl through to get your answer. If you have the IP as a key in the table, mysql will be MUCH faster returning the answer.

I checked the manual. The command I was looking for is insert on duplicate key update or something:

*

  If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. The affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated. See Section 12.2.4.3, &#8220;INSERT ... ON DUPLICATE KEY UPDATE Syntax&#8221;.

Inserting into a table requires the INSERT privilege for the table. If the ON DUPLICATE KEY UPDATE clause is used and a duplicate key causes an UPDATE to be performed instead, the statement requires the UPDATE privilege for the columns to be updated.

and use the IP as a key.

I do not think there are faster options. If you store the IP adress as long intereger instead of a string, storage would be not that bad. I cleaned a table the other day with 8 fields, 450K records which was 30-something MB.

goobers

thanks leatherback,

one last issue however.. lets say I also wanted to keep track of users who viewed a document, (I can then have a field called uid's or similar).. however, I would only want one unique entry for each user or IP. meaning if a single user logs in from multiple locations it will only make one entry. Using ON DUPLICATE KEY UPDATE would mean I would have to set the ip field as the primary key. How would I deal with the fact that I need only one entry per user/ip. Is it possible to have two unique fields, and if so, how would that translate to ON DUPILCATE KEY

thanks again

goobers

Ok, I've made both fields unique and it does indeed do what I just mentioned. However there is another issue I have not thought of, I will need a set of unique ips/uids per article.. meaning that there will be duplicate entries, just not for a given articleid.

Now I know I can do it by a normal select then insert. But in that case the table will not have a primary key/unique ID.

Is there a way to have a conditional insert (like insert if uid <> '123' and ip <> '123.456.789.123' where articleid = 456 ) ?

thanks

leatherback

I would probably have one table keep track of the IP numbers, and another table dealing with the pages. You will have to have multiple queries though. On the bright side: You could set the user IP -> ID in a session, and then subsequent pages you only need to insert into pages (page, user)

bradgrafelman

Just add another column - articleID - and make a single UNIQUE key apply to both the IP and articleID columns jointly (not two separate UNIQUE keys).