JeuWeb Créez votre jeu par navigateur !

Argorate · 09-03-2014, 06:28 PM

Bonjour,

je voulais savoir si quelqu'un a déjà pu faire des tests sur de gros volumes pour voir si une jointure explicite (EX: select * from user u LEFT JOIN quset q ON u.id = q.user_id) est plus optimisé/rapide qu'une jointure implicite dans un where (EX: select * from user u, quest q where u.id = q.user_id)?

Quel est la meilleur forme, pourquoi?

**Xenos** · (Modification du message : 09-03-2014, 07:48 PM par Xenos.)

JOIN/INNER JOIN sont du même ressort qu'une clause WHERE lorsque l'on joint deux tables, que ce soit une auto-jointure (joindre la table à elle-même) ou une jointure externe (joindre deux tables différentes).

Confirmé ici avec des détails intéressants là.

Sources
('tention y'en a long, et oui, ça pique les yeux Niahoo!)

Objectif: Récupérer tous les couples de cases voisines issues des tables 'cases' et 'casesTest'; ces deux tables sont des clones pour éviter d'être dans le cas de l'auto-jointure.
Edit:Le cas de l'auto-jointure aboutit finalement aux mêmes résultats.

Résultats (secondes):
'XY - INNER - IN' => float 15.29901099205
'XY - JOIN - IN' => float 15.239926099777
'XY - WHERE - IN' => float 15.183449983597
'ID - INNER - IN' => float 15.045351028442
'ID - JOIN - IN' => float 15.039290189743
'ID - WHERE - IN' => float 14.952344894409
'XY - WHERE - EQUALS' => float 2.5882639884949
'XY - INNER - EQUALS' => float 2.5821270942688
'XY - JOIN - EQUALS' => float 2.5597670078278
'ID - JOIN - EQUALS' => float 1.7225079536438
'ID - INNER - EQUALS' => float 1.7209889888763
'ID - WHERE - EQUALS' => float 1.7205929756165

Requêtes correspondantes:

'XY - WHERE - IN' =>
string 'SELECT `case0`.`x`, `case0`.`y`, `case1`.`x`, `case1`.`y` FROM `cases` `case0`, `casesTest` `case1` WHERE`case1`.`id` IN (`case0`.`idVoisin1`, `case0`.`idVoisin2`, `case0`.`idVoisin3`, `case0`.`idVoisin4`)' (length=205)

'XY - INNER - IN' =>
string 'SELECT `case0`.`x`, `case0`.`y`, `case1`.`x`, `case1`.`y` FROM `cases` `case0` INNER JOIN `casesTest` `case1` ON`case1`.`id` IN (`case0`.`idVoisin1`, `case0`.`idVoisin2`, `case0`.`idVoisin3`, `case0`.`idVoisin4`)' (length=212)

'XY - JOIN - IN' =>
string 'SELECT `case0`.`x`, `case0`.`y`, `case1`.`x`, `case1`.`y` FROM `cases` `case0` JOIN `casesTest` `case1` ON`case1`.`id` IN (`case0`.`idVoisin1`, `case0`.`idVoisin2`, `case0`.`idVoisin3`, `case0`.`idVoisin4`)' (length=206)

'XY - WHERE - EQUALS' =>
string 'SELECT `case0`.`x`, `case0`.`y`, `case1`.`x`, `case1`.`y` FROM `cases` `case0`, `casesTest` `case1` WHERE`case1`.`id`=`case0`.`idVoisin1`' (length=137)

'XY - INNER - EQUALS' =>
string 'SELECT `case0`.`x`, `case0`.`y`, `case1`.`x`, `case1`.`y` FROM `cases` `case0` INNER JOIN `casesTest` `case1` ON`case1`.`id`=`case0`.`idVoisin1`' (length=144)

'XY - JOIN - EQUALS' =>
string 'SELECT `case0`.`x`, `case0`.`y`, `case1`.`x`, `case1`.`y` FROM `cases` `case0` JOIN `casesTest` `case1` ON`case1`.`id`=`case0`.`idVoisin1`' (length=138)

'ID - WHERE - IN' =>
string 'SELECT `case0`.`id`, `case1`.`id` FROM `cases` `case0`, `casesTest` `case1` WHERE`case1`.`id` IN (`case0`.`idVoisin1`, `case0`.`idVoisin2`, `case0`.`idVoisin3`, `case0`.`idVoisin4`)' (length=181)

'ID - INNER - IN' =>
string 'SELECT `case0`.`id`, `case1`.`id` FROM `cases` `case0` INNER JOIN `casesTest` `case1` ON`case1`.`id` IN (`case0`.`idVoisin1`, `case0`.`idVoisin2`, `case0`.`idVoisin3`, `case0`.`idVoisin4`)' (length=188)

