Autor Zpráva
it47
Profil
Ahojte,

mam tabulku logy (id, datum, ip) a chcel by som sql dotaz, ktory mi vrati pocet unikatnych IP adries za poslednych 60 minut.

Mam nieco taketo:

SELECT ip, count( * ) AS pocet
FROM online
WHERE datum >= date_sub( now( ) , INTERVAL 60 MINUTE)
GROUP BY ip
ORDER BY pocet DESC 

avsak pocet IP adries je vlastne $result->num_rows, co je podla mna neefitivne.

1.) Vedeli by ste mi prosim poradit, ako napisat co najrychlejsi dotaz?
2.) Vytvorim index na stlpec datum? A nebude potom pomaly zapis (INSERT), ktory sa vykonava pri kazdom nacitani stranky? (Tabulka ma denne asi 300.000 riadkov, v noci sa vzdy aktualizuje a premaze)
3.) Odporucate pre tento typ tabulky ulozny system MyISAM alebo InnoDB?

Vopred dakujem velmi pekne.
Kajman
Profil
1.
SELECT count( distinct ip ) AS pocet
FROM   online
WHERE  datum >= date_sub( now( ) , INTERVAL 60 MINUTE)

2. ano operace s řádky budou náročnější, vybírání dotazem z 1 ale bude rychlejší

3. pokud to nejsou důležitá data, tak na log bych použil myisam
it47
Profil
Kajman:
super, dakujem velmi pekne, to distinct je velmi chytre.

S tym indexom je otazne, ci ho mat, pretoze:
- insert sa vykonava pri kazdom nacitani stranky (co je aj 1000x za minutu)
- select sa robi cronom jeden krat za kazdu minutu.

Co by si spravil ty na Tvojom mieste? S indexom alebo bez indexu na datum?

Vopred vdaka za odpoved.
juriad
Profil
it47:
Za tu hodinu a půl sis to mohl změřit. Ty máš k dispozici všechny údaje. My musíme jen hádat.

Pokud je SELECT jen jednou za 60000 INSERTů nejspíš index nebude mít smysl.

Tabulka obsahuje tedy 300 000 záznamů, jeden záznam zabírá kolem 40 bytů (nadhodnocuji), tedy celá tabulka má 12 MB, to MySQL bude pravděpoodobně udržovat v paměti. Full scan při takové velikosti bude pořád otázkou zlomků sekund. Index by zajistil efektivní zúžení prohledávaných dat na zhruba 1/24 (poslední hodina dne). Nakonec ale všechny záznamy za poslední hodinu bude nejspíš muset seřadit podle IP kvůli distinctu.

Pokud bys poměřoval rychlost s různými indexy, zkusil bych i variantu indexu nad (ip, datum); bylo by možné provést následující dotaz:
SELECT COUNT(*)
FROM (
  SELECT ip
  FROM online
  GROUP BY ip
  HAVING MAX(datum) >= date_sub( now( ) , INTERVAL 60 MINUTE)
) x

A ještě pohrát s tím, zda je nestačí jen index přes jeden sloupec - ip, a případně jaká je lepší orientace sloupce datum (ASC, DESC).

Tento přístup sice rychleji řeší distict, ale musí projít všechna data. Navíc při procházení v datech hodně skáče, což může dost vadit cachi (naštěstí však data budou alespoň v paměti).
Kajman
Profil
juriad:
To s having je dobrá finta.

it47:
Ještě si můžete udělat jednu tabulku, kde bude ip primární klíč a jen si k ní uchovávat poslední přístup. Také se to může promazávat a řákdů by se procházelo méně.
it47
Profil
juriad:
super, dakujem pekne, pohram sa s tym, ale vyzera, ze to je pomalsie, ako od Kajmana.
Ako prosim ta viem, v indexoch rozlisovat ASC a DESC? V PhpMyAdmin mi to neponuka tuto moznost.

Kajman:
SELECT count( distinct ip ) AS pocet
FROM   online
WHERE  datum >= date_sub( now( ) , INTERVAL 60 MINUTE)

prosim ta, ked toto zadam cez PhpMyAdmin, tak mi nezobrazi cas spracovania? Ako je to mozne?
Je to spracovane v momente, ale cas nenapise, pricom pri inych skriptoch napise.

Vopred dakujem velmi pekne.


Kajman:
Ještě si můžete udělat jednu tabulku, kde bude ip primární klíč a jen si k ní uchovávat poslední přístup. Také se to může promazávat a řákdů by se procházelo méně.
ale tuto pri vkladani musim vykonat najskor SELECT a az potom INSERT/UPDATE? Alebo je to mozne spracovat cez jeden prikaz?

Vopred dakujem pekne.
Kajman
Profil
it47:
Ako prosim ta viem, v indexoch rozlisovat ASC a DESC?

MySQL má dělané indexy tak, že je umí číst zepředu i zezadu, takže na to můžete zapomenout, je to jedno.

Ako je to mozne?
Zkuste jiného klienta, z webových můžu doporučit adminer.

V MySQL to jde jedním příkazem
dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
Tedy něco jako
INSERT INTO online_ip
            (
                        ip,
                        datum
            )
            VALUES
            (
                        Inet_aton('8.8.8.8'),
                        Now()
            )
ON DUPLICATE UPDATE datum=VALUES(datum) 
it47
Profil
Kajman:
Waau, super, dakujem za ukazku SQL dopytu.

Skusam adminera a pri prikaze mi tiez nevypise ziadny cas skriptu. Ako to prosim viem zapnut?

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: