Zadejte hledaný výraz...

Velká MySQL tabulka, optimalizace SELECT pro stránkování

pes502
verified
rating uzivatele
(4 hodnocení)
19. 6. 2012 19:22:42
Zdravím,
Mám v mysql tabulku (říkejme ji třeba "pokus") a ta obsahuje přes 1 500 000 záznamů.
Potřeboval bych udělat stránkování (to mám již vyřešené), nicméně když vypíši tímto příkladem data pro danou stránku:
pokud je $limit_start třeba 500 (LIMIT 500,40) je všecko ok, ale pokud mám $limit_start třeba 750000 (LIMIT 750000,40) tak se to strašně dlouho načítá (nebo vůbec).
Můžete mi prosím někdo poradit, jak mám udělat tento dotaz efektivně, abych nemusel číst všecky řádky v tabulce i když je nepotřebuji, ale aby se vypsalo jen těch 40 a rychlost byla furt stejná (co nejvyšší)??
Díky moc všem, kteří přispějí do diskuze
19. 6. 2012 19:22:42
https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani/#reply776972
Pošlete sem strukturu tabulky a výsledek toho dotazu s EXPLAIN:
19. 6. 2012 19:35:18
https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani/#reply776971
Zkuste to přepsat na
A - to nesouvisí s vaším dotazem, ale s obecnými doporučenými postupy - vypište explicitně sloupce, které potřebujete, místo hvězdičky.
19. 6. 2012 19:42:11
https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani/#reply776970
pes502
verified
rating uzivatele
(4 hodnocení)
19. 6. 2012 19:44:17
Explain je zde = http://up.ezin.cz/images/explain.png
Jinak
id INT (11)
všecko ostatní TEXT ... TEXT tam mám jako výchozí, protože jsem s tím dělal pokusy, je možné to přeházet na nějaké lepší datové typy.
Jinak velikost indexu: 15MB
Délka řádku ø 1 123
Velikost řádku ø 1 134 B
19. 6. 2012 19:44:17
https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani/#reply776969
Ještě lepší nápad
Další důvod pro pomalý dotaz (konkrétně Using filesort):
Pokud čtete sloupce typu TEXT a BLOB, MySQL nemůže pracovat v paměti, ale musí zapisovat na disk.
http://dev.mysql.com/doc/refman/5.0/en/blob.html
Instances of BLOB or TEXT columns in the result of a query that is processed using a temporary table causes the server to use a table on disk rather than in memory because the MEMORY storage engine does not support those data types. Use of disk incurs a performance penalty, so include BLOB or TEXT columns in the query result only if they are really needed. For example, avoid using SELECT *, which selects all columns.
Takže řešení takhle z křesla:
1. Nepoužívejte LIMIT :OFFSET, :CUTOFF pro vysoký OFFSET. Přepište to výše naznačeným způsobem.
Viz Avoid offset for large tables
2. Všechny sloupce typu TEXT přesuňte do druhé tabulky, kde použijete stejný primární klíč. Relační dotazy provádějte nad první tabulkou a z druhé vytáhněte potřebné informace až jako druhý krok, jen pomocí primárního klíče.
19. 6. 2012 20:01:21
https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani/#reply776968
pes502
verified
rating uzivatele
(4 hodnocení)
19. 6. 2012 20:08:51
Napsal Martin Schlemmer;810038
2. Všechny sloupce typu TEXT přesuňte do druhé tabulky, kde použijete stejný primární klíč. Relační dotazy provádějte nad první tabulkou a z druhé vytáhněte potřebné informace až jako druhý krok, jen pomocí primárního klíče.
Toto jsem moc nepochopil :/
Abych pravdu řekl, nikdy jsem takové problémy řešit nemusel a SQL zas až tak do hloubky neznám :/
19. 6. 2012 20:08:51
https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani/#reply776967
V jedné tabulce máte primární klíč (ve vašem případě `id`) a všechny sloupce kromě sloupců typu TEXT a BLOB.
V druhé tabulce máte stejný primární klíč a všechny sloupce typu TEXT a BLOB.
Všechny dotazy provádíte nad první tabulkou.
Druhou tabulku použijete pouze pro načtení chybějících údajů:
19. 6. 2012 20:18:18
https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani/#reply776966
pes502
verified
rating uzivatele
(4 hodnocení)
19. 6. 2012 20:20:40
A pokud přehážu pole typu TEXT třeba na VARCHAR (což by se dalo) tak to půjde vyřešit přímo z jedné tabulky?
19. 6. 2012 20:20:40
https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani/#reply776965
To záleží, jak široký ten VARCHAR bude a kolik má MySQL k dispozici paměti.
V tabulce typu MEMORY se totiž VARCHAR chová jako CHAR.
http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html
Variable-length types such as VARCHAR are stored using a fixed length.
Při sloupci VARCHAR(1024), tedy 1024 bytů / 1 kB na buňku a dvou milionech řádcích bude ten jediný sloupec zabírat necelé 2 GB RAM.
19. 6. 2012 20:27:53
https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani/#reply776964
Zkuste ještě tohle:
A ty IDs dosaďte do tohoto dotazu oddělené čárkou:
19. 6. 2012 20:31:41
https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani/#reply776963
pes502
verified
rating uzivatele
(4 hodnocení)
19. 6. 2012 20:33:23
Napsal Martin Schlemmer;810052
Zkuste ještě tohle:
A ty IDs dosaďte do tohoto dotazu oddělené čárkou:
To s tím BETWEEN jsem již četl na více webech, ale nevím, jak to mám udělat.
Na STRANA 1 mám video, které má ID třeba 1500000 a na straně třeba 37000 mám video, které má ID 1 a zatím mě nenapadl způsob, jak zjišťovat IDčka, abych je mohl dosadit.
Používám tuto fci, kteoru jsem si sepsal:
proměnná $actual stáhne z $_GET aktuální stránku, na které se uživatel nachází
19. 6. 2012 20:33:23
https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani/#reply776962
Za předpokladu, že v číslování není mezera a jsou aktivní všechny řádky (nejsou např. označené jako smazané nebo v moderaci), dostanete nejnižší a nejvyšší ID pro stránku takto:
Pokud neplatí ty dva předpoklady, nedá se poradit takhle na dálku. Budete muset být kreativní a víc se vzdělat.
Intuitivní řešení by bylo očíslovat si všechny aktivní záznamy samostatně, v extra sloupci.
P.S.: "Actual" znamená "skutečný". "Stávající" je "current".
19. 6. 2012 20:53:43
https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani/#reply776961
naniccz
verified
rating uzivatele
(3 hodnocení)
19. 6. 2012 20:58:52
Mate mě ten filesort v EXPLAINu -- nepoužije se index (primárního klíče?!), a proto to tak dlouho trvá. Pokud je čtení výrazně častější než zápis, vyplatí se držet "materializovaný pohled" s vazbou 1:1 k této tabulce, určující pořadí záznamů (s indexem na tom pořadí), a vybírat tím BETWEEN dotazem konkrétní interval pořadí...
19. 6. 2012 20:58:52
https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani/#reply776960
Pokud se nepletu, filesort se použije, protože ten index se musí řadit obráceně (DESC, přitom je ale řazený ASC).
19. 6. 2012 21:09:33
https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani/#reply776959
SEOPeter
verified
rating uzivatele
19. 6. 2012 21:50:32
Řešil jsem podobně "objemný" projekt s tabulkami cca milion řádků. Podle svých zkušeností musím říct, že MySQL není dělané na takto rozsáhlé databáze. Funguje to, ale je šíleně pomalé.
Problém je v typu DB, doporučuju změnit. Já sám jsem to musel řešit nakonec v Céčku.
19. 6. 2012 21:50:32
https://webtrh.cz/diskuse/velka-mysql-tabulka-optimalizace-select-pro-strankovani/#reply776958
Pro odpověď se přihlašte.
Přihlásit