Bonjour,

Sur mon site j'ai un petit système de statistiques de fréquentation en plus de Google Analytics.

Ça marche avec une table visites qui compte les visites (IP) avec quelques autres informations :

CREATE TABLE visites (
  ip varchar(30) NOT NULL,
  date date NOT NULL,
  timestamp_start int(10) unsigned NOT NULL DEFAULT '0',
  timestamp_end int(10) unsigned NOT NULL,
  host text NOT NULL,
  referer_visite text NOT NULL,
  cpt_vues smallint(5) unsigned NOT NULL,
  is_archived tinyint(4) NOT NULL,
  KEY id (ip)
) ENGINE=MyISAM;

-- et non PRIMARY KEY (ip)

Le tout est découpé PAR JOUR.
Ça veut dire que si une même IP vient le 20 mars et le 21 mars, il y aura 2 entrées dans la table : une pour le 20 mars et une autre pour le 21.

Mon objectif est d'optimiser le temps d'exécution de ces requêtes (parce qu'elles sont appelées à chaque page vue) :

Détection nouvelles visites

SELECT ip FROM visites WHERE date = CURDATE() AND ip='127.0.0.1'

Mise à jour à chaque page vue

UPDATE visites
SET cpt_vues = cpt_vues + 1, timestamp_end = 1299956232
WHERE ip = '127.0.0.1' AND date = CURDATE()

Donc je me demande quel index utiliser.
Actuellement j'ai un index PRIMARY sur le champ ip, et je me demande si ça ne serait pas mieux d'avoir à la place un index PRIMARY sur 2 colonnes : ip et date.

Est-ce que ça serait plus rapide ?

Autre point : l'espace disque, est ce qu'un index sur 2 colonnes prend significativement plus de place d'un index sur 1 colonne ?

Merci

La question à se poser en BDD quand on crée les index c'est : de quoi sont dépendantes les informations, cad : quel champ ou couple de champ donne accès à une entrée unique.

Là typiquement si tu met un PRIMARY sur ip tu vas forcément avoir un problème : si quelqu'un se ramène 2 jours différents avec la même IP comment fais-tu ? Tu aura une contrainte qui ne sera pas resolvable et la BDD vas refuser d'ajouter ton entrée. Donc là dans ton cas chaque champ est dépendant d'un couple d'information : l'ip et la date. Par conséquent la question ne se pose pas : Il faut une clef primaire composée des 2 champs ip et date.

-_-'

Je viens de me rendre compte que j'ai effectivement écrit n'importe quoi dans le CREATE TABLE.

Actuellement il n'y a pas de clé primaire, il y a juste un index sur le champ IP (un index simple, de type BTREE).

Donc du coup je repose ma question :

Vaut il mieux un index simple sur le champ ip, ou un index PRIMARY sur ip, date ?
(avantages/inconvénients sur les performances et l'espace disque)

Un "index primary", ça n'existe pas. La clé primaire est le plus petit sous-ensemble de colonnes permettant d'identifier les lignes de manières unique. Que les SGBDR placent un index automatiquement sur ce qui constitue la clé primaire, c'est cool, mais c'est un détail d'implémentation et ça n'a aucun rapport avec le concept.

Ta table a besoin d'une clé primaire, ce qui la constitue est à voir en fonction de l'énoncé. S'il y a bien une ligne par ip et par jour, la clé primaire doit porter sur (ip, date) ou sur (date, ip) selon la distribution et les requêtes.

En ce qui concerne les index, un index sur deux colonnes est évidemment plus volumineux qu'un index ne portant que sur une seule colonne puisque dans l'index ont trouve le contenu des colonnes indexées. Mais l'index sur plusieurs colonnes pourra offrir de meilleures performances. Prenons un exemple, une table avec deux colonnes de type int avec 10 000 tupples ((1, 1), (1, 2), (1, 3), ..., (100, 99), (100, 100)) et une requête avec un where a = 50 and b > 90.
Si on a un index portant sur (a), l'index permet de filtrer 9 900 lignes avec a = 50, il faudra donc lire les 100 restant pour accomplir le reste du where. Si à la place on avait un index portant sur (a, b), il permettrait de filtrer 9990 lignes, il n'y en aurait donc plus que 10 à lire. Et comme l'accès aux lignes n'est pas gratuit, moins on doit y accéder, plus performante est la requête. Le gain de performance peut être tel que parfois, on peut volontairement faire porter un index sur un plus grand nombre de colonnes pour que la seule lecture de l'index puisse répondre à une requête, ce qui permet de ne pas du tout devoir accéder aux données de la table et offrir des performances toujours plus folles, même si pour cela on sacrifie un peu d'espace disque.

Il ne faut pas nécessairement avoir peu d'avoir un volume d'index conséquent, je crois me souvenir d'une discussion avec une personne travaillant dans l'audit de base de données qu'en général, le volume d'index était de l'ordre de 40 à 60% du volume de données.

Pour la clé primaire il vaut mieux utiliser (date,ip) puisque l'index sera organisé par date, par conséquent les vieilles pages d'index pourront sortir du cache sans problème puisqu'on ne touche qu'à la date courante.

Sinon, tu peux alléger.

Le SELECT ne sert à rien : tu peux utiliser INSERT ON DUPLICATE KEY UPDATE ou bien vérifier si l'UPDATE renvoie une ligne ou pas.

Aussi, stocke toutes les infos dans la session, et ne mets la BDD à jour que toutes les 5 minutes par exemple. Cela évite de le faire à chaque page.

Ouais super !
Je ne connaissais pas le INSERT ON DUPLICATE KEY UPDATE, et ça m'embêtait de faire volontairement une requête UPDATE avec un échec potentiel (le tuple n'existe pas), puis de tester le retour pour faire un INSERT en cas d'échec.

Ça marche depuis quelle version de MySQL ?

D'après la doc, c'était déjà là dans la version 3.23.