On the whatismyipaddress.com website I’ve got a rolling history of IP address, user-agent, date, and a flag for each visit. Initially user-agent was stored in the table as a fairly long string. After several hundred thousand records I notice significant db performance delays. Since I didn’t really need to know what the user-agent was I started to store a 32-byte hash of the user-agent. This helped till the table exceeded 1.5 million records. Currently the IP address is stored as VARCHAR(15). I’m thinking of switching to storing the ip2long (IP as decimal) as an unsigned INT to see if that makes things faster. MySQL should be able to index an int better than 15 bytes of text.
2 Responses to “DB Issues”
-
Chrispcritters Says:
December 10th, 2008 at 7:32 pmSo, looks like that might not have been the issue. Some advertising is pulling better than expected. I’ve pull some of the more DB intensive items off the whatismyipaddress.com homepage (latest forum posts [now there is a link to more of them], the polls, and some of the plugings for geeklog). I’m thinking that I may have to look into some DB caching functionality.
-
Chrispcritters Says:
December 15th, 2008 at 12:26 pmDoubling all MySQL caches has improved performance.
Recent Comments