MySQL JOIN időköltsége (Hogyan ésszerűbb lekérdezni?)

laji · 2009. Okt. 8. (Cs), 11.48

Üdv mindenkinek!

Alapvetőnek tűnik, de nem sikerült választ találnom rá. A MySQL "EXPLAIN EXTENDED SELECT..." eredményét nem igazán sikerült értelmeznem.

Adott egy MySQLadattár:

SET NAMES utf8;
CREATE TABLE `zokni` (
`SORSZAM` tinyint(4) unsigned NOT NULL default '0',
`FAJTAKULCS` tinyint(2) unsigned NOT NULL default '0',
`SZINKULCS` tinyint(2) unsigned NOT NULL default '0',
`LEIRAS_1` varchar(45) COLLATE utf8_general_ci NOT NULL COMMENT 'ide jöhet pl. a gyártó',
`LEIRAS_2` text COLLATE utf8_general_ci NOT NULL COMMENT 'ecsetelgetem, milyen szép is ez',
`AR` tinyint(4) unsigned NOT NULL default '0' COMMENT 'az ár forintban',
PRIMARY KEY (`SORSZAM`)
KEY `FAJTAKULCS` (`FAJTAKULCS`),
KEY `SZINKULCS` (`SZINKULCS`),
) ENGINE=MyISAM COLLATE=utf8_general_ci;
INSERT INTO `zokni` VALUES (1, 1, 2, 'Egyesült Zokniművek Rt.', 'csodás benne a séta', 800);
INSERT INTO `zokni` VALUES (2, 2, 1, 'Alsókukutyinfalvi Zoknikunyhó', 'szinte ingyen', 100);
# ... stb. (3,000 tétel)
CREATE TABLE `fajta` (
`FAJTAKULCS` tinyint(2) unsigned NOT NULL default '0',
`FAJTA` varchar(128) COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`FAJTAKULCS`)
) ENGINE=MyISAM COLLATE=utf8_general_ci;
INSERT INTO `fajta` VALUES (1, 'rövid szellős tavaszi');
INSERT INTO `fajta` VALUES (2, 'hosszú vastag téli');
# ... stb.
CREATE TABLE `szin` (
`SZINKULCS` tinyint(2) unsigned NOT NULL default '0',
`SZIN` varchar(128) COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`SZINKULCS`)
) ENGINE=MyISAM COLLATE=utf8_general_ci;
INSERT INTO `szin` VALUES (1, 'piros, zöld pöttyökkel');
INSERT INTO `szin` VALUES (2, 'sárga, szürke csíkokkal');
# ... stb.

SET NAMES utf8;
CREATE TABLE `zokni` (
	`SORSZAM`	tinyint(4) unsigned NOT NULL default '0',
	`FAJTAKULCS`	tinyint(2) unsigned NOT NULL default '0',
	`SZINKULCS`	tinyint(2) unsigned NOT NULL default '0',
	`LEIRAS_1`	varchar(45) COLLATE utf8_general_ci NOT NULL COMMENT 'ide jöhet pl. a gyártó',
	`LEIRAS_2`	text COLLATE utf8_general_ci NOT NULL COMMENT 'ecsetelgetem, milyen szép is ez',
	`AR`	tinyint(4) unsigned NOT NULL default '0' COMMENT 'az ár forintban',
	PRIMARY KEY (`SORSZAM`)
	KEY `FAJTAKULCS` (`FAJTAKULCS`),
	KEY `SZINKULCS` (`SZINKULCS`),
) ENGINE=MyISAM COLLATE=utf8_general_ci;
INSERT INTO `zokni` VALUES (1, 1, 2, 'Egyesült Zokniművek Rt.', 'csodás benne a séta', 800);
INSERT INTO `zokni` VALUES (2, 2, 1, 'Alsókukutyinfalvi Zoknikunyhó', 'szinte ingyen', 100);
# ... stb. (3,000 tétel)

CREATE TABLE `fajta` (
	`FAJTAKULCS`	tinyint(2) unsigned NOT NULL default '0',
	`FAJTA`	varchar(128) COLLATE utf8_general_ci NOT NULL,
	PRIMARY KEY (`FAJTAKULCS`)
) ENGINE=MyISAM COLLATE=utf8_general_ci;
INSERT INTO `fajta` VALUES (1, 'rövid szellős tavaszi');
INSERT INTO `fajta` VALUES (2, 'hosszú vastag téli');
# ... stb.

CREATE TABLE `szin` (
	`SZINKULCS`	tinyint(2) unsigned NOT NULL default '0',
	`SZIN`	varchar(128) COLLATE utf8_general_ci NOT NULL,
	PRIMARY KEY (`SZINKULCS`)
) ENGINE=MyISAM COLLATE=utf8_general_ci;
INSERT INTO `szin` VALUES (1, 'piros, zöld pöttyökkel');
INSERT INTO `szin` VALUES (2, 'sárga, szürke csíkokkal');
# ... stb.

A 3,000 tételből meg kell jelenítenem 20-at egy lapon. Mi a gyorsabb megoldás, mi használ kevesebb erőforrást?

1. lehetőség

