Créé table temporaire en PHP/SQL

Si vous avez a travaillé avec des base de données relationnel volumineuse et que vos requêtes SQL prennent une éternité a ce faire vous devriez pensé à utilisé des tables temporaires.
 

Les principes d'optimisations avec des tables temporaires

• Le fait de faire plusieurs jointures implique la prise en charge des enregistrements de chaque table afin d'en faire le filtre et le trie.  Avec des table de +500 000 enregistrements le temps de calcul de la requêtes SQL peux prendre +30 secondes ce qui occasionne plusieurs problèmes.
• L'utilisation de table temporaire dans une base de données volumineuse sert a répartir la charge de travail de la requêtes SQL en deux ou plusieurs requêtes avec l'utilisation de table temporaire pour enregistrer les données pré-filtrer. 
• La création de table temporaire ce fait extrêmement rapidement et n'utilise que de la mémoire vive.
• La table temporaire existe seulement durant le temps d'exécution du script PHP et est effacer automatiquement après.

ATTENTION: l'utilisation de table temporaire ne va pas automatiquement augmenter la rapidité d'exécution du script, tout dépend de son utilisation et il est toujours souhaité lors d'optimisation de comparer les temps d'exécutions pour utilisé le script le plus performant.

Exemple concret d'optimisation avec table temporaire

Exemple d'un site de vente en ligne avec des produits, des items (liste des produits de la commande) et les commandes.

SQL pour trouver tout les items acheter pour le membre id=5

Voici la technique standard une requête SQL pour trouver le résultat

select item.quantite, item.prix_unitaire, item.total, commande.id, commande.statut
from item, commande, produit
where commande.id_membre = 5
and item.id_commande = commande.id
and produit.id = item.id_produit
order by commande.date_cree, item.ordre

Advenant le cas ou cette requête prend une éternité a ce faire utilisé plutôt la technique de la table temporaire

SQL création table temporaire de pré-filtrage
create temporary table commande_temp
                    as
                        select *
                        from commande
                        where id_membre = 5

Par la suite requete complète avec utilisation résultats pré-filtrer
select item.quantite, item.prix_unitaire, item.total, commande.id, commande.statut
                    from item, commande_temp as commande, produit
                    where item.id_commande = commande.id
                    and produit.id = item.id_produit
                    order by commande.date_cree, item.ordre


Code PHP de l'exemple

/*
* cree table temporaire
* @since 2013-01-21 19:02:04
* @autor Pascal
*/

$sql_temporaire = " create temporary table commande_temp
                    as
                        select *
                        from commande
                        where id_membre = 5   ";

$rep_temporaire = mysql_query($sql_temporaire)
      or die ("Erreur sql_temporaire:$sql_temporaire.mysql_error().);


$sql_liste_item = " select item.quantite, item.prix_unitaire, item.total, commande.id, commande.statut
                    from item, commande_temp as commande, produit
                    where item.id_commande = commande.id
                    and produit.id = item.id_produit
                    order by commande.date_cree, item.ordre";

$rep_liste_item = mysql_query($sql_liste_item)
                     or die ("Erreur sql_liste_item:$sql_liste_item".mysql_error());
                  
$nb_liste_item = mysql_num_rows($rep_liste_item);

while($list_liste_item = mysql_fetch_array($rep_liste_item))
{
            extract($list_liste_item);
            print "Commande #$id, item:$quantite * $prix_unitaire = $total ";

 }
mysql_free_result($rep_liste_item);
unset($rep_liste_item);

Article écrit le 25 janvier 2013
Article modifié le 29 janvier 2013