hi all!

i have a toplist website which has about 600 members site and about 160k unique hit.

my ip count table looks like;

CREATE TABLE `ip` (
`ip` varchar(27) NOT NULL default '',
`date` varchar(10) default NULL,
PRIMARY KEY (`ip`)
) TYPE=MyISAM;

i insert the ip address as $userid_$ip so i can not insert 2 entries for a single ip address.

but my system load is high at lots of times in a day. so i wonder if it will be better to create a seperate table for each web site and put their visitors ip address to them.

or any other suggestion to me ?

my server is celeron 1.3 and 1GB of ram.

best.

I don't know that it will help all that much. You're still going to be doing the same number of inserts into the database. Now if your load is from finding IPs or something where you're selecting a subset that might speed up by separating to various tables.

what about if i am going to have more than 1000 web site members. is there any problem if i have more than 1000 tables ?

CREATE TABLE `ip` (
`ip` varchar(27) NOT NULL default '',
`date` varchar(10) default NULL,
PRIMARY KEY (`ip`)
) TYPE=MyISAM;


This is fundamentally wrong for a number of reasons.

1) You are not using appropriate table types. A date is a DATE or DATETIME, not varchar(10).

2) IP's arent really varchar data either, or they really dont have to be.

I think the major problem you have here is NOT that mysql is slow, but that you are causing it unnecessary workload by using inappropriate column types that are using (wasting) a lot of space on disk.

Now, here is my solution for you:


CREATE TABLE `ip` (
`ip` INT UNSIGNED NOT NULL default 0,
eventdate DATE default NULL,
PRIMARY KEY (`ip`)
) TYPE=MyISAM;


You will notice I changed `date` to eventdate. Date is a reserved ANSI SQL work, which you should not get into the bad habits of using.

Also, you will notice I stored IP as an INT (unsigned). Why?

Storing INT's is very economical on space, and mysql provides two functions to convert IP addresses to and from integers.

Example:


mysql> SELECT INET_ATON('209.207.224.40');
-> 3520061480
mysql> SELECT INET_NTOA(3520061480);
-> '209.207.224.40'


So, when you insert IP's just ask Mysql to convert to numbers for you. I bet this speeds up your database quite nicely.

Finally:

what about if i am going to have more than 1000 web site members. is there any problem if i have more than 1000 tables ?

Do not even think of doing this without some other compelling reason. Its much faster to update one big table than a thousand tiny tables, plus backups, management, etc would be a nightmare.

I think eventdate is not necessary for me. i clear the table by crontab at 00:00am in everyday. so why would i need to use it.

Other stuffs are great for me. now i updated my table as your suggest. i hope these will decrease the system load.

thanks for your great helps.

best regards,
burak..