how to get an overhead?

[Free WordPress Themes]

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?

[Promote your blog for free....]

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

[Free WordPress Themes]

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?

[Free WordPress Themes]

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 <> ''

[Promote your blog for free....]

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....

[Free WordPress Themes]

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...

[Promote your blog for free....]

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....

[Free WordPress Themes]

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...

[Promote your blog for free....]

Good luck then....

[Guest]

[34.929]

Googlebot, Yahoo crawler, Baidu Spider