| Autor | Zpráva | ||
|---|---|---|---|
| Petr Ká Profil |
Ahoj,
potřeboval bych poradit s dotazem pro vhodný výběr reklamy. Bohužel SqlFidle tento dotaz nezpracuje, tak to aspon zkusim. Prvně popíšu co nyní mám: - sub-selectem si vyberu ID velikosti z tabulky ADS_SIZES (největší možnou reklamu dle dané proměnné - na příkladu je 640x120px, která zároveň existuje v tabulce ADS) - dále si teda z ADS vyberu jen ty reklamy, které mají danou velikost (pozor, tady znova používám absolutně stejné podmíny - jde to optimalizovat???) - Podle náhodně vygenerovaného čísla (0 - 100 - na příkladu je 21) se rozhodnu, zda si vyberu prvních 15 dle nejlepsí nabídky (BID), nebo nejvyššího rozpočtu na kampaň (FLOOR(BALANCE/BID) AS usages) a nebo zbytek náhodně (aby se zobrazovali i ostatní) - Těhle 15 kusů poté seřadím náhodně a vezmi si jen jeden, který zobrazím Co potřebuji? 1) Pokud to jde, dotaz zoptimalizovat - nebráním se i kompletnímu přepracování, ale nenapadá mě nic lepšího. Testováno na cca 4000 záznamech a v pohodě 2) Přidat vazbu do tabulky ADS_TARGETING, kde budou řádky AD_ID[vazba na ADS.ID], TYPE [enum "GEO","HOURS_FROM","HOUR_TO"] a VALUE (hodnota TYPE [např. GEO=CZ a HOURS_FROM=18 a HOURS_TO=21 znamená, že se reklama zobrazí jen v ČR v časech mezi 18-21 hodinou. Pokud nebude záznam např. GEO, tak nefiltrovat]). Lámu si s tím hlavu a fakt nevím :-) SQL: TADY DB SCHEMA: TADY EDIT: Adminum se omlouvam za tisíc úprav, ale když jsem dal do CODE celý SQL dotaz a do druhého CODE SQL SCHEMA, tak se to celé rozbouralo :) EDTI2: Jestě jsem nedodal, že ADS a ADS_TARGETING je 1:N... Ale to je ze schema asi patrné :) |
||
| Petr Ká Profil |
EDIT3: Je možné, že by to fungovalo TAKHLE ?
Stejně se mi tam nelíbí ten milion podselectů a opakující se podmínky |
||
| TomášK Profil |
ORDER BY RAND() může ten dotaz zpomalit i několikanásobně, navíc to zřejmě není úplně náhodné.
Edit: koukám, že je jen na 15 záznamech, tam je to asi jedno Edit2: je to i uvnitř dotazu |
||
| Petr Ká Profil |
#4 · Zasláno: 3. 5. 2016, 22:27:32
TomášK:
Nad těma sloupcema mám index, dočetl jsem se, že to nějak zásadně nezpomaluje. Spíš mě mrazí po zádech z těch podmínek a milionu subselectů... BTW: otestováno a funkční, kdyby náhodou někdy něco takovýho potřeboval. |
||
| TomášK Profil |
#5 · Zasláno: 3. 5. 2016, 22:36:02
V tom odkazu, který jsi poslal jsou dva příklady - jeden trvá 300 ms, druhý 1200 ms. To, co používáš, je analogické tomu 1200 ms, neuniká-li mi něco.
|
||
| Petr Ká Profil |
#6 · Zasláno: 4. 5. 2016, 08:03:07
TomášK:
Jo, taky jsem si toho pak všiml, už jsem byl hodně unavenej... Ostatně zpět k tomu dotazu, napadá Tě, jak by jej šlo zjednodušit? |
||
| CZechBoY Profil |
Ten CASE bych asi vyhodil, šlo by to přepsat pomocí OR.
a.end_date IS NULL OR a.end_date >= NOW() Nevím jestli to nějak zrychlí dotaz, ale určitě zjednoduší. |
||
| Petr Ká Profil |
#8 · Zasláno: 4. 5. 2016, 09:10:28
CZechBoY:
Nemyslím, ze by to byla nejaka zatez, ve finale to provede to samé, ale rád bych se zbavil ted opakujících se JOINů atd |
||
| TomášK Profil |
#9 · Zasláno: 4. 5. 2016, 11:01:08
Nevidím, jak se zbavit těch JOINů bez analytických funkcí, které do MySQL ještě nedorazily. S tabulkou ads_targeting se špatně pracuje, prootože není vhodně navržená (Entity–attribute–value). Rozdělíš tu tabulku podle enumu a pak v každém selectu vybíráš jednu konkrétní hodnotu a přetypováváš. Změnil bych ji, asi na tři tabulky.
|
||
| Petr Ká Profil |
#10 · Zasláno: 4. 5. 2016, 11:02:57
TomášK:
Nyní to mám pořešené tak, že jsem to rozdelil na 2 dotazy. Prvně si vytáhnu ty targeting ID a až pak pokládám ten dotaz. Tedy zbavil jsem se jednoho subselectu s 3mi joiny. |
||
| Kajman Profil |
Petr Ká:
1) Pokud je to v pohodě, tak bych opakování neřešil pokud to v dotaze databázi pomůže. 2) Asi bych zkusil dělat 3x left join na porušení omezení a ve where pak vybral ty, kde se provázání nepovedlo (tedy není žádná podmínka, nebo je splněna) V tabulce ADS_TARGETING zvažte více sloupců pro value podle typu (value_string, value_number, value_time). Případně ji nedělat univerzální a mít zemi a časy jako sloupce - pak stačí jeden left join na hledání porušení. |
||
| CZechBoY Profil |
#12 · Zasláno: 4. 5. 2016, 11:07:15
A nemůžeš prostě tu podmínku where se subselectem přepsat jako join? Nebo použít MAX? Mně připadá, že dělá úplně to stejný dvakrát zbytečně...
Nešlo by to takhle? SELECT final.*
FROM (
SELECT
MAX(s.x + s.y), s.id,
a.id, a.is_image, a.data, a.pricing, a.bid, a.balance, a.user_id,
s.x, s.y,
FLOOR(a.balance / a.bid) AS usages, 21 AS rand_number
FROM ads a
INNER JOIN ads_sizes s ON s.id = a.size_id
INNER JOIN users u ON a.user_id = u.user_id
WHERE
a.adult = 0 AND
a.bid <= a.balance AND
u.active = 1 AND
u.confirmed = 1 AND
a.active = 1 AND
a.start_date <= NOW() AND
CASE
WHEN a.end_date IS NOT NULL
THEN a.end_date >= NOW()
ELSE TRUE
END AND
ORDER BY
CASE
WHEN rand_number > 66 THEN bid
WHEN rand_number >= 33 THEN usages
ELSE RAND()
END
DESC
LIMIT 15
) AS final
ORDER BY RAND()
LIMIT 1 |
||
| Petr Ká Profil |
#13 · Zasláno: 4. 5. 2016, 11:08:56
CZechBoY:
Takhle to nelze, protože musím vybrat prvně nejvíce vyhovující velikost reklamy - která zároveň má nějaké reklamy splnujici podminky, proto 2x stejne podminky... |
||
| CZechBoY Profil |
#14 · Zasláno: 4. 5. 2016, 11:10:11
A když bys ty 2 podmínky dal do WHERE?
|
||
| Petr Ká Profil |
Kajman:
k ad 2) Nemyslíme oba to samé, což mám? CASE WHEN COUNT(b.id)>0 THEN (SELECT COUNT(bb.id) FROM ads_targeting bb WHERE bb.ad_id=a.id AND bb.type='GEO' AND bb.value='EN') ELSE -1 END AS geo_match . WHERE sub_final.geo_match <> 0 AND sub_final.hour_from_match <> 0 AND sub_final.hour_to_match <> 0 EDIT: Strukturu je možné ještě přepracovat, jedná se o nový projekt, rád se nechám poučit :) |
||
| Kajman Profil |
Měl jsem na mysli kratší zápis bez poddotazu
... left join ads_targeting bb WHERE bb.ad_id=aa.id AND bb.type='GEO' AND bb.value!='EN' .. where bb.ad_id is null Jestli bude rychlejší, je potřeba vyzkoušet. Ono != nebývá rychlé, ale třeba na ty časy se negace na rychlost neprojeví a kontrolují se omezení jen pro vybrazné inzeráty (jsou aktivní atp.) Edit. ale pokud by k jednomu inzerátu byly dvě GEO hodnoty - třeba CZ a SK, tak by se při tomto řešení nezobrazil nikde |
||
| Petr Ká Profil |
#17 · Zasláno: 4. 5. 2016, 11:34:01
Kajman:
„Edit. ale pokud by k jednomu inzerátu byly dvě GEO hodnoty - třeba CZ a SK, tak by se při tomto řešení nezobrazil nikde“ To je právě ten důvod, proč to mám takhle... Inzerent si bude moct zvolit oblast (nebo více). Proto ty hodnoty -1 (nefiltrováno), 0 (filtrováno, ale nevyhovuje) a COUNT( > 0 vyhovuje geocílení) |
||
| Petr Ká Profil |
#18 · Zasláno: 4. 5. 2016, 22:38:51
Takže jsem to kompletně přepsal a na 1000 dotazů je průměr doby zpracování 0.00339 sekundy (tabulka ADS má 4500 záznamů).
Výsledné SQL: SELECT tmp.* FROM (
SELECT
ad.id AS ad_id, ad.is_image,ad.adult, ad.target_url, ad.data,
IFNULL(adt_geo.value,'NO_LIMIT') AS geo_limit_val,
IFNULL(adt_hour_start.value,'NO_LIMIT') AS hour_from_limit_val,
IFNULL(adt_hour_to.value,'NO_LIMIT') AS hour_to_limit_val,
ads.x AS size_x,ads.y AS size_y, (ABS(600*2 - ads.x*2)+ABS(500 - ads.y)) AS size_diff,
ad.pricing,ad.bid, ROUND(ad.bid/max_bid.mbid*100/20)*20 AS bid_percent,max_bid.mbid,ad.balance, FLOOR(ad.balance/ad.bid) AS usages,
add_rnd.random
FROM ads ad
LEFT JOIN ads_targeting adt_geo ON adt_geo.ad_id=ad.id AND adt_geo.type = 'GEO'
LEFT JOIN ads_targeting adt_hour_start ON adt_hour_start.ad_id=ad.id AND adt_hour_start.type = 'HOURS_FROM'
LEFT JOIN ads_targeting adt_hour_to ON adt_hour_to.ad_id=ad.id AND adt_hour_to.type = 'HOURS_TO'
INNER JOIN ads_sizes ads ON ads.id=ad.size_id
INNER JOIN users u ON ad.user_id=ad.user_id AND u.active=1 AND u.confirmed=1
INNER JOIN (SELECT ROUND(RAND()*100) AS random) add_rnd
INNER JOIN (SELECT MAX(bid) AS mbid FROM ads) max_bid
WHERE
ad.active = 1 AND
ad.bid > 0 AND
ad.balance > 0 AND
ad.start_date <= NOW() AND
( ad.end_date IS NULL OR ad.end_date >= NOW() ) AND
ads.x <= 600 AND
ads.y <= 500
GROUP BY
ad.id, adt_geo.id, adt_hour_start.id, adt_hour_to.id
HAVING
( geo_limit_val = 'NO_LIMIT' OR geo_limit_val = 'CZ' ) AND
( hour_from_limit_val = 'NO_LIMIT' OR hour_from_limit_val >= HOUR(NOW()) ) AND
( hour_to_limit_val = 'NO_LIMIT' OR hour_to_limit_val >= HOUR(NOW()) )
ORDER BY
size_diff ASC,
CASE WHEN add_rnd.random > 66 THEN ABS(size_diff-bid_percent)
WHEN add_rnd.random >= 33 THEN ABS(size_diff-usages)
ELSE RAND()
END ASC
LIMIT 30
) AS tmp
ORDER BY RAND()
LIMIT 1 |
||
| Kajman Profil |
#19 · Zasláno: 4. 5. 2016, 23:18:12
Petr Ká:
A jak se zadává do tabulek, když má být reklama zobrazována od 23:00 do 01:00? Myslím, že to nebude fungovat korektně (ať to jsou dva řádky, nebo čtyři (23,23:59:9,0,1)). Myslím, že by byla lepší tabulka se sloupci (geo, from, to). Nevidím výhodu to ukládat omezení jako nezávislé řádky. Pokud se tam přidá nový typ omezení, tak stejně musíte upravit vyhledávací dotazy, proto mi nepřijde horší řešení přidat případně sloupec. Ještě mě napadá, že by mohli zákazníci raději omezovat čas podle času klienta a ne serveru. V případě omezení na straně serveru se o to více může hodit mít závislé omezení v jednom řádku CZ 23 23:59:59 CZ 0 1 US 9 11 null 22 null CH null null A v současném řešení mi přijde, že zvyšováním počtu splněných podmínek se u reklamy zvyšuje šance, že bude vybrána v těch ostatních (<33). |
||
| Petr Ká Profil |
#20 · Zasláno: 5. 5. 2016, 12:38:12
Kajman:
„A jak se zadává do tabulek, když má být reklama zobrazována od 23:00 do 01:00?“ To je dobrá poznámka... teď to vlastně je tak, ze se zada jen jeden intarval od-do, ale pres pulnoc to neresi... ajaj... „Nevidím výhodu to ukládat omezení jako nezávislé řádky“ A jak omezím geolokaci třeba na 10 států tedy? Leda pro každý stát nastavit / nenastavit časový interval... Ale zase, co když těch intervalů bude vícero ? „Pokud se tam přidá nový typ omezení“ Tam už mám v plánu ještě kategorizaci webů dle zaměření „Ještě mě napadá, že by mohli zákazníci raději omezovat čas podle času klienta“ Opět dobrá poznámka Otázkou je, co teď. Překopat to? |
||
| Kajman Profil |
#21 · Zasláno: 5. 5. 2016, 15:19:09
Asi bych udělal tu tabulku omezeni se všemi sloupci, co to mohou omezovat. Pokud bude hodnota null, tak se neomezuje. Jinak musí platit všechny nenullové omezení. Stačí když omezení bude vyhovovat jeden řádek. Pro deset států tedy bude např. 10 řádků, kde bude vyplněné jen geo a další omezení budou null.
V dotaze by pak šlo něco jako left join ads_targeting ex on ex.ad_id=ad.id
left join ads_targeting ok on ex.adt_id=ok.adt_id
and (ok.geo is null or ok.geo='UK')
and (ok.lang is null or ok.lang='en')
and (ok.hour_start_server is null or ok.hour_start_server<=hour(now()))
and (ok.hour_to_server is null or ok.hour_to_server>=hour(now()))
...
where (ex.adt_id is null or ok.adt_id is not null) # neni vubec zadna, nebo je alespon jedna splnena
...
group by ad.id ...(adt_id je primární klíč ads_targeting) |
||
| Petr Ká Profil |
#22 · Zasláno: 5. 5. 2016, 18:02:53
Kajman:
To zní hezky, zítra to otestuju, ted musim pryc a napisu sem, jak to dopadlo na datech ;) Dekuju! |
||
|
Časová prodleva: 8 let
|
|||
0