Autor Zpráva
Lucyk
Profil
Zdravím,

mám dotaz týkající se rychlosti výpisu záznamů z databáze a následné zobrazení na webu.

Mám tabulku s cca 30000 záznamy. Mám sestavený dotaz na vyhledávání konkrétních záznamů, které obsahují hledané slovo. Tím, jak jsem to postupně plnila až do současné hodnoty, tak se mi rychlost vyhledávání (a jeho zobrazení) rapidně snížila.

Čím je to způsobeno? Tím, že tabulka obsahuje tolik záznamů, či snad že by byl nevhodně zvolený dotaz na databázi? Což si moc nemyslím, neboť mám i dotazy na výpis třeba jen části dat z tabulky a u tohoto jsem také zpozorovala pomalé načítání (samozřejmě čím bylo méně dat tím to bylo rychlejší).

Lze to nějak optimalizovat, aby to s narůstáním dat v databázi bylo stále stejné a nesnižoval se výkon?

Děkuji za objasnění dotazu.
Keeehi
Profil
Lucyk:
Lze to nějak optimalizovat, aby to s narůstáním dat v databázi bylo stále stejné a nesnižoval se výkon?
Většinou ano.

Dodej popis tabulky a i tvůj dotaz a pak až ti budeme schopni poradit více.

Jinak 30 000 záznamů není nic extra. Databáze zvládají i mnohem větší objemy. Tudíž tam nejspíš nějaká neoptimalita bude.
CZechBoY
Profil
Pošli sql dotaz a jaké indexy máš na tabulce (tabulkách).
Lucyk
Profil
Ahoj,

níže uvádím strukturu tabulek, na které se vztahuje dotaz uveden na konci.

1.tabulka = rocnik
| id_rocnik | nazev |

2.tabulka = kategorie
| id_kategorie | nazev_kategorie | id_vyrobce | id_rocnik |

3.tabulka = podkategorie
| id_podkategorie | nazev_podkategorie | id_kategorie |

4.tabulka = karticky
| id_karta | id_podkategorie | number | lastname1 | firstname1 | jrsr1 | nameyear1 | lastname2 | firstname2 | jrsr2 | nameyear2 | lastname3 | firstname3 | jrsr3 | nameyear3 | lastname4 | firstname4 | jrsr4 | nameyear4 | unname | national1 | national2 | national3 | national4 | post1 | post2 | post3 | post4 | club1 | club2 | club3 | club4 | unclub | rc | limit | exist | note | note2 | image |

Dotaz
$dotaz="(select karticky.number, karticky.lastname1, karticky.firstname1, karticky.post1, karticky.lastname2, karticky.firstname2, karticky.post2, karticky.lastname3, karticky.firstname3, karticky.post3, karticky.lastname4, karticky.firstname4, karticky.post4, karticky.unname, karticky.limit, karticky.exist FROM rocnik JOIN kategorie ON rocnik.id_rocnik = kategorie.id_rocnik JOIN podkategorie ON kategorie.id_kategorie = podkategorie.id_kategorie JOIN karticky ON podkategorie.id_podkategorie = karticky.id_podkategorie WHERE CONCAT(nazev, ' ', nazev_kategorie, ' ', nazev_podkategorie)='$vypisuj' AND karticky.exist NOT LIKE 'not exist')";

Děkuji za případné návrhy na optimalizaci (pokud to lze)
TomášK.
Profil *
Děláš několik zásadních chyb.

1) struktura tabulek

Máš-li potřebu číslovat sloupce, je zpravidla potřeba vytvořit další tabulku. Zjednoduší to většinu dotazů, umožní to přidat libovolný počet asociací, lépe se s tím pracuje. Konkrétně by ty tabulky měly vypadat takto:

4.tabulka = karticky
| id_karta | id_podkategorie | number | unname | unclub | rc | limit | exist | image |

5.tabulka = ?
| id_? | id_karta | lastname | firstname | jrsr | nameyear | national | post | club

6.tabulka = notes
| id_note | id_karta | content |

2) LIKE

karticky.exist NOT LIKE 'not exist' je důvod, proč je to pomalé. Tahle konstrukce je celá špatně.

* Nepoužívej LIKE, pokud to není nezbytně nutné. S tabulkou, která má netriviální počet řádků, musíš vědět, jak se chová k indexům, jinak to bude pomalé.
* Používej vhodné operátory. Místo LIKE by mohla být prostá nerovnost.
* Používej vhodné datové typy. Sloupec s řetězci 'exist' a 'not exits' nahraď booleanem (TINYINT) s hodnotami 0/1.

3) CONCAT

Lepší než
CONCAT(nazev, ' ', nazev_kategorie, ' ', nazev_podkategorie)='$vypisuj'
je
$nazev, $kategorie, $podkategorie = split(" ", $vypisuj); // syntaxi php si nemapatuju, smysl je snad zřejmý
nazev = $nazev AND $kategorie = $kategorie AND podkategorie = $podkategorie;


Udělej ze sloupce exists boolean (TINYINT)


