ugrás a tartalomhoz

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.
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"];
}
?>
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"];
}
?>
Előre is köszönöm a segítséget.
‹ Keresés akár több kulcsszó alapján MySQL fulltext szóvégre keresés megoldható? [LIKE '%macska'] szerűen ›
 ■
1

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
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...)
2

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.
4

Rendezés

vbence · 2009. Okt. 8. (Cs), 17.35
mi rendez gyorsabban


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

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.
3

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)
6

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.
7

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...).
8

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
9

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. :(