<?php
$eredmény_zokni=mysql_query("
SELECT zokni.*,
fajta.FAJTA,
szin.SZIN
FROM zokni LEFT JOIN (fajta, szin)
ON(
fajta.FAJTAKULCS=zokni.FAJTAKULCS
AND
szin.SZINKULCS=zokni.SZINKULCS
)
ORDER BY SORSZAM
LIMIT 0, 20
");
while ($sor_zokni=mysql_fetch_assoc($eredmény_zokni)) {
echo $sor_zokni["SORSZAM"]
.$sor_zokni["FAJTA"]
.$sor_zokni["SZIN"]
.$sor_zokni["LEIRAS_1"]
.$sor_zokni["LEIRAS_2"]
.$sor_zokni["AR"];
}
?>

<?php
$eredmény_zokni=mysql_query("
	SELECT	zokni.*,
				fajta.FAJTA,
				szin.SZIN
	FROM		zokni LEFT JOIN (fajta, szin)
				ON(
					fajta.FAJTAKULCS=zokni.FAJTAKULCS
					AND
					szin.SZINKULCS=zokni.SZINKULCS
				)
	ORDER BY	SORSZAM
	LIMIT		0, 20
");
while ($sor_zokni=mysql_fetch_assoc($eredmény_zokni)) {
	echo	 $sor_zokni["SORSZAM"]
			.$sor_zokni["FAJTA"]
			.$sor_zokni["SZIN"]
			.$sor_zokni["LEIRAS_1"]
			.$sor_zokni["LEIRAS_2"]
			.$sor_zokni["AR"];
}
?>

2. lehetőség

<?php
$eredmény_zokni=mysql_query("
SELECT *
FROM zokni
ORDER BY SORSZAM
LIMIT 0, 20
");
while ($sor_zokni=mysql_fetch_assoc($eredmény_zokni)) {
$fajta=mysql_result(mysql_query("SELECT FAJTA FROM fajta WHERE FAJTAKULCS=".$sor_zokni["FAJTAKULCS"]),0)
$szín=mysql_result(mysql_query("SELECT SZIN FROM szin WHERE SZINKULCS=".$sor_zokni["SZINKULCS"]),0)
echo $sor_zokni["SORSZAM"]
.$fajta
.$szín
.$sor_zokni["LEIRAS_1"]
.$sor_zokni["LEIRAS_2"]
.$sor_zokni["AR"];
}
?>

<?php
$eredmény_zokni=mysql_query("
	SELECT	*
	FROM		zokni
	ORDER BY	SORSZAM
	LIMIT		0, 20
");
while ($sor_zokni=mysql_fetch_assoc($eredmény_zokni)) {
	$fajta=mysql_result(mysql_query("SELECT FAJTA FROM fajta WHERE FAJTAKULCS=".$sor_zokni["FAJTAKULCS"]),0)
	$szín=mysql_result(mysql_query("SELECT SZIN FROM szin WHERE SZINKULCS=".$sor_zokni["SZINKULCS"]),0)
	echo	 $sor_zokni["SORSZAM"]
			.$fajta
			.$szín
			.$sor_zokni["LEIRAS_1"]
			.$sor_zokni["LEIRAS_2"]
			.$sor_zokni["AR"];
}
?>

Előre is köszönöm a segítséget.

■

Adatbázisok

csirip

Harmadik lehetőség és megoldás

vbence · 2009. Okt. 8. (Cs), 12.35

Még egy versenyző:

SELECT zokni.*,
fajta.FAJTA,
szin.SZIN
FROM zokni, fajta, szin
WHERE
fajta.FAJTAKULCS=zokni.FAJTAKULCS
AND
szin.SZINKULCS=zokni.SZINKULCS
ORDER BY SORSZAM
LIMIT 0, 20

SELECT  zokni.*,  
                fajta.FAJTA,  
                szin.SZIN  
    FROM        zokni, fajta, szin  
    WHERE  
                fajta.FAJTAKULCS=zokni.FAJTAKULCS  
                AND  
                szin.SZINKULCS=zokni.SZINKULCS  
    ORDER BY    SORSZAM  
    LIMIT       0, 20

A megoldás pedig, használj profiligot.

Az általad vázolt második lehetőségtől sírni fognak az angyalok :) Legalább
SELECT * FROM szin és SELECT * FROM fajta legyen az elején, ami egy-egy tömbbe gyűjti a szótártábláidat, majd ezekből a tömbökből a megflelő kulcshoz tartozó értéket írd ki.
(Amit felvázoltál az 41 lekérdezés, amihez 41szer fog lefutni a mysql SQL értlemezője, 41szer nyitja meg a táblákat stb stb stb...)

A hozzászóláshoz regisztráció és belépés szükséges
új téma

Bocs, nem voltam gépközelben.

laji · 2009. Okt. 8. (Cs), 15.57

Élesben, bonyolultabb adattárral nekem a javasolt 3. megoldás rögrön kiesett. A MySQL nem is volt hajlandó lefuttatni, 1104-es hibával tért vissza:

Message: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

A tömbbe gyűjtés ésszerűnek tűnik, bár egy régebbi kérdésemre, mi rendez gyorsabban, a PHP vagy a MySQL, a MySQL volt a válasz - tehát lehet, hogy nem is nyernék a tömbökkel. Végül is, a 2. megoldás 2 kis MySQL-lekérdezése is elég rövid, egysoros dolog. Közben új adattár-kapcsolatot nyitni természetesen nem kell, az él.
A hivatkozást köszönöm, kipróbálom; nekem nem sikerült ilyesmire bukkannom. Azért közben szívesen veszem mások tapasztalatait is.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

Rendezés

vbence · 2009. Okt. 8. (Cs), 17.35

mi rendez gyorsabban

Ezt nem értem. Ki mondta, hogy PHP-ban kell rendezni?

A hozzászóláshoz regisztráció és belépés szükséges
új téma

Túl tömör voltam

laji · 2009. Okt. 8. (Cs), 17.50

Persze, rendezésről szó sincs. 1-2 éve kérdezősködtem itt egy találatsor eredményeinek rendezéséről (mert utf8-betűgondjaim voltak), s az akkori válaszokból azt szűrtem le, hogy a PHP sokoldalú, de ha nagy felsorolások kezeléséről van szó, akkor nagyságrendekkel lassabb, mint az arra kihegyezett adattárkezelő-nyelvek, tehát pl. a SQL. Másképp fogalmazva lebeszéltek arról, hogy adattömböket MySQL helyett PHP-vel faggassak - itt pedig épp erről lenne szó az ajánlatodban. Ezért merült fel bennem, hogy talán 41 MySQL-lekérdezés sem lesz lassabb, mint egyszer beolvasni egy PHP-tömbbe, s utána abból kérdezgetni le mindig.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

Szerinem

gphilip · 2009. Okt. 8. (Cs), 16.47

A második megoldásod semmiképpen sem javasolt.

Szerintem

SELECT zokni.*,
fajta.FAJTA,
szin.SZIN
FROM zokni
INNER JOIN fajta USING (FAJTAKULCS)
INNER JOIN szin USING (SZINKULCS)
ORDER BY SORSZAM
LIMIT 0, 20

INNER JOIN kulcsszavakkal sokkal átláthatóbb lesz a lekérdezés, és a JOIN-ok feltételei is egyértelműbbé válnak.

Ha rendesen indexelve van minden szükséges mező (FAJTAKULCS, SZINKULCS, SORSZAM), akkor ennek villámgyorsan le kell futnia egy 3000-es adatbázison (gondolom a fajták és sznek táblái nem túl nagyok)

A hozzászóláshoz regisztráció és belépés szükséges
új téma

Remélem, igazad van

laji · 2009. Okt. 8. (Cs), 18.08

..mert ez a szebb és kényelmesebb megoldás. A mutatóim [index] megvannak rendben, ezt igazolja a MySQL "EXPLAIN EXTENDED SELECT..." eredménye is. A fajták és színek táblái valóban nem nagyok, 100 sor alattiak.

Némi további magyarázat, miért is tetszene nekem is jobban a táblafűzéses megoldás [JOIN]: mert így pl. lehet rendezni a szín és a fajta szerint is rögtön ugyanabban az 1 lekérdezésben. Ez se megvetendő szempont.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

Valóban az 1. a jó megoldás.

laji · 2009. Okt. 9. (P), 18.40

Fényesen bevált az 1. megoldás, pörög a MySQL. Köszönöm a segítséget mindkettőtöknek!

Még annyit: a USING szerkezetet azért nem használom, mert szándékosan eleve nincsenek egyező oszlopnevek még különböző táblákban sem; tehát a valós tábláimban még a FAJTAKULCS és SZINKULCS oszlopokat is másképp neveztem el a különböző táblákban. Oka: UNION szerkezetet is használok, s ott az egyező oszlopnév bonyodalmat okozna a rendezésnél (ORDER BY...).

A hozzászóláshoz regisztráció és belépés szükséges
új téma

Alias

Poetro · 2009. Okt. 9. (P), 21.49

Az alias-okat pont azért találták ki, hogy ne legyen abból probléma ha több táblában azonos mezőnév van. Példa:

SELECT n.nid AS node_nid, c.nid AS content_nid
FROM node AS n
INNER JOIN content AS c USING vid
ORDER BY n.nid

SELECT n.nid AS node_nid, c.nid AS content_nid 
FROM node AS n 
INNER JOIN content AS c USING vid 
ORDER BY n.nid

A hozzászóláshoz regisztráció és belépés szükséges
új téma

Hátránya is van

laji · 2009. Okt. 9. (P), 23.09

A főtáblám 25 oszlopból áll, további 4-10 oszlop a fűzött táblákból (JOIN). Ebből 10-15 szerint rendezek is (ORDER). Sok helyen lekérdezem a főtáblát. Így elég mindenütt SELECT zokni.* lekérés; ha álneveket (ALIAS) használnék, egyenként fel kéne sorolnom az összes oszlopot.

Rossz helyre szúrtam be a megjegyzést. :(

A hozzászóláshoz regisztráció és belépés szükséges
új téma

Weblabor

Keresés