-- phpMyAdmin SQL Dump
-- version 3.2.0.1
 
--
-- Structure de la table `balises`
--
 
CREATE TABLE IF NOT EXISTS `balises` (
  `id` int(11) NOT NULL,
  `nom` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `nom` (`nom`)
) ;
 
 
--
-- Structure de la table `documents`
--
 
CREATE TABLE IF NOT EXISTS `documents` (
  `nom` varchar(255) NOT NULL,
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `nom` (`nom`)
) ;
 
 
--
-- Structure de la table `mots`
--
 
CREATE TABLE IF NOT EXISTS `mots` (
  `id` int(11) NOT NULL,
  `mot` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ;
 
 
--
-- Structure de la table `noeuds`
--
 
CREATE TABLE IF NOT EXISTS `noeuds` (
  `id` int(11) NOT NULL,
  `pere` int(11) DEFAULT NULL,
  `document` int(11) NOT NULL,
  `balise` int(11) NOT NULL,
  `pre` int(11) NOT NULL,
  `post` int(11) NOT NULL,
  `index` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`pere`) REFERENCES `noeuds`(`id`),
  FOREIGN KEY (`document`) REFERENCES `documents`(`id`),
  FOREIGN KEY (`balise`) REFERENCES `balises`(`id`)
);
 
 
--
-- Structure de la table `references`
--
 
CREATE TABLE IF NOT EXISTS `references` (
  `noeud` int(11) NOT NULL,
  `mot` int(11) NOT NULL,
  `nbRef` int(11) NOT NULL,
  FOREIGN KEY (`mot`) REFERENCES `noeuds`(`id`),
  FOREIGN KEY (`noeud`) REFERENCES `noeuds`(`id`)
);
 

SELECT DISTINCT documents.id
 , documents.nom
 , balises.id
 , balises.nom
 , noeudsall.id
 , noeudsall.pere
 , noeudsall.pre
 , noeudsall.post
 , noeudsall.`index`
FROM documents, balises, noeuds AS noeudsall, noeuds AS noeudsref, `references`, mots
WHERE ((mots.mot IN ( 'voyage', 'lourdes' ))
 AND (mots.id = `references`.mot)
 AND (`references`.noeud = noeudsref.id)
 AND (noeudsall.document = noeudsref.document)
 AND (noeudsall.pre <= noeudsref.pre)
 AND (noeudsall.post >= noeudsref.post)
 AND (noeudsall.document = documents.id)
 AND (noeudsall.balise = balises.id))
ORDER BY noeudsall.id, balises.id, documents.id

id  select_type  table     type    possible_keys      key      key_len  ref                            rows    Extra
1     SIMPLE   references  ALL      mot,noeud         NULL     NULL     NULL                           200982  Using temporary; Using filesort
1     SIMPLE   noeudsref   eq_ref   PRIMARY,document  PRIMARY  4        ri_index.references.noeud      1    
1     SIMPLE   noeudsall   ref      document,balise   document 4        ri_index.noeudsref.document    13      Using where
1     SIMPLE   documents   eq_ref   PRIMARY           PRIMARY  4        ri_index.noeudsref.document    1    
1     SIMPLE   mots        eq_ref   PRIMARY           PRIMARY  4        ri_index.references.mot        1        Using where
1     SIMPLE   balises     eq_ref   PRIMARY           PRIMARY  4        ri_index.noeudsall.balise      1