Filtrování podle štítků - pomohou SQL experti?

8.9.2008 16:27 | Přečteno: 1622× | AbcLinuxu

Máme v plánu umožnit filtrování poradny pomocí štítků, což by mělo fungovat jako náhrada za původní strukturu poraden (bug #1091).

Naše snahy mají ovšem vadu na kráse. Chceme umožnit, aby uživatel mohl zadat podobný filtr:

(nvidia OR ati) AND ubuntu AND NOT fglrx

Filtrování může takto fungovat díky štítkům. Potýkáme se ovšem s tím, že vyhledávání trvá příliš dlouho (typicky 5 sekund) a je to velká zátěž na databázi. Našli by se mezi vámi odborníci, kteří by dokázali poradit, jak věc zefektivnit?

Zde najdete dump databáze (3,2 MB), kde jsou dvě tabulky - relace a stitkovani. U relace je důležité cislo (identifikuje "stránku" v systému, se kterou kód může dále pracovat), potomek nesoucí číslo datové položky v systému, typ_potomka, kde nás zajímají jen relace s hodnotou 'P', a predchozi, pomocí kterého se můžeme omezit na Linuxovou poradnu (číslo 49490).

V tabulce stitkovani jsou sloupečky typ (opět nás zajímá vždy hodnota 'P'), cislo (které se musí provázat se sloupcem relace.potomek - pozor, ne s relace.cislo!) a stitek obsahující označení samotného štítku.

Jeden z našich pokusů o vyhledání relací, které splňují podmínku (nvidia OR ati) AND ubuntu, vypadá takto:

select count(R.cislo) from relace R where R.typ_potomka='P' and
R.predchozi=49490 and exists (select S.cislo from stitkovani S where S.typ =
'P' and S.cislo = R.potomek and (S.stitek='nvidia' or S.stitek='ati')) and
exists (select S.cislo from stitkovani S where S.typ = 'P' and S.cislo =
R.potomek and S.stitek='ubuntu');

Vrátí celkem 380 relací za cca 5 sekund, resp. o něco méně při opakování.

       

Komentáře

[janik]

$ time mysql -u ab ab < orig.sql
count(R.cislo)
380

real 0m1.936s
user 0m0.011s
sys 0m0.007s
$ time mysql -u ab ab < new.sql
count(R.cislo)
380

real 0m0.273s
user 0m0.012s
sys 0m0.004s


---

SQL prikaz:
select
    count(R.cislo)
from
    relace R
    LEFT JOIN stitkovani nvidia ON (nvidia.cislo = R.potomek AND nvidia.stitek='nvidia' AND nvidia.typ = 'P')
    LEFT JOIN stitkovani ati ON (ati.cislo = R.potomek AND ati.stitek='ati' AND ati.typ = 'P')
    LEFT JOIN stitkovani ubuntu ON (ubuntu.cislo = R.potomek AND ubuntu.stitek='ubuntu' AND ubuntu.typ = 'P')
    LEFT JOIN stitkovani fglrx ON (fglrx.cislo = R.potomek AND fglrx.stitek='fglrx' AND fglrx.typ = 'P')
WHERE
    R.typ_potomka = 'P' AND R.predchozi=49490 AND
    ((nvidia.cislo IS NOT NULL OR ati.cislo IS NOT NULL) AND ubuntu.cislo IS NOT NULL AND NOT fglrx.cislo IS NOT NULL)


[Sinuhet]

A pokud si chceme trochu zaprasit...

$ time mysql abc_test < new.sql
count(R.cislo)
380

real    0m0.558s
user    0m0.016s
sys     0m0.004s
$ time mysql abc_test < q.sql
count(r.cislo)
380

real    0m0.187s
user    0m0.008s
sys     0m0.004s
$ cat q.sql

select
        count(r.cislo)
from
        (
        select
                s.cislo
        from
                stitkovani s
        where
                s.typ = 'P'
                and s.stitek in ('nvidia', 'ati', 'ubuntu', 'fglrx')
        group by
                s.cislo
        having
                bit_or(s.stitek = 'nvidia' or s.stitek = 'ati') = 1
                and bit_or(s.stitek = 'ubuntu') = 1
                and bit_or(s.stitek = 'fglrx') = 0
        ) s
        join relace r on (s.cislo = r.potomek)
where
        r.typ_potomka = 'P'
        and r.predchozi = 49490
;

[Luboš Doležel (Doli)]

Tak jsem napsal parser vyhledávacích výrazů, který pomůže vygenerovat obdobné SQL. Díky moc, je to dost rychlé

[Josef Kufner]

Místo toho kopce joinů by se dal použít jeden a spočítat, kolik se jich trefilo. Něco jako:

SELECT R.cislo, COUNT(s.stitek)
FROM
    relace R
    LEFT JOIN stitkovani s ON (s.cislo = R.potomek AND s.typ = 'P')
WHERE
    R.typ_potomka = 'P' AND R.predchozi=49490 AND
    s.stitek IN ('ati', 'nvidia', 'ubuntu', 'fglrx')
GROUP BY R.cislo
HAVING count(s.stitek) = 4

Nezkoušel jsem to, jen mě to tak napadlo. Ale nenapadá mě, jak do toho zakomponovat ty ostatní operace (or, not).

[Luboš Doležel (Doli)]

Něco podobného jsem napsal taky a taky jsem nevěděl, jak zařadit takové operace. Nicméně to, co napsal Sinuhet, vypadá dobře

[Josef Kufner]

Hmm... ono ty operace vlastně ani nejsou moc potřeba, rozhodně ne všechny.

Když se dotaz převede do tvaru DNF, tak stačí zprovoznit operace A AND B, NOT C a X OR Y, aby se dalo spočítat (A AND B) OR (A AND NOT C). A tohle stačí na vyhodnocení jakéhokoliv výrazu.

A AND B jsem napsal před chvílí, X OR Y zvládne UNION. Takže zbývá vyřešit operaci NOT C.

[rADOn]

a co na to rika EXPLAIN ? ja bych tipnul ze neco ve smyslu ze ty subselecty jedou fulscanem

[Leoš Literák]

To je v linkovane chybe.

[Ignor]

Tak SQL expert teda opravdu nejsem, což ale mi nebrání k tomu něco říct. Kdybych to dělal já, tak bych jednoznačně měl tabulku stitky, kde by byly... štítky. A do tabulky stitkovani bych strkal místo řetězce odkaz na klíč do tabulky stitky. Nad tím integerem by se navíc dělal index mnohem příjemnějc než nad varchar(30).

A docela by mě zajímalo jak by to vypadalo, kdyby se použila (první co mě napadne) možnost 'and' dělám 'intersect' a 'or' dělám 'union'.

[Michal Vyskočil]

No a zrovna od tebe bych čekal spíš, že řekneš, že tohle patří do databázové poradny

[Josef Kufner]

Nezapomeňte na minimalizaci dotazu, který uživatel zadá (pokud to již neděláte).

ps: Místo hradel si tam představ sql dotaz ;)

[depka]

pouziva vubec nekdo ty stitky?