Connect.gt

Date un occhiata al link che segue per scoprire i vari tipi MySQL come char, enum, set, binary e come creare una funzione hash a 32 bit direttamente in MySQL.

Importare il DB da PHPStats

Teoria

La progettazione del database non segue appieno i canoni della cosidetta terza forma normale. Perchè i vincoli dati dai comuni hoster impongono la massima velocità per ogni utente e la minima occupazione di spazio (sotto windows un software analogo al nostro bloccava il sistema su diversi hoster, anche con appena 300 utenti).

PHPStats utilizza numerose tabelle, mantenendo in esse anche le informazioni aggregate. Non viene quindi mai eseguita una select con group by o simili. Ciò oltre a replicare informazioni crea la necessità di numerose query in scrittura con modifica di numerosi settori dell'hard disk. Per un programma di statistiche il numero accessi in scrittura (log utente) è decisamente superiore a quello in lettura (consultazione statistiche).

Salvare i dati aggregati (e aggiornarli continuamente) rende l'idea dello spreco di risorse fatto per un sistema che fa mille scritture e una sola lettua. Usare più tabelle separate (senza duplicazione di informazioni) non crea rallentamenti in scrittura (è come scrivere su due file invece che su uno soltanto la stessa quantità di informazioni). Mentre salvare il totale dei visitatori lo crea eccome.

L'ideale sarebbe loggare solo su detail e aggiornare le altre tabelle ogni tot di tempo tramite simil-cronjob.

Sono necessari diversi benchmark e una progettazione attenta, poichè sebbene una simile progettazione sarebbe molto più veloce nel registrare ogni utente, si dovrebbe conservare tutta la tabella detail. Che arriverebbe ben presto a dimensioni non proprio contenute per un hosting (700.000 record/righe all'anno supera i 50 MB di aruba).

Inoltre è necessario definire gli indici su praticamente tutte le colonne (che vengono aggiornati automaticamente ad ogni scrittura). Chiaramente usando insert delayed, o inserimenti bulk o aggiornando gli indici via cronjob (non so se si può disattivare l'aggiornamento) la cosa diventa più fattibile. Se poi si normalizza ta tabella sostituendo ad ogni page, risoluzione, ecc. un numero (tramite una funzione di compressione tipo hash ma reversibile: res = hash(1024x768)) la cosa diventa fattibile. Questa la useremo in ogni caso.

In pratica

-- phpMyAdmin SQL Dump
-- version 2.9.1.1
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generato il: 08 Giu, 2007 at 05:35 PM
-- Versione MySQL: 5.0.37
-- Versione PHP: 5.2.0
-- 
-- Database: `stats`
-- 

-- --------------------------------------------------------

-- 
-- Struttura della tabella `browser_list`
-- 

CREATE TABLE `browser_list` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `browser` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `browser` (`browser`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

-- 
-- Struttura della tabella `domains_list`
-- 

CREATE TABLE `domains_list` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `domain` varchar(255) NOT NULL,
  `subdomain` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `domain` (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

-- 
-- Struttura della tabella `pages_list`
-- 

CREATE TABLE `pages_list` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `url` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `url` (`url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

-- 
-- Struttura della tabella `pagineviste`
-- 

CREATE TABLE `pagineviste` (
  `id` bigint(20) unsigned NOT NULL,
  `id_visite` bigint(20) unsigned NOT NULL,
  `page` int(10) unsigned NOT NULL,
  `referer` int(10) unsigned NOT NULL,
  `dominio_pagina` int(10) unsigned NOT NULL,
  `page_parameters` varchar(255) NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `time` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;

-- --------------------------------------------------------

-- 
-- Struttura della tabella `referer_list`
-- 

CREATE TABLE `referer_list` (
  `id` int(10) unsigned zerofill NOT NULL auto_increment,
  `url` varchar(255) NOT NULL,
  `domain` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

-- 
-- Struttura della tabella `visite`
-- 

CREATE TABLE `visite` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `data` date NOT NULL,
  `ip` int(10) unsigned NOT NULL,
  `resolution` tinyint(3) unsigned NOT NULL,
  `color` tinyint(3) unsigned NOT NULL,
  `browser` smallint(5) unsigned NOT NULL,
  `os_useragent` smallint(5) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `ip_data` USING HASH (`data`,`ip`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Link utili

Dimensioni dati sul sito ufficiale di MySQL

Dimensione dei tipi di dato più usati

INSERT DELAYED, REPLACE e INSERT con SELECT