4) Zjisti si, co je SQL injection a jak se tomu vyhnout
Lucyk
Profil
TomášK.:
Děkuji za komentář. Níže uvádím příklad plnění tabulky kartičky (aby bylo zřejmé, proč je tvořena "tolika" sloupci).

| id_karta | id_podkategorie | number | lastname1 | firstname1 | jrsr1 | nameyear1 | lastname2 | firstname2 | jrsr2 | nameyear2 | lastname3 | firstname3 | jrsr3 | nameyear3 | lastname4 | firstname4 | jrsr4 | nameyear4 | unname | national1 | national2 | national3 | national4 | post1 | post2 | post3 | post4 | club1 | club2 | club3 | club4 | unclub | rc | limit | exist | note | note2 | image |
| 1 | id_podkategorie | 56 | Novotny | Petr | - | 1987 | - | - | - | - | - | - | - | - | - | - | - | - | - | CZE | - | - | - | G | - | - | - | KLA | - | - | - | - | - | - | - | error | - | url obrazku |
| 2 | id_podkategorie | 57 | Skopal | Jiri | Sr. | 1950 | Skopal | Jiri | Jr. | 1989 | - | - | - | - | - | - | - | - | - | SVK | SVK | - | - | CO | G | - | - | SLA | SLA | - | - | - | - | /9 | - | - | - | url obrazku |
| 3 | id_podkategorie | 58 | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | AC Sparta Praha Logo | - | - | - | - | - | - | - | - | - | - | - | - | SPA | - | - | - | - | - | url obrazku |
| 4 | id_podkategorie | 59 | Kokes | Hans | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | GER | - | - | - | - | - | - | - | TRI | - | - | - | - | rc | /8 | not exist | - | - | url obrazku |

Vysvětlivky k tabulce: Na jedné kartičce se může objevovat více jmen, zatím jsem teda narazila na variantu se 4 jmény a pak také kartička může mít pojmenování např. Týmové logo, což řadím do sloupečku (unname). Sloupce "note" zpravidla jsou plněny poznámkami, pokud jde o chybnou kartu nebo fotku hráče. Ve sloupci "exist" se nachází text "not exist" pokud daná kartička neexistuje, např. bylo plánováno její vydání, byla uvedena na kontrolním seznamu, ale ve skutečnosti nebyla vydána, proto je i v mém dotazu uvedena podmínka LIKE "not exist", neboť v některých případech budu chtít vypisovat i tento text, a není to řešeno pomocí 0 a 1.
Jinak ve skutečné tabulce, kde nejsou uvedeny žádné hodnoty je buňka prázdná (tady jsem to pro lepší orientaci vyplnila znakem "pomlčky").

Poznámka 2: Zkusím alespoň na vyzkoušení zrušit podmínku s LIKE a uvidím, zda se rychlost načítání zrychlí, nicméně pokud si dobře vzpomínám tak LIKE se používá pro porovnání, zda je v dané buňce obsažen určitý text či nikoliv.

Poznámky 3 a 4 nastuduji na internetu co to obnáší a jak to zahrnout do svého dotazu. Zatím jsem jen vždy používala CONCAT.
TomášK
Profil
Níže uvádím příklad plnění tabulky kartičky (aby bylo zřejmé, proč je tvořena "tolika" sloupci).
\
Ten příklad nedokáže obhájit tu strukturu. Ve struktuře, kterou jsem popsal, jde reprezentovat to samé. A navíc tam jdou pokládat dotazy "Získej seznam hráčů", "Na kterých kartičkách se hráč vyskytuje", "Kolik hráčů se vyskytuje na kartičce" a podobné. Nebudeš muset měnit strukturu, pokud se objeví kartička s pěti hráči.

Ještě lepé použít víc tabulek, než jsem psal:

4.tabulka = karticky
| id_karta | id_podkategorie | number | unname | unclub | rc | limit | exist | image |

5.tabulka = hraci
| id_hrac | lastname | firstname | jrsr | nameyear | national | post | id_club

7. tabulka
| id_hrac | id_karta |

8. tabulka = kluby
id_club | nazev

I posty a národnosti by mohly jít každé do samotné tabulky a tabulka hráči by na ně jen odkazovala.


Ve sloupci "exist" se nachází text "not exist" pokud daná kartička neexistuje, např. bylo plánováno její vydání, byla uvedena na kontrolním seznamu, ale ve skutečnosti nebyla vydána, proto je i v mém dotazu uvedena podmínka LIKE "not exist", neboť v některých případech budu chtít vypisovat i tento text, a není to řešeno pomocí 0 a 1.

I tady je vhodná další tabulka, která bude obsahovat poznámku k existenci ("exist", "not exist") a id, tabulka hráči se na ní bude odkazovat přes exist_id.

Filtrování pomocí
JOIN existence ON hraci.exist_id = existence.exist_id AND existence.note = "exists"
bude mnohem rychlejší než LIKE. Pohlídej si, aby na všech sloupcích, které koncí _id byl index. Pokud je správně označíš jako cizí klíče, myslím, že ho tam MySQL udělá automaticky.

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: