Webdigity Webmaster Forums - how to get an overhead?

olaf

Fri 11 August 2006, 11:26 am GMT +0300

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?

Nikolas

Fri 11 August 2006, 11:33 am GMT +0300

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

;)

olaf

Fri 11 August 2006, 11:59 am GMT +0300

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?

olaf

Fri 11 August 2006, 12:01 pm GMT +0300

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

Nikolas

Fri 11 August 2006, 12:04 pm GMT +0300

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

olaf

Fri 11 August 2006, 12:17 pm GMT +0300

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

Nikolas

Fri 11 August 2006, 12:24 pm GMT +0300

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

olaf

Fri 11 August 2006, 12:29 pm GMT +0300

Quote

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

Nikolas

Fri 11 August 2006, 12:36 pm GMT +0300

Good luck then....

:)