'ID - JOIN - IN' =>
string 'SELECT `case0`.`id`, `case1`.`id` FROM `cases` `case0` JOIN `casesTest` `case1` ON`case1`.`id` IN (`case0`.`idVoisin1`, `case0`.`idVoisin2`, `case0`.`idVoisin3`, `case0`.`idVoisin4`)' (length=182)

'ID - WHERE - EQUALS' =>
string 'SELECT `case0`.`id`, `case1`.`id` FROM `cases` `case0`, `casesTest` `case1` WHERE`case1`.`id`=`case0`.`idVoisin1`' (length=113)

'ID - INNER - EQUALS' =>
string 'SELECT `case0`.`id`, `case1`.`id` FROM `cases` `case0` INNER JOIN `casesTest` `case1` ON`case1`.`id`=`case0`.`idVoisin1`' (length=120)

'ID - JOIN - EQUALS' =>
string 'SELECT `case0`.`id`, `case1`.`id` FROM `cases` `case0` JOIN `casesTest` `case1` ON`case1`.`id`=`case0`.`idVoisin1`' (length=114)

Source PHP:



<?php

	set_time_limit(120);

	$mysqli = new mysqli('127.0.0.1', 'root', '', 'eclerd_earth');

	$resultats = array();

	$queries = array();

	function test($id, $req)

	{

		global $mysqli, $resultats, $queries;

		$s = microtime(true);

		$r = $mysqli->query($req);

		$resultats[$id] = microtime(true) - $s;

		$queries[$id] = $req;

	}

	function testAll($nom, $selection)

	{

		test(

			$nom.' - WHERE - IN',

			'SELECT '.$selection

			.' FROM `cases` `case0`, `casesTest` `case1`'

			.' WHERE'

				.'`case1`.`id` IN (`case0`.`idVoisin1`, `case0`.`idVoisin2`, `case0`.`idVoisin3`, `case0`.`idVoisin4`)'

			);

		test(

			$nom.' - INNER - IN',

			'SELECT '.$selection

			.' FROM `cases` `case0`'

			.' INNER JOIN `casesTest` `case1`'

			.' ON'

				.'`case1`.`id` IN (`case0`.`idVoisin1`, `case0`.`idVoisin2`, `case0`.`idVoisin3`, `case0`.`idVoisin4`)'

		);

		test(

			$nom.' - JOIN - IN',

			'SELECT '.$selection

			.' FROM `cases` `case0`'

			.' JOIN `casesTest` `case1`'

			.' ON'

				.'`case1`.`id` IN (`case0`.`idVoisin1`, `case0`.`idVoisin2`, `case0`.`idVoisin3`, `case0`.`idVoisin4`)'

		);

		test(

			$nom.' - WHERE - EQUALS',

			'SELECT '.$selection

			.' FROM `cases` `case0`, `casesTest` `case1`'

			.' WHERE'

				.'`case1`.`id`=`case0`.`idVoisin1`'

			);

		test(

			$nom.' - INNER - EQUALS',

			'SELECT '.$selection

			.' FROM `cases` `case0`'

			.' INNER JOIN `casesTest` `case1`'

			.' ON'

				.'`case1`.`id`=`case0`.`idVoisin1`'

		);

		test(

			$nom.' - JOIN - EQUALS',

			'SELECT '.$selection

			.' FROM `cases` `case0`'

			.' JOIN `casesTest` `case1`'

			.' ON'

				.'`case1`.`id`=`case0`.`idVoisin1`'

		);

	}

	testAll('XY', '`case0`.`x`, `case0`.`y`, `case1`.`x`, `case1`.`y`');

	testAll('ID', '`case0`.`id`, `case1`.`id`');

	arsort($resultats);

	var_dump($resultats);

	var_dump($queries);

?>

MySQL utilisé:
Nom de table: cases (casesTest en est un clone)

Colonnes:

id: int(10)
x: smallint(5)
y: int(10)
altitude: bigint(20)
poisson: bigint(20)
foret: bigint(20)
eau: bigint(20)
co2: bigint(20)
chaleur: bigint(20)
idVoisin1: int(10)
idVoisin2: int(10)
idVoisin3: int(10)
idVoisin4: int(10)
mer: bit(1)

Indexes:

PRIMARY BTREE, Unique, Non compressé, (id:516960), Interclassement A, NOT NULL
BTREE, Unique, Non compressé, (x:,y:516960), Interclassement A, NOT NULL

Espace utilisé: 51095Kio dont 37863Kio de données, 13232Kio d'index

Statistiques

Format: statique
Lignes: 516 960
Longueur de ligne ø: 75
Taille de la ligne ø: 101 o
Prochain index automatique: 516 961

Myrina · 10-03-2014, 11:57 AM

(09-03-2014, 07:23 PM)Xenos a écrit : 'XY - JOIN - EQUALS' => float 2.5597670078278
'ID - JOIN - EQUALS' => float 1.7225079536438

La différence de temps entre la récupération que de la PRIMARY KEY et la récupération des colonnes XY est assez impressionnante