how to get an overhead?

Hello,

I know that a optimized table layout reduce the overhead for a n table.

I have this table to collect some visitor stats:

Code:

CREATE TABLE `visits` (
  `id` int(11) NOT NULL auto_increment,
  `ip_adr` varchar(15) NOT NULL default '',
  `referer` varchar(250) NOT NULL default '',
  `country` char(2) NOT NULL default '',
  `client` varchar(100) NOT NULL default '',
  `visit_date` date default NULL,
  `time` time NOT NULL default '00:00:00',
  `on_page` varchar(100) NOT NULL default '',
  `hostname` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;

I use this table in several website and there are not really problems, but now I noticed a big overhead (592,816     Bytes), how does this happen?

[Join us @ facebook]

Do you use UPDATE or DELETE statements?

BTW as you are using this table only for logging the overhead is not a real problem, and you can allways use a tool like this

yes, I delete one time a month the records which are older then 12 month, I see that this is the problem...

I read this data also vor statistics is this a reason that the whole thing will slow down?

for example this query need several records to execute 300.000 records

SELECT ip_country.country AS variable, COUNT(*) AS value FROM visits AS tbl LEFT JOIN ip2nationCountries AS ip_country ON ip_country.code = tbl.country WHERE tbl.country <> ''

[Join us @ facebook]

The reason for slow down is that you use grouping functions when you show up the stats.

The why to do this faster is JOIN most of those data with other tables. Eg. the referer and the client fields.

But the real way to do this fast is to use some form of caching like awstats do, because in any way the parsing of this data is a slow process....

yes that's true the sql statement wasn't complete:

SELECT ip_country.country AS variable, COUNT(*) AS value FROM visits AS tbl LEFT JOIN ip2nationCountries AS ip_country ON ip_country.code = tbl.country WHERE tbl.country <> '' AND hostname = 'www.finalwebsites.com' GROUP BY tbl.country ORDER BY value DESC LIMIT 0, 10

I'm using group, but how to optimize the table with joins I have only two tables...

[Join us @ facebook]

You have to split it to more tables...

And you can start by the hostname. Insted of using the actual hostname you will use the id of it in the hostnames (index that field)

Actually that was the reason that I stop developing my statistics program....

Actually that was the reason that I stop developing my statistics program....

that sounds not good,

last week I wrote a function to limit the visitors access...

[Join us @ facebook]

Good luck then....

[Guest]

[62.865]

Googlebot, Yahoo crawler, Msnbot